two foreign key constraints on one column

Hello,

Is it possible to assign two foriegn keys on one column? The meaning of this column is one or the other, but I want to ensure referential integrity.  We are unable to alter the structure due to past issues. Can we perform this?

Thank you
lulubell-bAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>becuase it doesn't exist in both places.

Please clarify.  If you've read our posts, we have taken this question to possibly mean two different things.

Data existing in BOTH places is different from EITHER place.

Are you checking a child record against two possible parent tables?
create table child(id char(1), parent_id char(1));
create table parent1(id char(1));
create table parent2(id char(1));
--the child's parent MUST be in EITHER of the parent tables

or the reverse?
0
 
slightwv (䄆 Netminder) Commented:
I believe a true FK is just to one table.

You can add a trigger to enforce this since you cannot change the structure.
0
 
sdstuberCommented:
each FK would be from a different child table

a single child can have the same parent twice but only for different columns

a single column (or group of columns) can't be FK'd to the same parent key twice
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sdstuberCommented:
for instance,  if you had a table of people, each row has 3 columns (personid, fatherid, motherid)

the fatherid and motherid columns could each have their own FK pointing back to personid
but you couldn't have fatherid FK'd twice to personid
0
 
jaiminpsoniCommented:
Although in good designs you will not come under such situations, syntactically you can create 2 FKs on single column.

I tried that and it worked for me....

SQL> create table T1 (id number (2));
Table created.
SQL> create table T2 (id number (2) primary key);
Table created.
SQL> create table T3 (id number (2) primary key);
Table created.
SQL> alter table T1 add constraint FK1 foreign key (id) references T2(id);
Table altered.
SQL> alter table T1 add constraint FK2 foreign key (id) references T3(id);
Table altered.
SQL> insert into T1 values (1);
insert into T1 values (1)
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.FK2) violated - parent key not found
SQL> insert into T3 values (1);
1 row created.
SQL> insert into T1 values (1);
insert into T1 values (1)
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.FK1) violated - parent key not found
SQL> insert into T2 values (1);
1 row created.
SQL> insert into T1 values (1);
1 row created.
SQL>
0
 
slightwv (䄆 Netminder) Commented:
>>syntactically you can create 2 FKs on single column.

I do not believe this is what is being asked.  I take the question to mean (using your example), when inserting a 1 into T1, it must exist in EITHER t2 or t3.  Not both.
0
 
sdstuberCommented:
ah - I misread that too.


In that case,  I suggest creating a sister table to the two parents.

The sister will have all id's from both parent tables (use a trigger or create it as a materialized view to enforce this) then use a FK constraint from the child to this sister table rather than directly to the parents.

Alternatively (and better),  you could have the parents be children of this new table too; but that might be unfeasible within an existing application
0
 
slightwv (䄆 Netminder) Commented:
Maybe I misread it?

I also doubt they can create a sister table "We are unable to alter the structure due to past issues"

If I read it correctly, I feel triggers are the simplest solution.
0
 
sdstuberCommented:
>>>  I feel triggers are the simplest solution.

except triggers can't maintain integrity

If I delete/modify your parent but haven't committed, your trigger will see the old value, when I commit, your child will now be orphaned


>> We are unable to alter the structure

actually that's exactly why I suggested a new table,  other than adding contraints (which is the request anyway) none of the existing tables need to change
0
 
slightwv (䄆 Netminder) Commented:
>>except triggers can't maintain integrity

Sure you can.  You just need triggers on BOTH the parent and child tables.

A before delete to check for children?

>>actually that's exactly why I suggested a new table,

I guess if they could create triggers, they might be able to create a new table.  Just added space.  It might be simpler to maintain as well instead of multiple triggers.
0
 
sdstuberCommented:
>>> A before delete to check for children?

Nope, still no go, simple test case...

1 Parent , no children

I delete parent  - delete trigger says ok, no other activity
You create child - insert trigger says ok, you can't see my delete
I commit
You commit

Your child is orphaned
0
 
jaiminpsoniCommented:
Thanks slightwv, I indeed misread the question.

I completely agree that creating a new table looks simpler to maintain.
0
 
slightwv (䄆 Netminder) Commented:
>>Your child is orphaned

You are correct.  Point conceded.
0
 
sdstuberCommented:
Note, there is still a problem of maintaining the new table.

If the parents aren't made children of the new table then there is still the possibility of a hole.

but, among a selection of imperfect choices, I do think it's the easiest
0
 
sdstuberCommented:
on second thought, it might be ok.  Unlike a trigger-only approach,  the new table would have real FK's on it, so triggers from the parents to the new table would cause cascades/locks/errors, etc.

too many moving parts to keep track in my head.  
Try it, test it, confirm/deny for yourself.
Which you should do anyway.
0
 
lulubell-bAuthor Commented:
SQL> alter table T1 add constraint FK1 foreign key (id) references T2(id);
Table altered.
SQL> alter table T1 add constraint FK2 foreign key (id) references T3(id);


I tried adding the above code to my existing table and its giving me a constraint error. I think I know why because the ID has to exists in both places. Is there a way to set it up to say either the ID exists in the one table OR the ID exists in the other.
0
 
sdstuberCommented:
yes, read the thread above,  create a new table
0
 
lulubell-bAuthor Commented:
I tried again with accurate data it will not let me insert data becuase it doesn't exist in both places.

I will have to use the trigger approach.

Thanks
0
 
sdstuberCommented:
did you read the thread above about creating the new table? Did you have questions?
What did you try?

using the new table you don't have FK's to the parents you have FK's to the new table.

 triggers don't work, see above discussion and simple example showing why
0
 
lulubell-bAuthor Commented:
1. I deleted all information out of column
2. I was able to create tow foriegn keys on a single column -- good
3. Inserted the data and the received the RI error.

I crossed checked the data to make sure it exists in one or the other and it does
0
 
sdstuberCommented:
There is only one foreign key involved with the new table as explained above.

One FK from the child to the new table


OR, if you can do it (maybe not with existing application)


3 foreign keys,  one from each parent to the new table,  one from the child to the new table.


In no variation of an "either/or" does it involve 2 FK's from the child to anything
0
 
slightwv (䄆 Netminder) Commented:
We are not understanding one another.

We've explained how two FK's cannot do an EITHER TABLE and con only do a BOTH TABLES.

I also believe there is confusion about the design:
1 child table, either of two parents
or
2 child tables 1 parent.
0
 
lulubell-bAuthor Commented:
No problems, I missed some of the above comments.

Thank you
0
 
sdstuberCommented:
isn't a split in order?
0
All Courses

From novice to tech pro — start learning today.