Solved

two foreign key constraints on one column

Posted on 2011-02-22
24
918 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:lulubell-b
  • 11
  • 7
  • 4
  • +1
24 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34953114
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 34953195
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 34953221
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
 
LVL 5

Expert Comment

by:jaiminpsoni
ID: 34953819
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34953964
>>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
 
LVL 73

Expert Comment

by:sdstuber
ID: 34954034
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34954244
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 34954329
>>>  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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34954356
>>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
 
LVL 73

Expert Comment

by:sdstuber
ID: 34954485
>>> 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
 
LVL 5

Expert Comment

by:jaiminpsoni
ID: 34954495
Thanks slightwv, I indeed misread the question.

I completely agree that creating a new table looks simpler to maintain.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34954527
>>Your child is orphaned

You are correct.  Point conceded.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 34954615
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 34954648
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
 

Author Comment

by:lulubell-b
ID: 34954702
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 34954736
yes, read the thread above,  create a new table
0
 

Author Comment

by:lulubell-b
ID: 34954970
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 34955005
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 34955081
>>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
 

Author Comment

by:lulubell-b
ID: 34955384
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 34955420
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34955897
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
 

Author Comment

by:lulubell-b
ID: 34973072
No problems, I missed some of the above comments.

Thank you
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34973119
isn't a split in order?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now