Foreign Key constraint

Question

Foreign Key constraint

naveenreddy.84

Ten Centuries

Points: 1252
More actions
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

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

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

dsdeming Ten Centuries Points: 1236 More actions · Answer 1

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.

naveenreddy.84 Ten Centuries Points: 1252 More actions · Answer 2

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

Tobias Kiemes SSCommitted Points: 1604 More actions · Answer 3

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.

naveenreddy.84 Ten Centuries Points: 1252 More actions · Answer 4

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