Enforcing "zero-or-one to one" relationship on SQL database?

Question

I have a Post entity and a FbPost entity.

Post.FbPost is either null or a FbPost, and no two Post entities can reference the same FbPost entity. In other words, zero-or-one to one.

What is the proper way to enforce this as zero-or-one to one instead of many to one in SQL Server (ideally)?

If that's not possible, how can I enforce this on EF level?

Answer 1

Normally, you would probably attempt to have the foreign key (in the source table) be nullable and place a unique constraint on it.

The fact that it's nullable means that you can have an null entry in the source table that does not refer to an entry in the target table. And, if it's not null, the unique constraint ensures that only one row of the source table can reference a row in the target table.

Unfortunately, at least with SQL Server ^(a), NULLs in uniquely constrained columns have to be unique as well, although this "breaks" the SQL guideline that NULL is not equal to any value, including another NULL. So basically, this method won't work with SQL Server.

One possible way out of this quandary ^(b) is a foreign key constraint with a nullable column, but no unique constraint. This will allow you to ensure you either don't reference a row in the target table at all (NULL in the source table) or reference a target row (any non-NULL value in the source table).

It doesn't, however, give you your "only one source row can reference the target row" requirement. That could be added with an before-(insert/update) trigger which would check every other row in the source table to ensure no other row already references the target row.

And you should almost always prefer constraints in the database itself. You never know when a rogue application (malignant or buggy) will connect to your database and decide not to follow the rules.

---

^(a) The following text, paraphrased from here, shows the differing support for nullable unique columns in several DBMS products:

The standard:

As the constraint name indicates, a (set of) column(s) with a UNIQUE constraint may only contain unique (combinations of) values.

A column, or a set of columns, which is subject to a UNIQUE constraint must also be subject to a NOT NULL constraint, unless the DBMS implements an optional "NULLs allowed" feature (feature ID 591). The optional feature adds some additional characteristics to the UNIQUE constraint:

First, columns involved in a UNIQUE constraint may also have NOT NULL constraints, but they do not have to. Secondly, if columns with UNIQUE constraints do not also have NOT NULL constraints, then the columns may contain any number of NULL 'values' (a logical consequence of the fact that NULL is not equal to NULL).

PostgreSQL:

Follows the standard, including the optional NULLs allowed feature.

DB2:

Follows the non-optional parts of the UNIQUE-constraint. Doesn't implement the optional NULLs allowed feature.

MSSQL:

Follows the standard with a twist.

MSSQL offers the NULLs allowed feature, but allows at most one instance of a NULL 'value', if NULLs are allowed. In other words, it breaks characteristic 2 in the above description of the standard.

MySQL:

Follows the standard, including the optional NULLs allowed feature.

Oracle:

Follows the standard with a twist regarding multi-column UNIQUE-constraints.

The optional NULLs allowed feature is implemented: If the UNIQUE-constraint is imposed on a single column, then the column may contain any number of NULLs (as expected from characteristic 2 in the above description of the standard). However, if the UNIQUE-constraint is specified for multiple columns, then Oracle sees the constraint as violated if any two rows

• contain at least one NULL in a column affected by the constraint
• identical, non-NULL values in the rest of the columns affected by the constraint

---

^(b) The other way out, of course, is to choose a DBMS that implements this feature, like PostgreSQL or MySQL.

That may not be possible in your specific case but it should be at least contemplated. For example, I steer clear of Oracle because of its inability to discern NULLs from empty strings in certain character columns, though others aren't probably as "purist" (my wife would say "anal retentive") as I am :-)

Answer 2

Create a unique filtered index:

CREATE UNIQUE INDEX Post_Unq_FbPost ON dbo.Post(FbPost) WHERE FbPost IS NOT NULL;

Also create a foreign key, of course.