IMO the problem (at least in this case) is not NULL in the DB, but NULL at the application level.
If NULL is some sort of Maybe monad and you're forced to deal with it, well, you're forced to deal with it, think about it, etc.
Empty string, whatever NULL string is in your language of choice, or some sort of sigil value you invent... not much of a difference.
An empty string is better as a sentinel value because at least this doesn't have the weird "unknown value" semantics that NULL does. But if you really want the same level of explicitness and safety as an option type, the theoretically proper way to do this in relational model is to put the strings themselves in a separate table in a 1:N (where N is 0 or 1) relationship with the primary table.
A generated UUID, if possible, is probably much more clear because it will only be there if it was inserted very deliberately.
But at that point honestly I'd usually prefer breaking the field out into its own table.
In this case, a `user_uris` table with non-nullable columns and a unique constraint on `user_id` is the first option that comes to mind.
You can build a constraint to check that if it's in the same table, but across tables it seems to be a bit more complex right ?
It might prevent error if you only rely on INNER JOIN but that's rarely the case at least for me (you often want to access the record anyways).
Much safer to deal with the NULL at the application level.
Yes! NULL is relational for “don’t know”, and SQL is (mostly, with varying degrees of success) designed to treat is as such. That’s why NULL=anything is NULL and not e.g. false (and IMO it’s a bit of a misfeature that queries that branch on a NULL don’t crash, although it’s still better than the IEEE 754 NaN=anything outright evaluating to false). If the value is funky but you do know it, then store a funky value, not NULL.
Might or might not be based in production data I deal with on a daily basis.