Post reply

Foreign Key constraint

  • October 6, 2009 at 3:03 am

    #215105

    Hi all,

    Can you suggest me how to achieve the following scenario.

    I have table table1 with a column LocationNo. This should refer to column storeNo and depoNO of two tables table2 and table3 respectively.I,e the LocationNo data should refer to values either from table2 or table3. No value should be allowed to enter into LocationNo, if they are not in table2 or table3.

    Can foreign key constraint help me in this scenario?

    I tried doing this, its doesnt allow me to enter a value which are not present in both the tables table2 and table3.However, i can enter the value in locationNo if the value are present in both table2 and table3

    Kindl;y suggest

  • October 6, 2009 at 5:55 am

    #1062177

    Are you entering non-NULL values in both storeNo and depoNO? A foreign key should only cause an error if you enter a non-NULL value that is not found in the primary key table.

  • October 6, 2009 at 7:27 am

    #1062231

    i am entering a non-null values, which are present in either of the two primary tables

  • October 6, 2009 at 7:28 am

    #1062233

    If I understand your requirements, you would need a foreign key, that refers to table2 XOR table3. That's not possible. If you are not working with performance critical data you can create a trigger on table1 which checks the requirements.

  • October 6, 2009 at 7:41 am

    #1062244

    So,i can establish foreign key relationship with column in the primary table(whether the same table or different table).

    Thats right i was trying to insert value in table1 from the values in either table2 or table3 via with a foreign key constraint

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply