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.
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)
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'.
`[CALCID,ORDINAL]` means one column with the name 'calcid,ordinal' and `[CALCID], [ORDINAL]` means two columns, one called 'calcid' and one called 'ordinal'.
