x

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.

more ▼

asked Sep 23, 2010 at 10:39 AM in Default

avatar image

Katie 1
1.4k 132 164 205

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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)
more ▼

answered Sep 23, 2010 at 11:01 AM

avatar image

Kev Riley ♦♦
65.9k 48 63 81

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

Sep 23, 2010 at 11:03 AM Katie 1

[CALCID,ORDINAL] means one column with the name 'calcid,ordinal' and [CALCID], [ORDINAL] means two columns, one called 'calcid' and one called 'ordinal'.

Sep 23, 2010 at 11:05 AM Matt Whitfield ♦♦

cheers Matt, was just going to say, missing '],['

Sep 23, 2010 at 11:09 AM Kev Riley ♦♦

Ya got it. Thank you.

Sep 23, 2010 at 11:09 AM Katie 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2170
x43
x42
x35
x25

asked: Sep 23, 2010 at 10:39 AM

Seen: 2029 times

Last Updated: Sep 23, 2010 at 11:31 AM

Copyright 2017 Redgate Software. Privacy Policy