Temporal Entity Integrity

In order to ensure temporal entity integrity in a bitemporal model a few constraints have to be in place:

  • Primary key constraint over (ID, ChangedAt, RecordedAt, ErasedAt) that ensures that no duplicate rows can exist.
  • Unique constraint over (ErasedAt, ID, ChangedAt) that ensures that no temporal duplicates can co-exist for the same entity and version.
  • Check constraint that ensures that RecordedAt < ErasedAt.
  • Check constraint that ensures no overlaps in the recording timeline.

These have now been added to the modeling tool in the test version, and can optionally be switched off from the “Defaults” menu. Below is a script that tests the functionality of these constraints.

Published by

Lars Rönnbäck

Co-developer of the Anchor Modeling technique. Programmer of the online modeling tool. Site maintainer. Presenter and trainer.

4 thoughts on “Temporal Entity Integrity”

  1. Please note that a primary key composed of (ID, ChangedAt, RecordedAt) is sufficient in theory, but due to technical reasons of the implementation ErasedAt is added as a fourth component. In order to create two table partitions, one with currently recorded information and one with erased information, SQL Server requires the partitioning column to be a part of the primary key.

  2. I am trying to find tables, views or scripts with the attributes erasedAT or recordedAT. I cannot find them: how can i get these attributes generated ?

    1. These were present in a previous version, which has since been superceeded by concurrent-reliance-temporal modeling, giving many more features. The online modeling tool generates CRT if you select it for “Temporalization” under the Generate menu.

Leave a Reply

Your email address will not be published. Required fields are marked *