This is cool, but wouldn't creating a constraint using a nullable column be considered a poor design decision? In which scenarios would this be a good idea?
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.
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.