question

Katie 1 avatar image
Katie 1 asked

DDL ERROR.. What is the error here.

CREATE TABLE CALC_OPERATION ( CALCID INT NOT NULL , ORDINAL INT NOT NULL , FUNCTIONNAME VARCHAR(100) ) ALTER TABLE CALC_OPERATION ADD CONSTRAINT PK_CALC_OPERATION PRIMARY KEY CLUSTERED (CALCID,ORDINAL) ALTER TABLE CALC_OPERATION WITH CHECK ADD CONSTRAINT FK_SEMANTICMODEL_CALC FOREIGN KEY([CALCID]) REFERENCES SEMANTIC_CALCMODEL ([CALCID]) GO CREATE TABLE CALC_OPERATION_PARAMETER ( CALCID INT NOT NULL , OPERATIONORDINAL INT NOT NULL , SOURCENAME VARCHAR(100), TYPE VARCHAR(100) ) ALTER TABLE CALC_OPERATION_PARAMETER ADD CONSTRAINT PK_CALC_OPERATION1 PRIMARY KEY CLUSTERED (CALCID,OPERATIONORDINAL) ALTER TABLE CALC_OPERATION_PARAMETER WITH CHECK ADD CONSTRAINT FK_CALC_OPERATION_PERM_CALC FOREIGN KEY([CALCID]) REFERENCES CALC_OPERATION ([CALCID]) GO ALTER TABLE CALC_OPERATION_PARAMETER WITH CHECK ADD CONSTRAINT FK_CALC_OPERATION_PERM_CALC12 FOREIGN KEY([OPERATIONORDINAL]) REFERENCES CALC_OPERATION ([ORDINAL]) GO error > Msg 1776, Level 16, State 0, Line 14 There are no primary or candidate keys in the referenced table 'CALC_OPERATION' that match the referencing column list in the foreign key 'FK_CALC_OPERATION_PERM_CALC'. Msg 1750, Level 16, State 0, Line 14 Could not create constraint. See previous errors. Msg 1776, Level 16, State 0, Line 2 There are no primary or candidate keys in the referenced table 'CALC_OPERATION' that match the referencing column list in the foreign key 'FK_CALC_OPERATION_PERM_CALC'. Msg 1750, Level 16, State 0, Line 2 Could not create constraint. See previous errors.
sql-server-2008primary-keyforeign-keyddlconstraint
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
Your foreign key on `CALC_OPERATION_PARAMETER` table is trying to reference the primary key on `CALC_OPERATION` using just the `CalcID` field, but the PK is defined as `CalcID, Ordinal`. Presumably you meant to have the FK defined as ALTER TABLE CALC_OPERATION_PARAMETER WITH CHECK ADD CONSTRAINT FK_CALC_OPERATION_PERM_CALC FOREIGN KEY([CALCID],OPERATIONORDINAL) REFERENCES CALC_OPERATION ([CALCID],ORDINAL)
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ALTER TABLE CALC_OPERATION_PARAMETER WITH CHECK ADD CONSTRAINT FK_CALC_OPERATION_PERM_CALC123 FOREIGN KEY([CALCID],[OPERATIONORDINAL]) REFERENCES CALC_OPERATION ([CALCID,ORDINAL]) GO Now i get this error Number of referencing columns in foreign key differs from number of referenced columns, table 'CALC_OPERATION_PARAMETER'.
0 Likes 0 ·
`[CALCID,ORDINAL]` means one column with the name 'calcid,ordinal' and `[CALCID], [ORDINAL]` means two columns, one called 'calcid' and one called 'ordinal'.
0 Likes 0 ·
cheers Matt, was just going to say, missing '],['
0 Likes 0 ·
Ya got it. Thank you.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.