❌ Msg 1776 SQL Server Error: There Are No Primary or Candidate Keys That Match the Referencing Column List

SQL Server Error Explained: Cannot Create Foreign Key When Referencing Only One Column of a Composite Primary Key

Introduction

In SQL Server, foreign keys must exactly match the primary key (or a unique key) they reference. A very common mistake is trying to reference only one column from a composite primary key. This results in a foreign key creation error.

This blog explains:

Why does the error occur
The exact SQL Server error message
How to fix it correctly

Parent Table with Composite Primary Key

CREATE TABLE dbo.ParentTable
(
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 INT NOT NULL,
    parent_data VARCHAR(50),
    CONSTRAINT PK_ParentTable PRIMARY KEY (col1, col2, col3)
);

Here, (col1, col2, col3) together uniquely identify a row.

❌ Wrong: Foreign Key Using Only One Column

CREATE TABLE dbo.ChildTable
(
    col1 INT NOT NULL,
    child_data VARCHAR(50),
    CONSTRAINT FK_ChildTable_ParentTable
        FOREIGN KEY (col1)
        REFERENCES dbo.ParentTable (col1)
);

❗ SQL Server Error Message

Msg 1776, Level 16, State 0
There are no primary or candidate keys in the referenced table ‘ParentTable’ that match the referencing column list in the foreign key ‘FK_ChildTable_ParentTable’.

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

❓ Why This Error Happens

ParentTable does NOT have a primary key or unique constraint on col1 alone
SQL Server does not allow partial references to a composite primary key
Referential integrity would be broken if this were allowed

✅ Correct Way: Match the Composite Key Exactly

CREATE TABLE dbo.ChildTable
(
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 INT NOT NULL,
    child_data VARCHAR(50),
    CONSTRAINT FK_ChildTable_ParentTable
        FOREIGN KEY (col1, col2, col3)
        REFERENCES dbo.ParentTable (col1, col2, col3)
);

✔ Column count matches (3 → 3)
✔ Order matches
✔ Data types match
✔ Referenced columns are a PRIMARY KEY

Optional: Using Cascade Delete

FOREIGN KEY (col1, col2, col3)
REFERENCES dbo.ParentTable (col1, col2, col3)
ON DELETE CASCADE
ON UPDATE NO ACTION

This automatically deletes child rows when the parent row is deleted.

Key Takeaways

✔ Composite Primary Key → Composite Foreign Key
❌ You cannot reference only one column
✔ SQL Server enforces strict referential integrity
✔ Error 1776 clearly indicates a key mismatch

Conclusion

If your parent table uses a composite primary key, your foreign key must include all columns of that key. Referencing only one column will always fail — and SQL Server will protect your data by design.

Happy querying! 🚀