Unanswered: Two foreign keys, one must have a value the other not
It's maybe a theoretical question, but since we're using Oracle I wanted to post here.
We have a table PERSONEL and 2 parent tables, say DepA and DepB.
now PERSONEL has 2 foreign key columns, one for each parent table.
PERSONEL is in this case the child table of DEPA AND DEPB.
but it can only be the child table of one parent table at a time.
so either FK_DEPA or FK_DEPB must have a value but neither can these 2 columns be null nor can they have a value at the same time.
we have to declare both FK fields as nullable, but that's not entirely true, because one of these columns must always have a value.
is there a way of declaring such a constraint/relationship in an RDBMS (such as Oracle) ?
or is there a better solution to this problem (like using only one FK field, if possible) ?
Note : I also posted this question on the Concepts & Design forum.