如何更新存在自引用外键约束、被其他行关联的行的Type字段

解决方案 #

方案1：使用事务+延迟外键约束（推荐，兼容性好的场景优先） #

支持延迟约束的数据库（比如PostgreSQL、Oracle、SQL Server 2016+）可以将外键约束设置为延迟验证，约束检查会推迟到事务提交时执行，操作步骤：

• 开启事务
• 先更新子行B/C/D的bexParentTypeID为目标值（此时因为约束延迟检查，不会触发冲突）
• 再更新父行A的bexTypeID为相同的目标值
• 提交事务，此时外键约束验证通过，无冲突

对应SQL示例（以PostgreSQL为例，先调整外键为可延迟）：

-- 调整外键属性
ALTER TABLE main.Boolean_Expressions 
DROP CONSTRAINT <原外键约束名>,
ADD CONSTRAINT <新外键约束名> 
FOREIGN KEY(bexParentID, bexParentTypeID) 
REFERENCES Boolean_Expressions(bexID, bexTypeID)
DEFERRABLE INITIALLY DEFERRED;

-- 执行更新的事务
BEGIN TRANSACTION;
-- 先更新所有关联子行的ParentType
UPDATE main.Boolean_Expressions 
SET bexParentTypeID = <目标Type值> 
WHERE bexParentID = <A的bexID> AND bexParentTypeID = <A原来的Type值>;
-- 再更新父行A的Type
UPDATE main.Boolean_Expressions 
SET bexTypeID = <目标Type值> 
WHERE bexID = <A的bexID>;
COMMIT;

方案2：临时禁用外键约束（仅低版本数据库无延迟约束时使用） #

如果你的数据库不支持延迟约束，可以在事务中临时关闭外键检查，更新完成后再开启，注意该操作仅在当前事务生效，避免全局关闭导致其他数据异常：

-- SQLite示例
BEGIN TRANSACTION;
PRAGMA foreign_keys = OFF;
-- 执行更新操作
UPDATE main.Boolean_Expressions SET bexTypeID = <目标Type值> WHERE bexID = <A的bexID>;
UPDATE main.Boolean_Expressions SET bexParentTypeID = <目标Type值> WHERE bexParentID = <A的bexID>;
PRAGMA foreign_keys = ON;
COMMIT;

-- MySQL示例
BEGIN TRANSACTION;
SET FOREIGN_KEY_CHECKS = 0;
-- 执行更新操作
UPDATE main.Boolean_Expressions SET bexTypeID = <目标Type值> WHERE bexID = <A的bexID>;
UPDATE main.Boolean_Expressions SET bexParentTypeID = <目标Type值> WHERE bexParentID = <A的bexID>;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

注意：该方案需要确保更新操作的正确性，避免关闭约束期间产生无效的脏数据。

方案3：临时中转行（仅约束无法关闭/延迟时使用） #

也就是你提到的方案，适合无法调整约束验证时机的场景，相比上面两种方案操作更繁琐，需要额外创建和删除中转行：

• 新建临时中转行X，bexID设为临时值，bexTypeID设为任意合法值
• 将所有关联A的子行B/C/D的bexParentID和bexParentTypeID修改为指向X
• 更新A的bexTypeID为目标值
• 将子行B/C/D的bexParentTypeID修改为目标值，bexParentID改回A的ID
• 删除临时行X

方案对比 #

• 优先选择方案1，不会破坏外键约束的完整性检查，全程约束都处于生效状态，不会产生脏数据
• 方案2适合不支持延迟约束的数据库，操作简单但需要严格控制事务范围，避免约束关闭期间其他异常写入
• 方案3是兜底方案，不需要修改表结构或数据库配置，但操作步骤多，性能开销更大

内容的提问来源于stack exchange，提问作者Cliff Pennalligen