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.