Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Syntax for Composite Foreign Key

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
syed-1919

New User


Joined: 26 Oct 2006
Posts: 12
Location: Chennai

PostPosted: Thu Oct 11, 2007 12:17 am    Post subject: Syntax for Composite Foreign Key
Reply with quote

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
Back to top
View user's profile Send private message

syed-1919

New User


Joined: 26 Oct 2006
Posts: 12
Location: Chennai

PostPosted: Thu Oct 11, 2007 2:06 am    Post subject: Reply to: Syntax for Composite Foreign Key
Reply with quote

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.
Back to top
View user's profile Send private message
nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore

PostPosted: Thu Oct 11, 2007 3:27 pm    Post subject:
Reply with quote

ALTER TABLE TABLE2
ADD ( FOREIGN KEY (COL1, COL2, COL3)
REFERENCES TABLE1 (COL1, COL2 COL3));
Back to top
View user's profile Send private message
syed-1919

New User


Joined: 26 Oct 2006
Posts: 12
Location: Chennai

PostPosted: Thu Oct 11, 2007 10:33 pm    Post subject: Reply to: Syntax for Composite Foreign Key
Reply with quote

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
Back to top
View user's profile Send private message
nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore

PostPosted: Fri Oct 12, 2007 9:49 am    Post subject:
Reply with quote

Try and let me know..
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Syntax for running batch history repo... polymathtarun CA Products 1 Tue Jun 21, 2016 1:51 pm
No new posts Syntax to check the first 4 digits of... kshirabdhi DFSORT/ICETOOL 10 Tue May 03, 2016 12:21 pm
No new posts Syntax to create new tableBASE library Sumeendar IBM Tools 1 Tue Sep 15, 2015 6:25 pm
No new posts Illegal symbol syntax error HABBIE DB2 5 Wed Aug 26, 2015 5:58 pm
No new posts PL/1 and COBOL syntax list khaarthikm PL/I & Assembler 1 Thu Jul 16, 2015 6:30 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us