Home/ Questions/How to deal with ''there are no primary or candidate keys in the referenced table'' error?
Next
Answered
lloydbeier
  • 4
Asked: 2022-07-28T17:06:44+00:00 In:

How to deal with ”there are no primary or candidate keys in the referenced table” error?

  • 4

. Advertisement .

..3..

. Advertisement .

..4..

Hi everyone,

Today when I run my program, I get the error ”there are no primary or candidate keys in the referenced table”. 

I have the 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
)

An employee can only be a member of one department, depending on the business requirements.I add a FOREIGN KEY constraint to the employee exists to ensure that the DepartmentID assigned to the employee exists in the [dbo].[Department] table:

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'.

I don’t know how to fix it? Can you give me some advice?

 

 

Share

1 Answer

  1. Best Answer
    Expert

    The cause: When a FOREIGN KEY constraint is created on a table but the column being referenced as a FOREIGN KEY is not a PRIMARY KEY on the other table, this error occurs.

    Solution: You must first create the PRIMARY KEY constraint in the table that will be used as a reference in the FOREIGN KEY constraint in order to prevent this problem.

    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

    You can construct a UNIQUE index or UNIQUE constraint on the column if the table to be referred by the FOREIGN KEY constraint already has a PRIMARY KEY and it’s not the column to be referenced by the FOREIGN KEY.

    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
    • 3

Leave an answer

You must login to add an answer.

Need An Account,