Comments - System-Versioned Tables
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
I would like to create a bitemporal schema utilizing the standardized approach. Currently (10.4.6) application-time periods cannot be used to enforce temporal uniqueness and temporal referential integrity on the application time dimension (MDEV-16978, MDEV-16983). To compensate I have added the app-time start and end columns to primary, unique and foreign keys, and attempted to enforce said aspects via trigger. Unfortunately even the trivial INSERT has proven challenging: A BEFORE INSERT trigger failed to run, producing a "field <system-time (tx) start col> doesn't have a default value" error. An AFTER INSERT trigger did run, but only to leave me wondering how I am supposed to reliably distinguish the newly-inserted row from pre-existing ones for validation purposes in this case (my only clue, NEW.<system-time (tx) start col> is set to 0). Am I missing something, or should I better resort to a "userspace" approach until 10.5 arrives?
As an aside, AUTO_INCREMENT fires for rows inserted as a consequence of (UPDATE | DELETE) ... FOR PORTION OF. Is this intentional?