CODEWITHSUNDEEP
sqlsql-serverdatabase
M.A.G
M.A.G

Reputation: 549

No primary or candidate key in the referenced table

I have created a table Request which has a primary key made up from three columns, and also a foreign key

CREATE TABLE Request 
(
    Iqama varchar(255) ,
    Cid int,
    ReqID int,

    FOREIGN KEY (Iqama, Cid) REFERENCES Users(Iqama, ID),

    PRIMARY KEY (Cid, Iqama, ReqID)
);

I also created the table below which is a multi-valued attribute for table Request, however I am getting an error

Msg 1776, Level 16, State 0, Line 51
There are no primary or candidate keys in the referenced table 'Request' that match the referencing column list in the foreign key 'FK__Request_Services__151B244E'.

Msg 1750, Level 16, State 1, Line 51
Could not create constraint or index. See previous errors.

The table:

CREATE TABLE Request_Services_chosen
(
    Iqama varchar(255) ,
    Cid int,
    ReqId_ int,
    Servicechosen varchar(255),

    FOREIGN KEY (ReqId_, Iqama, Cid) REFERENCES Request(ReqID, Iqama, Cid),
    PRIMARY KEY (ReqId_, Iqama, Cid, Servicechosen)
);

and here is the Users table:

CREATE TABLE Users 
(
    ID int NOT NULL,
    Iqama varchar(255) NOT NULL,
    Name varchar(255),
    Password varchar(255),
    Phone varchar(255),

    PRIMARY KEY (Iqama, ID)
);

Upvotes: 0

Views: 175

Answers (1)

sticky bit
sticky bit

Reputation: 37472

The primary key in request is defined as (cid, iqama, reqid) but in your REFERENCES clause in request_services_chosen you use (reqid, iqama, cid). That's the wrong order.

Use the same order.

CREATE TABLE Request_Services_chosen(
    ...
    FOREIGN KEY (Cid,Iqama,ReqId_) REFERENCES Request(Cid,Iqama,ReqID),
    ...
);

Upvotes: 3

Related Questions