SQL Server why am I getting error for creating foreign key constraint for no matching key in referenced table?

Question

I get this error from SQL Server:

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

However, the OrderID column is in the Orders table, so I cannot understand why the error says there is no matching keys in the Orders table. I am obviously doing something wrong but do not know what it is.

My code is fairly short and is:

CREATE DATABASE TestMattressSite
GO

USE TestMattressSite
GO

CREATE TABLE Mattresses 
(
    MattressID INT IDENTITY NOT NULL,
)
GO

CREATE TABLE Customers 
(
    CustomerID INT IDENTITY NOT NULL,
)
GO

CREATE TABLE Orders 
(
    OrderID BIGINT IDENTITY NOT NULL,
    CustomerID INT NOT NULL,
)

CREATE TABLE Cart 
(
    OrderID BIGINT NOT NULL,
    MattressID INT NOT NULL,
    CustomerID INT NOT NULL
)
GO

ALTER TABLE Mattresses 
    ADD CONSTRAINT PK_Mattresses PRIMARY KEY (MattressID)
GO

ALTER TABLE Customers 
    ADD CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)
GO

ALTER TABLE Orders 
    ADD CONSTRAINT PK_Orders PRIMARY KEY (OrderID, CustomerID)
GO

ALTER TABLE Cart 
    ADD CONSTRAINT PK_Cart PRIMARY KEY (OrderID, MattressID)
GO

ALTER TABLE Orders 
    ADD CONSTRAINT FK_Orders_Customers 
        FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
GO

ALTER TABLE Cart 
    ADD CONSTRAINT FK_Cart_Mattresses 
            FOREIGN KEY (MattressID) REFERENCES Mattresses (MattressID),
        CONSTRAINT FK_Cart_Orders 
            FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
        CONSTRAINT FK_Cart_Customers 
            FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
GO

Can someone please take a look and point out my error?

What is the PK of Orders? It is (which is a problem). Your FK must include both columns. — SMor, Dec 18 '20 at 04:09
So you are saying since my key that I am referencing is a composite key, then I must use the whole composite (both fields) even if I want only to use one of the fields as a foreign key, correct? — William S, Dec 18 '20 at 04:15
You **CANNOT** reference on part of a primary key - if your primary key (in `Orders`) contains **two columns** - then all your foreign keys referencing that PK **must also** contain those two columns — marc_s, Dec 18 '20 at 05:11

score 2 · Accepted Answer · edited Dec 18 '20 at 04:52

Since your Orders table have primary key on two columns OrderID and CustomerID, its a composite primary key so when you want to reference this key as foreign key any table, you need to define all the column of composite primary key, like below.

ALTER TABLE Cart ADD
   CONSTRAINT FK_Cart_Mattresses FOREIGN KEY (MattressID)  REFERENCES Mattresses (MattressID),
   CONSTRAINT FK_Cart_Orders FOREIGN KEY (OrderID, CustomerID) REFERENCES Orders (OrderID, CustomerID),
   CONSTRAINT FK_Cart_Customers FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
GO

Or,

If you want your foreign key to have only one column OrderID, change your primary key like this:

ALTER TABLE Orders ADD
   CONSTRAINT PK_Orders PRIMARY KEY (
      OrderID
   )
GO

With this, you current query will work as it is.

There already a question on this, refer this link.

I took a long look at what I was doing and realized that I should have only OrderID as the primary key in my orders table. Thanks for the help and explanations. — William S, Dec 19 '20 at 02:50

SQL Server why am I getting error for creating foreign key constraint for no matching key in referenced table?

1 Answers1