定义外键时使用 ON UPDATE 和 ON DELETE 如何使用

在定义外键约束时，ON UPDATE 和 ON DELETE 子句用于指定当被引用表中的记录被更新或删除时，引用表中的相应记录应该如何处理。这些子句有助于维护数据的一致性和完整性。

ON UPDATE 和 ON DELETE 子句

• ON UPDATE：指定当被引用表中的主键或唯一键值被更新时，引用表中的外键应该如何处理。
• ON DELETE：指定当被引用表中的主键或唯一键值被删除时，引用表中的外键应该如何处理。

操作选项

• CASCADE：级联操作，即引用表中的相应记录也进行同样的操作。
• SET NULL：将引用表中的外键列设置为 NULL。
• SET DEFAULT：将引用表中的外键列设置为默认值。
• NO ACTION：不执行任何操作，但会检查引用完整性，若不满足则报错（通常与 RESTRICT 效果相同）。
• RESTRICT：拒绝操作，防止删除或更新，保证引用完整性。

示例和代码分析

假设有两个表：departments 和 employees。我们希望确保 employees 表中的 department_id 列只能包含 departments 表中存在的 id 值，并且定义一些级联操作。

创建 departments 表

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

• id 列是 departments 表的主键。

创建 employees 表并添加外键约束，使用 ON UPDATE 和 ON DELETE 子句

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INT,
    CONSTRAINT fk_emp_dept FOREIGN KEY (department_id)
    REFERENCES departments (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
);

• id 列是 employees 表的主键。
• department_id 列是一个外键。
• CONSTRAINT fk_emp_dept 给外键约束命名为 fk_emp_dept。
  FOREIGN KEY (department_id) REFERENCES departments(id) 表示 department_id 列引用 departments 表的 id 列。
• ON UPDATE CASCADE：当 departments 表中的 id 被更新时，employees 表中的 department_id 也相应更新。
• ON DELETE SET NULL：当 departments 表中的 id 被删除时，employees 表中的 department_id 被设置为 NULL。

使用示例

插入数据

INSERT INTO departments (id, name)
VALUES (1, 'Human Resources'),
       (2, 'IT'),
       (3, 'Finance');

INSERT INTO employees (id, name, department_id)
VALUES (1, 'Alice', 1),
       (2, 'Bob', 2),
       (3, 'Charlie', 3);

插入部门数据和员工数据，确保每个员工的 department_id 都在 departments 表中存在。

更新部门 ID

-- 更新部门 ID，级联到 employees 表中的 department_id
UPDATE departments
SET id = 4
WHERE id = 1;

-- 查看更新后的数据
SELECT * FROM employees;

departments 表中的 id 更新为 4，employees 表中的相应 department_id 也更新为 4。

删除部门

-- 删除部门记录，employees 表中的 department_id 被设置为 NULL
DELETE FROM departments
WHERE id = 2;

-- 查看删除后的数据
SELECT * FROM employees;

删除 departments 表中的 id 为 2 的记录，employees 表中的相应 department_id 被设置为 NULL。

总结

• ON UPDATE 和 ON DELETE 子句用于指定外键约束在引用的记录被更新或删除时的操作。
• 常见操作包括 CASCADE、SET NULL、SET DEFAULT、NO ACTION 和 RESTRICT。
• 通过这些子句，可以维护数据的完整性和一致性，根据业务需求设置合适的级联操作。