The foreign key only gaurantees that the other entity exists.
The unique constraint ensures that only one pair of entities has this relationship, preventing a one-to-many binding.
The distinctness of NULL allows you to have multiple entities with the same NULL value without violating the above UNIQUE constraint.
The "NULL is empty" vs "NULL is unknown" is a series of trade-offs of labor-saving. Imho, the wrong trade-offs were made, but once the choice is made it makes sense to continue and be consistent with it. I'd rather be consistently wrong than inconsistently right.
The foreign key only gaurantees that the other entity exists.
The unique constraint ensures that only one pair of entities has this relationship, preventing a one-to-many binding.
The distinctness of NULL allows you to have multiple entities with the same NULL value without violating the above UNIQUE constraint.
The "NULL is empty" vs "NULL is unknown" is a series of trade-offs of labor-saving. Imho, the wrong trade-offs were made, but once the choice is made it makes sense to continue and be consistent with it. I'd rather be consistently wrong than inconsistently right.