This discussion is archived
3 Replies Latest reply: Nov 21, 2012 12:00 AM by jeneesh RSS

Reg : Alter Referential Constraint --

ranit B Expert
Currently Being Moderated
Hi Experts,

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.

Ranit B.

Edited by: ranit B on Nov 21, 2012 1:10 PM
  • 1. Re: Reg : Alter Referential Constraint --
    908002 Expert
    Currently Being Moderated
    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
  • 2. Re: Reg : Alter Referential Constraint --
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi Ranit,

    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?

  • 3. Re: Reg : Alter Referential Constraint --
    jeneesh Guru
    Currently Being Moderated
    You should think of cleaning your data model - it looks strange..


  • Correct Answers - 10 points
  • Helpful Answers - 5 points