Syntax for Composite Foreign Key

syed-1919 · New User Joined: 26 Oct 2006 Posts: 12 Location: Chennai

Hi,

Could anyone please provide the ALTER statement syntax for a composite Foreign Key????

For ex. I have the following tables:

Table1 (Col1, Col2, Col3, Col4)
Table2 (Col1, Col2, Col3, Col4)
Table3 (Col1, Col2, Col3, Col4)

Now Col1 and Col2 of Table2 refer to the Col1 and Col2 of Table1. Insert into Table2 should pass only when the combination of Col1 and Col2 of table2 is equivalent to Col1 and Col2 of table1.

Similarly, Col1 and Col2 of Table1 should refer to the Col1 and Col2 of Table1 entirely.

Hope the problem is clear.

Cheers,
Syed

syed-1919 · New User Joined: 26 Oct 2006 Posts: 12 Location: Chennai

Sorry there is a typo in my post:

Incorrect:
-----------

Similarly, Col1 and Col2 of Table1 should refer to the Col1 and Col2 of Table1 entirely.

Correct:
----------

Similarly, Col1 and Col2 of Table3 should refer to the Col1 and Col2 of Table1 entirely.

nuthan · Posted: Thu Oct 11, 2007 3:27 pm

ALTER TABLE TABLE2
ADD ( FOREIGN KEY (COL1, COL2, COL3)
REFERENCES TABLE1 (COL1, COL2 COL3));

syed-1919 · New User Joined: 26 Oct 2006 Posts: 12 Location: Chennai

Hi Nuthan,

Thanks a lot...I hope the statement would work for the following scenario

Table1
--------

Col1 Col2 Col3
----- ----- ------
aaa xxx 111
bbb yyy 222
ccc zzz 333

Now after the statment u specified is successful, we try to insert the following record in the Table2

Insert into Table2 Values (aaa,yyy,333);

I want this statement to fail...Though they are the values of the referred columns, the combination is invalid...So I believe the ALTER statement you specified would handle this...

Thanks again,
Syed

nuthan · Posted: Fri Oct 12, 2007 9:49 am

Try and let me know..