5 ms·
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?
by hagope 4y ago
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?
- cptn_badass 4y agoWhen 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 4y agoin that scenario why not use foreign key? any advantage to using constraint?
- Pxtl 4y agoYou 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 4y agoIf the entity doesn't exist, wouldn't it violate the FK and therefore no need for the nullable constraint?