Implementing constraints

One common question I am asked is how constraints are implemented in an Anchor modeled database. Temporality make constraints less straightforward than in static tables. With unitemporal history the constraint needs to hold for every possible time slice in the database given every point in changing time (t_c). If concurrency and bitemporal history is used, they need to hold for every possible time slice for every positor given every point in bitemporal time (p, t_p, t_c). The good thing is that a slice behaves just like a static database. It is a snapshot of what the information looked like at that point.

Implementing this using a CHECK constraint would be quite cumbersome. Even if you can limit the points of time that needs to be checked to those actually used in the database they quickly grow to a large number. The better way is to do the checking with an AFTER INSERT trigger. At insert time, it is possible to check only against those slices that are affected by the insert.

Below is a script that generates a unique constraint for the stage name in our example model. Note that time slice checking is done by applying the point-in-time perspective of the affected anchor. This “template” for constraints can of course be extended to include more complex rules, such that two performances cannot be held on the same stage on the same date, found further down in the same script.

Note that in the case of the performance date constraint, we do not have to take time into account, since none of the involved constructs are historized. However, there is one additional important consideration. Since the trigger joins the tie, the tie is expected to have its rows populated before the attributes. In other words, for the trigger to detect duplicates, the loading order must be:

1. Generate the desired number of identities in the performance anchor.
2. Populate the tie by connecting these identities to the associated stages.
3. Populate the attributes on the performance anchor.

If the loading order cannot be guaranteed, a similar constraint must be placed on the tie. In that case, attributes will pass through its trigger if loaded first, since join with the tie “truncates” the result set, but the trigger on the tie will fail because of duplicates. A larger transaction containing the two steps would then still roll back to what the database looked like before the insert.