Documentation:Unique constraints on 1 to 1 links

When you have a 1-to-1 relation between classes, a small (e) appears on the embedded link (where the key is stored).

Technically, you can store the key on both ends, but that is sort of asking for trouble - nothing good comes of it. The recommendation is to have only one (e) in the association.

A single link is implemented in the 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 left a bug I have been unable to track down yet – a single link pointing 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. The obvious way is to 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 which is not what we want or need – since null is on if the association is 0..1-1.

I found that at least SQLServer supports indexes with criteria 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.