T-SQL Tutorial

Msg 1776 Level 16 - There are no primary or candidate keys in the referenced table


On Transact SQL language the Msg 1776 Level 16 - There are no primary or candidate keys in the referenced table means that the primary key is missing.

Msg 1776 Level 16 Example:

Invalid alter:

USE tempdb;
GO
ALTER TABLE dbo.EMPLOYEES
ADD CONSTRAINT FK_ID_DEP FOREIGN KEY (DEPT_ID)
REFERENCES dbo.departments (ID);
GO

Message
Msg 1776, Level 16, State 0, Line 3
There are no primary or candidate keys in the referenced table 'dbo.departments' that match the referencing column list in the foreign key 'FK_ID_DEP'.
Msg 1750, Level 16, State 1, Line 3
Could not create constraint or index. See previous errors.




Correct alter:

USE tempdb;
GO
ALTER TABLE dbo.departments
ADD CONSTRAINT PK_DEPT_ID PRIMARY KEY CLUSTERED (ID);
GO
ALTER TABLE dbo.EMPLOYEES
ADD CONSTRAINT FK_ID_DEP FOREIGN KEY (DEPT_ID)
REFERENCES dbo.departments (ID);
GO

Message
Command(s) completed successfully.

Other error messages: