Home | Functions | Tips & Tricks | SQL Server 2005 | SQL Server 2008 | SQL Server 2012 | Forums | FAQ | Practice Test |    
Tip of the Day : Differences Between SET and SELECT When Assigning Variables
Home > SQL Server Error Messages > Msg 1776 - There are no primary or candidate keys in the referenced table 'Table Name' that match the referencing column list in the foreign key 'Foreign Key Constraint Name'
SQL Server Error Messages - Msg 1776 - There are no primary or candidate keys in the referenced table 'Table Name' that match the referencing column list in the foreign key 'Foreign Key Constraint Name'

SQL Server Error Messages - Msg 1776

Error Message:

Server: Msg 1776, Level 16, State 1, Line 1
There are no primary or candidate keys in the referenced
table 'Table Name' that match the referencing
column list in the foreign key 'Foreign Key Constraint
Name'.

Causes:

This error is encountered when creating a FOREIGN KEY constraint on a table and the column being referenced as a FOREIGN KEY is not a PRIMARY KEY on the other table.

To illustrate, let’s say you have the following table definition:

CREATE TABLE [dbo].[Department] (
    [DepartmentID]      INT NOT NULL IDENTITY,
    [DepartmentName]    VARCHAR(50)
)
GO

CREATE TABLE [dbo].[Employee] (
    [EmployeeID]     INT NOT NULL IDENTITY,
    [FirstName]      VARCHAR(50),
    [LastName]       VARCHAR(50),
    [DepartmentID]   INT
)

Based on the business requirements, an employee can only belong to one department.  To make sure that the DepartmentID assigned to the employee exists in the [dbo].[Department] table, you create a FOREIGN KEY constraint on the column:

ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT [FK_Employee_Department]
FOREIGN KEY ( [DepartmentID] ) REFERENCES [dbo].[Department] ( [DepartmentID] )

But since the DepartmentID in the [dbo].[Department] is not designated as a PRIMARY KEY on that table, the following error is encountered:

Server: Msg 1776, Level 16, State 1, Line 1
There are no primary or candidate keys in the referenced table 'dbo.Department'
that match the referencing column list in the foreign key 'FK_Employee_Department'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

Solution/Workaround:

To avoid this error, you have to first create the PRIMARY KEY constraint in the table to be referenced in a FOREIGN KEY constraint.

ALTER TABLE [dbo].[Department]
ADD CONSTRAINT [PK_Department] PRIMARY KEY ( [DepartmentID] )
GO

ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT [FK_Employee_Department]
FOREIGN KEY ( [DepartmentID] ) REFERENCES [dbo].[Department] ( [DepartmentID] )
GO

If the table to be referenced by the FOREIGN KEY constraint already has a PRIMARY KEY and it’s not the column to be referenced by the FOREIGN KEY, you can create a UNIQUE index or UNIQUE constraint on the column.

Using a UNIQUE index:

CREATE UNIQUE INDEX [IX_DepartmentID]
ON [dbo].[Department] ( [DepartmentID] )
GO

ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT [FK_Employee_Department]
FOREIGN KEY ( [DepartmentID] ) REFERENCES [dbo].[Department] ( [DepartmentID] )
GO

Using a UNIQUE constraint:

ALTER TABLE [dbo].[Department]
ADD CONSTRAINT [IX_DepartmentID] UNIQUE ( [DepartmentID] )
GO

ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT [FK_Employee_Department]
FOREIGN KEY ( [DepartmentID] ) REFERENCES [dbo].[Department] ( [DepartmentID] )
GO

Related Topics: