SQL:Add foreign key in exist table

最新推荐文章于 2019-12-12 21:49:46 发布

weixin_34417183

最新推荐文章于 2019-12-12 21:49:46 发布

阅读量115

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/dmgactive/archive/2011/06/07/2074392.html

本文介绍了解决SQL Server中创建外键约束时遇到的错误Msg1776的方法。主要原因是被引用的列不是主键。文章提供了通过创建主键约束或唯一约束来解决此问题的步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

转载于:https://www.cnblogs.com/dmgactive/archive/2011/06/07/2074392.html