Adding same foreign key to two columns

Question

Adding same foreign key to two columns

rami.atedgi

Right there with Babe

Points: 776
More actions
November 29, 2007 at 5:47 am

#179577

Hello,
I have table named 'a' that have 2 columns 'aa' and 'cc' corresponding to 2 PK columns in table 'b' -'aa' and 'cc' respectively
can I assign the same constraint_name to both columns and if yes
(because I refer to both columns as complex key)
what is the syntax (query) for this?
Thank
Rachamim

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

This looks slightly like a homework question. Yes, you can have a foreign key constraint wherein two columns in one table reference two columns in another. The syntax is in the ALTER TABLE topic in Books Online... try it and post back if you're having trouble.

John

rami.atedgi Right there with Babe Points: 776 More actions · Answer 2

yes ,I have a problem

my query look like this:

CREATE TABLE BATCHES_LOT_DETAILS ([BATCHNO] varchar (15) ,[CALCULATED] char (1) ,[ORDNO] varchar (13) ,[ORIGREC] int ,[QTY] numeric ,[TRANSFERID] int CONSTRAINT PK_BATCHES_LOT_DETAILS PRIMARY KEY (BATCHNO,ORDNO,TRANSFERID ) CONSTRAINT FK_BATCHES_LOT__BATCHES_LOTS01 FOREIGN KEY ( BATCHNO,TRANSFERID ) REFERENCES BATCHES_LOTS ( BATCHNO,TRANSFERID) ON DELETE CASCADE ON UPDATE CASCADE)

and the result:

Msg 8140, Level 16, State 0, Line 1

More than one key specified in column level FOREIGN KEY constraint, table 'BATCHES_LOT_DETAILS'.

can you help me to repair it?

Rachamim

Lowell SSC Guru Points: 323518 More actions · Answer 3

when i reformatted your sql for readability, it was missing a couple of commas.

this works:

CREATE TABLE BATCHES_LOTS (

[BATCHNO] varchar (15),

[TRANSFERID] int,

CONSTRAINT PK_BATCHES_LOT PRIMARY KEY (BATCHNO,TRANSFERID ))

CREATE TABLE BATCHES_LOT_DETAILS (

[BATCHNO] varchar (15) ,

[CALCULATED] char (1) ,

[ORDNO] varchar (13) ,

[ORIGREC] int ,[QTY] numeric ,

[TRANSFERID] int ,

CONSTRAINT PK_BATCHES_LOT_DETAILS PRIMARY KEY (BATCHNO,ORDNO,TRANSFERID ) ,

CONSTRAINT FK_BATCHES_LOT__BATCHES_LOTS01 FOREIGN KEY ( BATCHNO,TRANSFERID )

REFERENCES BATCHES_LOTS ( BATCHNO,TRANSFERID)

ON DELETE CASCADE

ON UPDATE CASCADE)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!