I have a Table- 'X' which has a Foreign Key reference on Table- 'Y'.
So, till now a Procedure was putting data into X (pulling from Y).
But now I'm changing this Procedure to pull data from 2 new tables- 'A' & 'B'. NOTE - All 3 tables- 'Y', 'A' and 'B' pulls data into a single common column of table 'X'.
Also, all 3 tables have different column names (but of same data type i.e NUMBER)
1] How can I alter my Foreign Key to add these 2 new tables also?
2] Can I have a REFERENCES clause with more than 2 tables?
Can anybody please provide some ideas.
Edited by: ranit B on Nov 21, 2012 1:10 PM
No, as per my knolwedge, you can have reference from one table.
Also, if you are looking for existince of records in both A & B you can add two foreign key constraints on same column by defining constraints at table level. but you ar elooking at A or B you can write a trigger to work as constraint ( before statement trigger) and verify the existence from A and Btables .
Edited by: Kiran on Nov 20, 2012 11:50 PM
IMHO the data model for a common column for 3 different tables (Y, A and B) doesn't look so nice.
I would prefer to merge data from Y, A and B into one table, maybe by adding a new column to distinguish the type of row if you need.
Just think when you have to draw relationship diagram: you will have one column which is referring to 3 different tables. Also in case you have to look up for parent data reading child table you might have to check up to 3 different tables. Is it good?