[cptn_badass]

When an entry can belong to 0 or 1 related object only. Not that I'd put a constraint in such scenario, but I imagine a User can optionally have a Subscription, so subscription_id is either nil or present, and said subscription cannot be associated to any other User.

[hagope]

in that scenario why not use foreign key? any advantage to using constraint?

[Pxtl]

You do both.

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.

[hagope]

If the entity doesn't exist, wouldn't it violate the FK and therefore no need for the nullable constraint?