- Home /

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.

Comment

Best Answer

**Answer** by Kev Riley
·
Sep 23, 2010 at 11:01 AM

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)

Matt Whitfield

Katie 1

WilliamD

Oleg

Fatherjack

Grant Fritchey

Katie 1

WilliamD

Oleg

Fatherjack

Grant Fritchey

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'.

PK, FK and Relationships 1 Answer

Delete error 2 Answers

Reproducible Deadlock Using Cascading Constraints 2 Answers

PRIMARY - FORIEGN KEY ERROR 2 Answers

Copyright 2018 Redgate Software. Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Ask SSC Site Issues (meta-askssc)
- Explore
- Topics
- Questions
- Users
- Badges