SQL:Add foreign key in exist table
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'.
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.
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.
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
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