November 29, 2007 at 5:01 am
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
November 29, 2007 at 6:16 am
Do you mean something like this:
ALTER TABLE dbo.X
ADD CONSTRAINT MyConstraint
FOREIGN KEY (aa,bb)
REFERENCES dbo.Y(aa,bb)
Like that?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 29, 2007 at 6:24 am
yes,
this what I wrote in my query;
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)
but when I do so its gives me the following error:
Msg 8140, Level 16, State 0, Line 1
More than one key specified in column level FOREIGN KEY constraint, table 'BATCHES_LOT_DETAILS'.
November 29, 2007 at 7:02 am
You've got a slight syntax error. Your code should 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
)
The comma's in front of the constraint definitions change it from a column to a table constraint. The script as written is trying to constrain a sinigle column, but referring to more than one. Change it and you should be good to go.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 29, 2007 at 7:17 am
Thank you very much!:)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply