For a table with a single column (plus FK), isn't LEFT JOIN isomorphic to just having a NULLABLE column (and much better at data locality)?
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.
I had a situation where I'm not really sure I could have used something else than null: I need a value in one of two columns exactly (meaning one is NULL and the other not).
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 ?
You could have the first column indicate the type of value and the second column the value. If you now have your columns named "a" and "b" you could have the first column named "type" only allowing values "a" or "b" and the second column would be "value" where the actual value is stored.
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.