SQL Server error 1776: 'There are no primary or candidate keys in the referenced table'

There are no primary or candidate keys in the referenced table 'dbo.Books' that match the referencing column list in the foreign key 'FK_Loans_Books'. (error 1776) means the foreign key points at a column that is not a primary key and has no unique constraint or index. Make the referenced column unique, or point the foreign key at the primary key instead.

A foreign key must reference something that identifies exactly one row. Add UNIQUE to the referenced column, then create the foreign key again.

The error

ALTER TABLE dbo.Loans ADD CONSTRAINT FK_Loans_Books
    FOREIGN KEY (BookIsbn) REFERENCES dbo.Books (Isbn);

Msg 1776, Level 16, State 1, Server MACHINE\LOCALDB#EAD42A01, Line 2
There are no primary or candidate keys in the referenced table 'dbo.Books' that match the referencing column list in the foreign key 'FK_Loans_Books'.
Msg 1750, Level 16, State 1, Server MACHINE\LOCALDB#EAD42A01, Line 2
Could not create constraint or index. See previous errors.

Why it happens

dbo.Books has an identity Id as its primary key and an Isbn char(13) NOT NULL column with no uniqueness rule. The loans table wanted to refer to books by ISBN. Nothing stops two books from sharing an ISBN, so a loan's ISBN could match two rows, and SQL Server refuses to create a foreign key that cannot point at a single parent. A candidate key is any column set that is declared unique: the primary key, a UNIQUE constraint, or a unique index. Isbn was none of those. Error 1750 then only reports that the constraint was not created.

The fix

ALTER TABLE dbo.Books ADD CONSTRAINT UQ_Books_Isbn UNIQUE (Isbn);
ALTER TABLE dbo.Loans ADD CONSTRAINT FK_Loans_Books
    FOREIGN KEY (BookIsbn) REFERENCES dbo.Books (Isbn);
SELECT name FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'dbo.Loans');

With UQ_Books_Isbn in place the foreign key was created, and sys.foreign_keys listed FK_Loans_Books. The tables were empty here; on a table with data, look for duplicate ISBNs before adding the constraint. The other common fix is to change the child table: store BookId and reference dbo.Books (Id), the primary key. That is usually the better design when the natural value, such as an ISBN, might be corrected later, because the key in every child row then never has to change.

How it was reproduced

One script run with sqlcmd -S "(localdb)\MSSQLLocalDB" -d fix8_lab -I -i e1776.sql -W on SQL Server 2022 LocalDB 16.0.1210.3 and sqlcmd 16.0.1000.6, in a throwaway database fix8_lab created for the test and dropped afterwards. Two empty tables, books and loans. The server's computer name in the messages is replaced by MACHINE; nothing else changed.

Frequently asked

What does 'There are no primary or candidate keys in the referenced table' mean?

SQL Server error 1776 means the foreign key references a column that is neither the primary key nor covered by a UNIQUE constraint or unique index, so it may not identify one row.

Can a foreign key reference a column that is not the primary key?

Yes, if that column is unique. Add a UNIQUE constraint to it, as in ALTER TABLE dbo.Books ADD CONSTRAINT UQ_Books_Isbn UNIQUE (Isbn), then create the foreign key.

How do I fix error 1776 in SQL Server?

Either make the referenced column unique, or change the foreign key to reference the parent table's primary key, for example a BookId column referencing dbo.Books (Id).

More decoded errors in the Fixes category, including error 3726, could not drop a table referenced by a foreign key.