These are interesting questions, and they require quite a bit of work in SQL Server due to limitations of its functionality. Because of this, we generally recommend that "business rules" like the ones you describe are handled in the application layer. Note that these become even trickier when you do bitemporal modeling.
First, to guarantee
uniqueness of an attribute value.
STATIC ATTRIBUTE
value unique within the attribute table,
simple constraintHISTORIZED ATTRIBUTE
value unique within the attribute table at every point in time,
complex constraintThe constraint must prevent:
TimepointID 1234567891 |--A-|-B-2 |--A---The constraint must allow:
TimepointID 1234567891 --A-|--B-2 |--A-For example:
The value AC_NAM_Actor_Name is
not unique within the table AC_NAM_Actor_Name, but it
is unique with respect to the point-in-time perspective pAC_NAM_Actor_Name(corresponding AC_NAM_ChangedAt).
Secondly, to ensure
existence of an attribute value.
STATIC & HISTORIZED ATTRIBUTES
value is not null with respect to the latest view of the anchor,
theoretically possible constraintFor example:
The value AC_NAM_Actor_Name is not null in the view lAC_Actor.
Unfortunately SQL Server does not allow constraints on views (yet), so the only way I see this being set up is as a check constraint in the anchor, that ensures a row exists in AC_NAM_Actor_Name for every AC_ID. However, AC_ID in AC_NAM_Actor_Name is a foreign key to AC_Actor(AC_ID), meaning that the anchor row must exist before the AC_NAM_Actor_Name row, preventing such a constraint from working. But, disabling the FK may have other consequences, like rendering the insert/update/delete triggers unusable. In other words, may be hard to achieve in practice.
Anyone have any other ideas?
