I have a relational database with an Address entity that should have a parent Address or a parent Unit, but not both. Currently the schema is Address having a ParentUnitID field and an ParentAddressID field. Is there a way to change this that prevents an Address from having both a parent Address and a parent Unit at the same time, but retains the foreign key constraints?
Asked
Active
Viewed 83 times
1
-
1Add a `CHECK` constraint, enforcing that exactly one of the FKs is non-null. This _might_ need to be implemented by a trigger, depending on your DBMS. – wildplasser Oct 09 '13 at 19:40
-
1You could have foreign key constraints to both possible parents (i.e. two distinct foreign key columns) and then a CHECK constraint that ensures one or the other is always NULL. – Joel Brown Oct 09 '13 at 19:40
-
You should propose this as an answer so he can select it as the answer – John Christman Oct 09 '13 at 19:45
-
Define unit, what is unit? – Damir Sudarevic Oct 09 '13 at 20:13
1 Answers
0
A FOREIGN KEY is not enforced on a NULL value, so you just need to make sure that only one of them is non-NULL:
CHECK (
(ParentUnitID IS NOT NULL AND ParentAddressID IS NULL)
OR (ParentUnitID IS NULL AND ParentAddressID IS NOT NULL)
)
If you happen to use MySQL, you'll need to implement this as a trigger, since MySQL doesn't enforce CHECK constraints.
Alternatively, you could use inheritance, as described here.
Community
- 1
- 1
Branko Dimitrijevic
- 50,809
- 10
- 93
- 167