This article was moved to MDriven Wiki – Unique constraints on 1 to 1 links
When you have a 1 to 1 relation between classes a small (e) appears on the link that is embedded (where the key is actually stored).
Technically you can store the key in both ends but that is sort of just asking for trouble and nothing good comes out of it. So the recommendation is to have only one (e ) in the association.
A single link is implemented in the exact same way as a multilink – i.e a foreign key on one end. The fact that it is a single link is handled further up in the model logic.
This has apparently left a bug that I have not been able to track down as of yet – a single link that points to two or more objects.
When I found this fact in a running system I started to think about how to stop that from happening. And the obvious way is add a unique constraint to the foreign key when it is a 1-1 link. But a straight up unique constraint will disallow multiple nulls and that is not what we want or need – since null is on if the association is 0..1-1.
Then I found that at least SQLServer supports indexes with criterias like this:
CREATE unique NONCLUSTERED INDEX IX_test ON ConsolidationRequest
(WinnerID) where WinnerID is not null
Dailybuilds onwards does this – but since not all DB’s like it there is a flag in the DBConfig; SupportsFilteredIndex.
BoringPredictableServer sets this to true for MSSql, but not for SQLCe of MySql…