Skip Navigation Links
Home
Functions
Tips & Tricks
SQL Server 2005
SQL Server 2008
SQL Server 2012
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : SQL Server 2008 Management Studio Tips and Tricks – Object Explorer Details – More Property Details
Error Messages
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'

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 / Work Around:

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 Articles :