. Advertisement .
. Advertisement .
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?
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.
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:
Using a UNIQUE constraint: