MySQL外键设置之Cascade、NO ACTION、Restrict、SET NULL详解

目录

1.外键约束语法

1.1.添加外键

1.2.删除外键

2.外键约束--删除时、更新时的操作

---

MySQL有两种常用的引擎类型：MyISAM和InnoDB。

1.外键约束语法

目前只有InnoDB引擎类型支持外键约束。InnoDB中外键约束定义的语法如下：

ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol] DROP] 
    FOREIGN KEY [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

1.1.添加外键

ALTER TABLE `t_user` CONSTRAINT `FK_USER_DEPT` FOREIGN KEY (`dept_id`) REFERENCES `t_dept` (`id`)

1.2.删除外键

ALTER TABLE `t_user` DROP FOREIGN KEY `FK_USER_DEPT`;

2.外键约束--删除时、更新时的操作

1. cascade：在父表上 update / delete记录时，同步 update / delete掉子表的匹配记录

2. No action：如果子表中有匹配的记录, 则不允许对父表对应候选键进行update / delete操作

3. Restrict：同 no action, 都是立即检查外键约束

4. set null：在父表上 update / delete记录时，将子表上匹配记录的列设为 null要注意子表的外键列不能为 not null

5. default：父表有变更时, 子表将外键列设置成一个默认的值但Innodb不能识别

        空、RESTRICT、NO ACTION    删除：从表记录不存在时，主表才可以删除，删除从表，主表不变。
更新：从表记录不存在时，主表菜可以更新，更新从表，主表不变。
        CASCADE    删除：删除主表时自动删除从表。删除从表，主表不变。
更新：更新主表时自动更新从表。更新从表，主表不变。
        SET NULL    删除：删除主表时自动更新从表为NULL，删除从表，主表不变。
更新：更新主表时自动更新从表值为NULL。更新从表，主表不变。
        RESTRICT | CASCADE | SET NULL | NO ACTION外键的使用需要满足下列的条件：

• 1.两张表必须都是InnoDB表，并且它们没有临时表。
• 2.建立外键关系的对应列必须具有相似的InnoDB内部数据类型。
• 3.建立外键关系的对应列必须建立了索引。
• 4.假如显式的给出了CONSTRAINT symbol，那 symbol在数据库中必须是唯一的。

        假如没有显式的给出， InnoDB会自动的创建。如果子表试图创建一个在父表中不存在的外键值， InnoDB会拒绝任何 INSERT或 UPDATE操作。如果父表试图 UPDATE或者 DELETE任何子表中存在或匹配的外键值，最终动作取决于外键约束定义中的 ON UPDATE和 ON DELETE选项。 InnoDB支持 5种不同的动作，如果没有指定 ON DELETE或者 ON UPDATE，默认的动作为 RESTRICT:
        1.CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和 ON UPDATE CANSCADE都被 InnoDB所支持。
        2.SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。 ON DELETE SET NULL和 ON UPDATE SET SET NULL都被 InnoDB所支持。
        3.NO ACTION: InnoDB拒绝删除或者更新父表。
        4.RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者 NO ACTION）和忽略 ON DELETE或者 ON UPDATE选项的效果是一样的。
        5.SET DEFAULT: InnoDB目前不支持。外键约束使用最多的两种情况无外乎：
        1）父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败；
        2）父表更新时子表也更新，父表删除时子表匹配的项也删除。
前一种情况，在外键定义中，我们使用 ON UPDATE CASCADE ON DELETE RESTRICT；后一种情况，可以使用 ON UPDATE CASCADE ON DELETE CASCADE。当执行外键检查之时， InnoDB对它照看着的子或父记录设置共享的行级锁。 InnoDB立即检查外键约束，检查不对事务提交延迟。要使得对有外键关系的表重新载入转储文件变得更容易， mysqldump自动在转储输出中包括一个语句设置 FOREIGN_KEY_CHECKS为 0。这避免在转储被重新装载之时，与不得不被以特别顺序重新装载的表相关的问题。也可以手动设置这个变量：

        mysql > SET FOREIGN_KEY_CHECKS = 0;
        mysql > SOURCE dump_file_name;
        mysql > SET FOREIGN_KEY_CHECKS = 1;　

        如果转储文件包含对外键是不正确顺序的表，这就以任何顺序导入该表。这样也加快导入操作。
        设置FOREIGN_KEY_CHECKS为 0，对于在 LOAD DATA和 ALTER TABLE操作中忽略外键限制也是非常有用的。
        InnoDB不允许你删除一个被 FOREIGN KEY表约束引用的表，除非你做设置 SET FOREIGN_KEY_CHECKS = 0。当你移除一个表的时候，在它的创建语句里定义的约束也被移除。如果你重新创建一个被移除的表，它必须有一个遵从于也引用它的外键约束的定义。它必须有正确的列名和类型，并且如前所述，它必须对被引用的键有索引。如果这些不被满足， MySQL返回错误号 1005并在错误信息字符串中指向 errno 150。