This is a really clear explanation of a trap I have seen catch people in production. The key point for me is that NULL means unknown, not empty, so two NULLs are not equal to each other and a UNIQUE constraint does not stop them.

One practical addition: the behaviour is not the same everywhere, so it is worth testing on the database you actually use. PostgreSQL 15 and later gives you NULLS NOT DISTINCT when you really do want only one NULL, while SQL Server treats NULL as a value in a unique constraint and allows only one. In older setups, a partial unique index that only applies where the column is not NULL can be a cleaner fix than adding a sentinel value.

I also agree with the point about composite keys — that is where this bites hardest, because one NULL in the combination can quietly let duplicates through. Checking for existing duplicates before adding the constraint, as part of the migration, has saved me from a failed deploy more than once. Thanks for writing this up so plainly.