Can't create Foreign Key in Sql Server

1.7k views Asked by At

I have two tables: SDCUST and the newly created SDRTS (defined below).

SDCUST table definition

enter image description here

DROP TABLE SDRTS
  GO
  CREATE TABLE SDRTS(
    BGTID int NOT NULL,
    RTSID uniqueidentifier NOT NULL,
    CONSTRAINT PK_SDRTS PRIMARY KEY CLUSTERED (BGTID,RTSID),
    CUSTID uniqueidentifier NOT NULL,
    CONSTRAINT FK_SDRTS_SDCUST FOREIGN KEY(CUSTID)
    REFERENCES SDRTS(CUSTID),
    BGRPID_1 uniqueidentifier NOT NULL,
    BGRPID_2 uniqueidentifier NOT NULL,
    INCRT FLOAT NULL,
    CURRID int NOT NULL,
    EDATE datetime NULL,
    EUSRID uniqueidentifier NULL,
    UDATE datetime NULL,
    UUSRID uniqueidentifier NULL,
    V001 float NULL,

I want CUSTID to be the foreign key on the new table, but I get an error:

Msg 3701, Level 11, State 5, Line 1 Cannot drop the table 'SDRTS', because it does not exist or you do not have permission.
Msg 1776, Level 16, State 0, Line 3 There are no primary or candidate keys in the referenced table 'SDRTS' that match the referencing column list in the foreign key 'FK_SDRTS_SDCUST'.
Msg 1750, Level 16, State 0, Line 3 Could not create constraint or index. See previous errors.

How can I create this key?

1

There are 1 answers

0
Mark Schultheiss On

Just to follow up on the comments this was resolved by:

Add both columns. CONSTRAINT FK_SDRTS_SDCUST FOREIGN KEY(BGTID, CUSTID) REFERENCES SDCUST(BGTID, CUSTID)