3 Replies Latest reply: Nov 21, 2012 2:00 AM by jeneesh RSS

    Reg : Alter Referential Constraint --

    ranit B
      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 --
          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 --
            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 --
              You should think of cleaning your data model - it looks strange..