When I read about the system versioned (and bitemporal) tables in MariaDB, I was both surprised and delighted. This kind of data versioning is thought of as standard in the “immutable” databases that are big in Clojureland, such as Datomic and XTDB. On the other hand, these databases in Clojureland are usually EAVT based and not the “usual” relational kind. After a little thinking I could think of a bunch of really tricky issues that could arise from system versioning (eg having a by-default immutable log of all changes to some piece of data) in a relational context. The first being foreign keys.

If you create a foreign key in MariaDB without any further specifiers, it assumes ON UPDATE RESTRICT ON DELETE RESTRICT, meaning no referenced key could be changed and no referenced row could be deleted. Just consider this: if there’s a row in some table referencing a row in another, system versioned table, can the row in the system versioned “parent” table be deleted? After all the referenced version of the row will still exist… Or in a similar scenario if the “child” row is system versioned and updated to now reference a different row, will both of those referenced rows be RESTRICTed? After all, both referencing rows will still exist… Of course I had to try.

create table users (
id int not null auto_increment primary key,
name varchar(50) not null
) with system versioning;

create table profiles (
id int not null auto_increment primary key,
user_id int not null references users(id)
) with system versioning;

I tested with these minimal tables and found the following. For the exceptionally lazy reader: foreign keys work with current values and disregard history. On the other hand table schema is not and cannot be versioned.

The foreign key constraint still holds, meaning I can’t delete a row from users as long as it’s referenced from any “current” row in profiles. Likewise I can’t UPDATE the referenced key either.

This also means that the foreign key constraint stops holding once the referencing row (in profiles) is marked as deleted, even if it can still be inspected and even used using historical queries with AS OF or FOR.

Similarly, if the child row in profiles is updated to reference a different row (change user_id from 1 to 2), then only the id = 2 row in users will have be RESTRICTed and not the former parent.

One big catch with the system versioned tables of MariaDB is that they cannot be ALTERed even if they have no rows whatsoever. System versioning either has to be dropped from the table with ALTER TABLE profiles DROP SYSTEM VERSIONING or the @@system_versioning_alter_history setting has to be changed. It’s a bummer because it means that either you lose history or you can’t trust it anymore (since it can be altered).