【SQL Server】外键约束

前序

在很多场景里面，都会用到外键来关联两张表或两张以上的表之间主从关系，从而来快捷地通过外键字段来查询数据之间的联系。

其实外键在我的概念中还是比较模糊的，没有真正地使用过。大多数情况下，我都是使用单表来处理。

场景：在数字孪生的项目中，动环设备、网管设备、安防设备它们的设备信息都来源于设备资产，设备资产会记录所有的设备信息
，在这三个模块里面，除了常规的设备信息外，每个模块里面的设备资产也对应了不同的信息，例如属于动环设备的设备资产记录了每个设备的信号，网管设备的设备资产记录了每个设备的线路，安防设备的设备资产记录了每个设备的监控，因此依靠这设备信息衍生出了很多的其他信息，且不同功能模块下，表的结构也不一样，当一开始将表都设计成单表时，问题就出来了！

问题： 因为表都设计成了单表，彼此之间没有任何连接，导致删除了设备信息表里面一条设备信息，而其他表对应的这条设备的其他信息没有被删除，也没有阻止删除设备信息表的这条设备信息。

解决：为了解决这个问题，就需要引入外键约束的概念，这包括了一对多或一对一的概念，一对多就意味着主键对应着一张表的多个外键，一对一就意味着主键只能对应着一张表的一个外键。

外键约束

为了说明外键约束的特性，先建两张表：asset_record、signal_lab。

create table asset_record
(
    id nvarchar(36) not null
        constraint PK__asset_re__3213E83F986BB1C9
            primary key,
    sn nvarchar(32),
    name nvarchar(32),
    asset_type nvarchar(36),
    asset_status nvarchar(32),
    asset_brand nvarchar(36),
    brand_model nvarchar(36),
    responsible nvarchar(32),
    create_by nvarchar(50),
    create_time datetime,
    update_by nvarchar(50),
    update_time datetime,
    remark nvarchar(32),
    price float,
    asset_tag nvarchar(32),
    putaway_time datetime,
    buy_time datetime,
    takeoff_time datetime,
    asset_room nvarchar(36),
    area_id nvarchar(32)
)
go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

上面创建了一张 asset_record 表，它用来记录设备信息，其中主键为 id 。

create table signal_bind
(
	id nvarchar(36) not null
		constraint PK__signal_b__3213E83F122745EB
			primary key,
	asset_record nvarchar(36),
	asset_type nvarchar(36),
	signal_code nvarchar(36),
	signal_id nvarchar(36),
	signal_type nvarchar(3),
	name nvarchar(50),
	alarm_level nvarchar(3),
	threshold nvarchar(10),
	value_desc nvarchar(100)
)
go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

上面创建了一张 signal_bind 表，它用来记录设备的信号，其中主键为 id，与 asset_record 的关系为一对多。

表中并没有设置外键，就是两张及其普通的单表结构。

现在为其两张表分别插入两条数据。

INSERT INTO [dbo].[asset_record] ([id], [sn], [name], [asset_type], [asset_status], [asset_brand], [brand_model], [responsible], [create_by], [create_time], [update_by], [update_time], [remark], [price], [asset_tag], [putaway_time], [buy_time], [takeoff_time], [asset_room], [area_id]) VALUES ('170100001000004', NULL, N'温湿度', N'1689559538522439684', N'2', N'1689877026292453378', N'4028a98189e33ed80189e340b9d60006', NULL, N'admin', '2023-09-13 13:48:52.073', NULL, NULL, N'', NULL, N'1', '2023-09-13 13:48:51.383', '2023-09-13 13:48:51.383', NULL, N'4028a98189f17d860189f17d860c0000', NULL);
INSERT INTO [dbo].[asset_record] ([id], [sn], [name], [asset_type], [asset_status], [asset_brand], [brand_model], [responsible], [create_by], [create_time], [update_by], [update_time], [remark], [price], [asset_tag], [putaway_time], [buy_time], [takeoff_time], [asset_room], [area_id]) VALUES ('080300000000006', NULL, N'UPS', N'1689559538522439683', N'2', N'1689877026032406530', N'4028a98189e346420189e34642120000', NULL, N'admin', '2023-09-13 13:48:51.507', NULL, NULL, N'', NULL, N'1', '2023-09-13 13:48:51.383', '2023-09-13 13:48:51.383', NULL, N'4028a98189f17d860189f17d860c0000', NULL);

1
2
3

asset_record 插入两个设备信息：温湿度、UPS。

INSERT INTO [dbo].[signal_bind] ([id], [asset_record], [asset_type], [signal_code], [signal_id], [signal_type], [name], [alarm_level], [threshold], [value_desc]) VALUES ('1693536753555603458', N'170100001000004', N'1689559538522439684', N'', N'017100001', N'0', N'温度1通信状态', N'2', N'1', N'0&正常;1&告警');
INSERT INTO [dbo].[signal_bind] ([id], [asset_record], [asset_type], [signal_code], [signal_id], [signal_type], [name], [alarm_level], [threshold], [value_desc]) VALUES ('1693536757124956161', N'080300000000006', N'1689559538522439683', N'', N'07610000E', N'0', N'UPS设备通信状态告警', N'2', N'1', N'0&正常;1&告警');
1
2

signal_bind 插入两个设备的一条信号：温湿度【温度1通信状态】、UPS【UPS设备通信状态告警】。

这个时候删除 asset_record 里面的一条温湿度设备信息。

DELETE FROM asset_record WHERE id = '170100001000004';
1

按理来说，既然设备都不存在了，那么设备的信号存储在表里面是没有意义的，因此还需要删除对应的信号数据。

DELETE FROM signal_bind WHERE id = '170100001000004';
1

这样表面是没有问题的，但是如果忘记删除设备信号，那么设备信息还是可以被删除，这就导致设备信号表里面存在脏数据，脏数据多了就会影响查询效率。

因此需要用外键来约束。

ALTER TABLE signal_bind
ADD CONSTRAINT FK_asset_record
FOREIGN KEY (asset_record)
REFERENCES asset_record (id);
1
2
3
4

只需要在修改 signal_bind 表的 asset_record 字段为外键，且与 asset_record 的主键关联就可以啦，其中你需要注意如果 signal_bind 表的外键存在，而 asset_record 表里面没有对应的主键数据，那么创建外键就会失败，并且报错 ALTER TABLE 语句与 FOREIGN KEY 约束"FK_asset_record"冲突。 巴拉巴拉之类的。

SELECT sb.asset_record
FROM signal_bind AS sb
LEFT JOIN asset_record AS ar ON sb.asset_record = ar.id
WHERE ar.id IS NULL;
1
2
3
4

通过联合查询来判断 signal_bind 表里面哪些外键在 asset_record 表里面没有对应的主键。

设置好外键后，再次直接删除设备信息表里面UPS的数据就会报错（DELETE 语句与 REFERENCE 约束"FK_asset_record"冲突。），要先删除设备信息对应的设备信息数据，才能删除设备信息。