Sql 多重外键约束

最新推荐文章于 2025-06-08 09:07:23 发布

原创最新推荐文章于 2025-06-08 09:07:23 发布 · 3.9k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#Sql #多重外键约束 #循环或多重级联路径

SQL 专栏收录该内容

4 篇文章

订阅专栏

本文探讨了SQL中的多重外键约束，即一个表中的两个或更多外键指向同一主键表的情况。以部门管理为例，部门可能有正副两个管理者。在一般情况下，部门表（Department）的ManagerID和SupporterID外键分别对应Manager和Supporter表，且可设置级联删除。然而，在多重外键约束下，SQL Server只允许一个外键设置级联删除，否则会导致循环或多重级联路径错误，需改为NO ACTION或UPDATE NO ACTION。

多重外键约束这个名称可能并不准确，它是多个外键约束情况的一个特例，它指的是外键表有存在两个及以上外键关联到同一个主键表，例如一个部门，有正副两个管理者

首先看看不含多重外键的多个外键约束的一般情况，例如一个部门，有一个管理者和一个IT技术支持

CREATE TABLE [dbo].[Manager] --管理者表
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Manager] ADD CONSTRAINT [PK_dbo.Manager] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Supporter] --IT支持者表
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Supporter] ADD CONSTRAINT [PK_dbo.Supporter] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Department] --部门表
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL,
[ManagerID] [int] NOT NULL,
[SupporterID] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Department] ADD CONSTRAINT [PK_dbo.Department] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ManagerID] ON [dbo].[Department] ([ManagerID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SupporterID] ON [dbo].[Department] ([SupporterID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Department] ADD CONSTRAINT [FK_dbo.Department_dbo.Manager_ManagerID] FOREIGN KEY ([ManagerID]) REFERENCES [dbo].[Manager] ([ID]) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Department] ADD CONSTRAINT [FK_dbo.Department_dbo.Supporter_SupporterID] FOREIGN KEY ([SupporterID]) REFERENCES [dbo].[Supporter] ([ID]) ON DELETE CASCADE
GO

Department表中有两个外键ManagerID和SupporterID，分别关联到Manager表和Supporter表，同时两个外键约束中删除规则是级联，也就是ON DELETE CASCADE

再看多重约束情况，例如一个部门有正副两个管理者

CREATE TABLE [dbo].[Manager] --管理者表
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Manager] ADD CONSTRAINT [PK_dbo.Manager] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Department] --部门表
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL,
[PrimaryManagerID] [int] NOT NULL,
[SecondManagerID] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Department] ADD CONSTRAINT [PK_dbo.Department] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PrimaryManagerID] ON [dbo].[Department] ([PrimaryManagerID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SecondManagerID] ON [dbo].[Department] ([SecondManagerID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Department] ADD CONSTRAINT [FK_dbo.Department_dbo.Manager_PrimaryManagerID] FOREIGN KEY ([PrimaryManagerID]) REFERENCES [dbo].[Manager] ([ID])
GO
ALTER TABLE [dbo].[Department] ADD CONSTRAINT [FK_dbo.Department_dbo.Manager_SecondManagerID] FOREIGN KEY ([SecondManagerID]) REFERENCES [dbo].[Manager] ([ID]) ON DELETE CASCADE
GO

这里需要特别指出的是，在SQL Server中，在多重外键约束情况下只能有一个外键约束中的删除规则可以是级联的，其它外键约束如果设置删除规则为级联，将被提示：

“Department”表
- 无法创建关系“FK_dbo.Department_dbo.Manager_SecondManagerID”。
将 FOREIGN KEY 约束 'FK_dbo.Department_dbo.Manager_SecondManagerID' 引入表 'Department' 可能会导致循环或多重级联路径。请指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION，或修改其他 FOREIGN KEY 约束。
无法创建约束。请参阅前面的错误消息。