网站运营实例,郴州建网站,互联网技术岗位校园招聘,有免费做网站的吗外键约束 前序外键约束 前序
在很多场景里面#xff0c;都会用到外键来关联两张表或两张以上的表之间主从关系#xff0c;从而来快捷地通过外键字段来查询数据之间的联系。
其实外键在我的概念中还是比较模糊的#xff0c;没有真正地使用过。大多数情况下#xff0c;我都… 外键约束 前序外键约束 前序
在很多场景里面都会用到外键来关联两张表或两张以上的表之间主从关系从而来快捷地通过外键字段来查询数据之间的联系。
其实外键在我的概念中还是比较模糊的没有真正地使用过。大多数情况下我都是使用单表来处理。
场景在数字孪生的项目中动环设备、网管设备、安防设备它们的设备信息都来源于设备资产设备资产会记录所有的设备信息 在这三个模块里面除了常规的设备信息外每个模块里面的设备资产也对应了不同的信息例如属于动环设备的设备资产记录了每个设备的信号网管设备的设备资产记录了每个设备的线路安防设备的设备资产记录了每个设备的监控因此依靠这设备信息衍生出了很多的其他信息且不同功能模块下表的结构也不一样当一开始将表都设计成单表时问题就出来了
问题 因为表都设计成了单表彼此之间没有任何连接导致删除了设备信息表里面一条设备信息而其他表对应的这条设备的其他信息没有被删除也没有阻止删除设备信息表的这条设备信息。
解决为了解决这个问题就需要引入外键约束的概念这包括了一对多或一对一的概念一对多就意味着主键对应着一张表的多个外键一对一就意味着主键只能对应着一张表的一个外键。
外键约束
为了说明外键约束的特性先建两张表asset_record、signal_lab。
create table asset_record
(id nvarchar(36) not nullconstraint PK__asset_re__3213E83F986BB1C9primary 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上面创建了一张 asset_record 表它用来记录设备信息其中主键为 id 。
create table signal_bind
(id nvarchar(36) not nullconstraint PK__signal_b__3213E83F122745EBprimary 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上面创建了一张 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温湿度, N1689559538522439684, N2, N1689877026292453378, N4028a98189e33ed80189e340b9d60006, NULL, Nadmin, 2023-09-13 13:48:52.073, NULL, NULL, N, NULL, N1, 2023-09-13 13:48:51.383, 2023-09-13 13:48:51.383, NULL, N4028a98189f17d860189f17d860c0000, 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, NUPS, N1689559538522439683, N2, N1689877026032406530, N4028a98189e346420189e34642120000, NULL, Nadmin, 2023-09-13 13:48:51.507, NULL, NULL, N, NULL, N1, 2023-09-13 13:48:51.383, 2023-09-13 13:48:51.383, NULL, N4028a98189f17d860189f17d860c0000, NULL);
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, N170100001000004, N1689559538522439684, N, N017100001, N0, N温度1通信状态, N2, N1, N0正常;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, N080300000000006, N1689559538522439683, N, N07610000E, N0, NUPS设备通信状态告警, N2, N1, N0正常;1告警);signal_bind 插入两个设备的一条信号温湿度【温度1通信状态】、UPS【UPS设备通信状态告警】。
这个时候删除 asset_record 里面的一条温湿度设备信息。
DELETE FROM asset_record WHERE id 170100001000004;按理来说既然设备都不存在了那么设备的信号存储在表里面是没有意义的因此还需要删除对应的信号数据。
DELETE FROM signal_bind WHERE id 170100001000004;这样表面是没有问题的但是如果忘记删除设备信号那么设备信息还是可以被删除这就导致设备信号表里面存在脏数据脏数据多了就会影响查询效率。
因此需要用外键来约束。
ALTER TABLE signal_bind
ADD CONSTRAINT FK_asset_record
FOREIGN KEY (asset_record)
REFERENCES asset_record (id);只需要在修改 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;通过联合查询来判断 signal_bind 表里面哪些外键在 asset_record 表里面没有对应的主键。
设置好外键后再次直接删除设备信息表里面UPS的数据就会报错DELETE 语句与 REFERENCE 约束FK_asset_record冲突。要先删除设备信息对应的设备信息数据才能删除设备信息。
