mysql中外键更新时set null用法

一、看案例

上表中，音乐审核表中 引用了 管理员 id 和 音乐id 字段，不难理解。事务的要求是 管理员对音乐的审核。

如何我们使用了RESTRICT限制性约束的话，admin和music的id不能为null，如何我们使用 删除时、或更新时setnull，则需要开启为null的约束。

二、外键约束讲解

在MySQL数据库中，外键约束可以设置4个不同的操作选项来处理主键表中发生更改或删除的行所引用的外键表中的行。这些选项包括CASCADE、NO ACTION、RESTRICT和SET NULL。

下面是这些操作选项的详细说明：

CASCADE

当在主键表中更新或删除行时，CASCADE选项会自动更新或删除外键表中相关的行。例如，如果在主键表中删除了一行，那么与该行相关的所有外键表中的行也将被删除。

NO ACTION

使用NO ACTION选项时，如果尝试在主键表中更新或删除行时，将不会发生任何操作，直到删除或更新相关的外键表中的行。如果没有这样的行，则允许在主键表中更新或删除行。

RESTRICT

如果使用RESTRICT选项，当试图在主键表中删除或更新行时，如果在外键表中还有相关的行，则会阻止此操作。这种情况下，必须首先从外键表中删除相关的行，然后才能在主键表中执行删除或更新操作。

SET NULL

如果使用SET NULL选项，当在主键表中更新或删除行时，与该行相关的外键表中的所有行将设置为NULL值。例如，如果在主键表中删除了一行，则与该行相关的所有外键表中的行将设置为NULL值。

总之，外键约束操作选项是一种非常有用的功能，可以帮助保护数据库的完整性，确保数据表之间的关系得到正确维护。

三、代码样例

假设我们有两个表：authors（作者）和 books（书籍）。books 表中有一个外键 author_id，它指向 authors 表中的主键 id。现在，我们想要设置当删除或更新 authors 表中的记录时，将 books 表中对应的 author_id 设置为 NULL。

创建两张表

CREATE TABLE authors (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE books (
    id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors (id) ON UPDATE SET NULL ON DELETE SET NULL
);

数据插入

INSERT INTO authors (id, name) VALUES (1, 'John Smith');
INSERT INTO books (id, title, author_id) VALUES (101, 'Book 1', 1);
INSERT INTO books (id, title, author_id) VALUES (102, 'Book 2', 1);

主表主键更新时

UPDATE authors SET id = 100 WHERE id = 1;

这将导致 books 表中 author_id 为 1 的记录被更新为 NULL。

四、总结

在一些主表需要用从表做约束时，我们主要考虑是否设置为”限制RESTRICT“、”为空SET NULL“，这样就能避免在从表数据字段被更新或删除时导致 主表操作失效。

在单表备份操作时，如果开发人员对业务存疑，先将约束都改为SET NULL（个人观点，接受指正）