删除Lookup表关联行时将父表字段设为Null的实现问题

要实现删除Lookup表记录时自动将父表对应关联字段设为Null，核心是在父表的外键约束中配置ON DELETE SET NULL，下面是具体实现步骤和问题排查：

1. 正确配置外键约束（新建表场景） #

外键约束需要定义在父表上，关联Lookup表的主键，同时指定ON DELETE SET NULL。注意：父表的关联字段必须允许为Null（这是SET NULL生效的前提）。

示例SQL：

-- 创建Lookup表
CREATE TABLE lookup_table (
    id INT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL
);

-- 创建父表并配置外键
CREATE TABLE parent_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    lookup_id INT NULL, -- 必须允许为Null
    content TEXT,
    -- 外键约束核心配置
    FOREIGN KEY (lookup_id)
        REFERENCES lookup_table(id)
        ON DELETE SET NULL
);

2. 修改已存在的表（调整现有外键） #

如果表已经创建，需要先删除旧的外键约束，再添加带ON DELETE SET NULL的新约束：

MySQL 示例： #

-- 1. 先查询父表的外键名称（替换为你的表名）
SHOW CREATE TABLE parent_table;

-- 2. 删除旧外键（替换为查询到的外键名）
ALTER TABLE parent_table DROP FOREIGN KEY parent_table_ibfk_1;

-- 3. 添加新的外键约束
ALTER TABLE parent_table
    ADD CONSTRAINT fk_parent_lookup
    FOREIGN KEY (lookup_id)
    REFERENCES lookup_table(id)
    ON DELETE SET NULL;

PostgreSQL 示例： #

-- 1. 查询外键名称
SELECT conname FROM pg_constraint WHERE conrelid = 'parent_table'::regclass AND contype = 'f';

-- 2. 删除旧约束并添加新约束
ALTER TABLE parent_table
    DROP CONSTRAINT old_fk_name,
    ADD CONSTRAINT fk_parent_lookup
    FOREIGN KEY (lookup_id)
    REFERENCES lookup_table(id)
    ON DELETE SET NULL;

3. 常见问题排查 #

如果配置后仍然失效，检查以下几点：

• 父表关联字段是否允许Null：如果字段是NOT NULL，ON DELETE SET NULL无法执行，需先修改字段属性：

  -- MySQL
  ALTER TABLE parent_table MODIFY COLUMN lookup_id INT NULL;
  
  -- PostgreSQL
  ALTER TABLE parent_table ALTER COLUMN lookup_id DROP NOT NULL;
  

• 外键是否关联了Lookup表的主键/唯一键：外键必须引用Lookup表的主键或唯一约束字段，否则约束不生效。
• 是否有自定义触发器干扰：如果之前创建了处理删除逻辑的触发器，可能会覆盖外键的ON DELETE行为，需检查并调整触发器逻辑。

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