restrict

MySQL外键设置之Cascade、NOACTION、Restrict、

SETNULL

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

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

ALTERTABLEtbl_name

ADD[CONSTRAINT[symbol]DROP]

FOREIGNKEY[index_name](index_col_name,...)

REFERENCEStbl_name(index_col_name,...)

[ONDELETEreference_option]

[ONUPDATEreference_option]

//添加外键

ALTERTABLE`t_ur`CONSTRAINT`FK_USER_DEPT`FOREIGNKEY(`dept_id`)REFERENCES`t_dept`(`id`)

//删除外键

ALTERTABLE`t_ur`DROPFOREIGNKEY`FK_USER_DEPT`;

e：在⽗表上update/delete记录时，同步update/delete掉⼦表的匹配记录

on：如果⼦表中有匹配的记录,则不允许对⽗表对应候选键进⾏update/delete操作

ct：同noaction,都是⽴即检查外键约束

l：在⽗表上update/delete记录时，将⼦表上匹配记录的列设为null要注意⼦表的外键列不能为notnull

t：⽗表有变更时,⼦表将外键列设置成⼀个默认的值但Innodb不能识别

空、RESTRICT、NOACTION

删除：从表记录不存在时，主表才可以删除，删除从表，主表不变。

更新：从表记录不存在时，主表菜可以更新，更新从表，主表不变。

CASCADE

删除：删除主表时⾃动删除从表。删除从表，主表不变。

更新：更新主表时⾃动更新从表。更新从表，主表不变。

SETNULL

删除：删除主表时⾃动更新从表为NULL，删除从表，主表不变。

更新：更新主表时⾃动更新从表值为NULL。更新从表，主表不变。

RESTRICT|CASCADE|SETNULL|NOACTION外键的使⽤需要满⾜下列的条件：

1.两张表必须都是InnoDB表，并且它们没有临时表。

2.建⽴外键关系的对应列必须具有相似的InnoDB内部数据类型。

3.建⽴外键关系的对应列必须建⽴了索引。

4.假如显式的给出了CONSTRAINTsymbol，那symbol在数据库中必须是唯⼀的。

假如没有显式的给出，InnoDB会⾃动的创建。如果⼦表试图创建⼀个在⽗表中不存在的外键值，InnoDB会拒绝任何INSERT或UPDATE

操作。如果⽗表试图UPDATE或者DELETE任何⼦表中存在或匹配的外键值，最终动作取决于外键约束定义中的ONUPDATE和ON

DELETE选项。InnoDB⽀持5种不同的动作，如果没有指定ONDELETE或者ONUPDATE，默认的动作为RESTRICT:

E:从⽗表中删除或更新对应的⾏，同时⾃动的删除或更新⾃表中匹配的⾏。ONDELETECANSCADE和ONUPDATE

CANSCADE都被InnoDB所⽀持。

L:从⽗表中删除或更新对应的⾏，同时将⼦表中的外键列设为空。注意，这些在外键列没有被设为NOTNULL时才有效。ON

DELETESETNULL和ONUPDATESETSETNULL都被InnoDB所⽀持。

ON:InnoDB拒绝删除或者更新⽗表。

CT:拒绝删除或者更新⽗表。指定RESTRICT（或者NOACTION）和忽略ONDELETE或者ONUPDATE选项的效果是⼀样

的。

AULT:InnoDB⽬前不⽀持。外键约束使⽤最多的两种情况⽆外乎：

1）⽗表更新时⼦表也更新，⽗表删除时如果⼦表有匹配的项，删除失败；

2）⽗表更新时⼦表也更新，⽗表删除时⼦表匹配的项也删除。

前⼀种情况，在外键定义中，我们使⽤ONUPDATECASCADEONDELETERESTRICT；后⼀种情况，可以使⽤ONUPDATE

CASCADEONDELETECASCADE。当执⾏外键检查之时，InnoDB对它照看着的⼦或⽗记录设置共享的⾏级锁。InnoDB⽴即检查外键

约束，检查不对事务提交延迟。要使得对有外键关系的表重新载⼊转储⽂件变得更容易，mysqldump⾃动在转储输出中包括⼀个语句设置

FOREIGN_KEY_CHECKS为0。这避免在转储被重新装载之时，与不得不被以特别顺序重新装载的表相关的问题。也可以⼿动设置这个变

量：

mysql>SETFOREIGN_KEY_CHECKS=0;

mysql>SOURCEdump_file_name;

mysql>SETFOREIGN_KEY_CHECKS=1;

如果转储⽂件包含对外键是不正确顺序的表，这就以任何顺序导⼊该表。这样也加快导⼊操作。

设置FOREIGN_KEY_CHECKS为0，对于在LOADDATA和ALTERTABLE操作中忽略外键限制也是⾮常有⽤的。

InnoDB不允许你删除⼀个被FOREIGNKEY表约束引⽤的表，除⾮你做设置SETFOREIGN_KEY_CHECKS=0。当你移除⼀个表的时

候，在它的创建语句⾥定义的约束也被移除。 如果你重新创建⼀个被移除的表，它必须有⼀个遵从于也引⽤它的外键约束的定义。它必

须有正确的列名和类型，并且如前所述，它必须对被引⽤的键有索引。如果这些不被满⾜，MySQL返回错误号1005并在错误信息字符串

中指向errno150。