十、MySQL 外键

• 一、MySQL 操作集
• 二、MySQL 数据类型
• 三、MySQL 函数
• 四、MySQL Explain
• 五、MySQL 索引优化一
• 六、MySQL 索引优化二
• 七、SQL 优化
• 八、MySQL 锁与事务
• 九、MySQL 索引数据结构
• 十、MySQL 外键

---

十、MySQL 外键

MySQL 有两种常用的引擎类型：

• MyISAM 和 InnoDB；
• 目前只有 InnoDB 引擎类型支持外键约束。

1. InnoDB 添加外键约束

ALTER TABLE `子表名` ADD
    [CONSTRAINT [symbol]]
    FOREIGN KEY [index_name] (index_col_name,...)
    REFERENCES `主表名` (index_col_name,...)
    [ON DELETE `reference_option`]	--指标删除时操作
    [ON UPDATE `reference_option`]	--主表修改时操作
    --reference_option(CASCADE、NO ACTION、RESTRICT、SET NULL)

ALTER TABLE `user_resource`			--子表名称
    CONSTRAINT `FKEEAF1E02D82D57F9`	--子表外键名称
    FOREIGN KEY (`user_Id`)			--子表外键
    REFERENCES `sys_user` (`Id`)	--主表主键	

---

2. InnoDB 删除外键约束

ALTER TABLE `user_resource`
    DROP FOREIGN KEY `FKEEAF1E02D82D57F9`;

---

3. 外键约束属性（reference_option）

选项	描述
CASCADE	在父表上 update、delete 记录时，同步 update、delete 子表的匹配记录
SET NULL	在父表上 update、delete 记录时，将子表上匹配记录的列设为null (要注意子表的外键列不能为 not null)
NO ACTION	如果子表中还存在关联的记录，则不允许对父表对应的键进行 update、delete 操作
RESTRICT	同 NO ACTION，都是立即检查外键约束【默认】
SET DEFAULT	父表有变更时，子表将外键列设置成一个默认的值【InnoDB不支持】

---

选项	更新	删除
CASCADE	更新主表时自动更新从表。更新从表，主表不变	删除主表时自动删除从表。删除从表，主表不变
SET NULL	更新主表时自动更新从表值为NULL。更新从表，主表不变	删除主表时自动更新从表值为NULL。删除从表，主表不变
NO ACTION、RESTRICT	从表记录不存在时，主表才可以更新。更新从表，主表不变	从表记录不存在时，主表才可以删除。删除从表，主表不变

---

1. CASCADE：从父表中删除或更新对应的行，同时自动的删除或更新子表中匹配的行。ON DELETE CANSCADE 和 ON UPDATE CANSCADE 都被 InnoDB 所支持。
2. SET NULL：从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为 NOT NULL 时才有效。ON DELETE SET NULL 和 ON UPDATE SET NULL 都被 InnoDB 所支持。
3. NO ACTION：InnoDB 拒绝删除或者更新父表。
4. RESTRICT：拒绝删除或者更新父表。指定 RESTRICT 或 NO ACTION 和忽略 ON DELETE 或 ON UPDATE 选项的效果是一样的。
   5. SET DEFAULT：InnoDB 目前不支持 。

---

4. 外键使用条件

1. 两张表必须都是 InnoDB 表，并且它们没有临时表；
2. 建立外键关系的对应列，必须具有相似的 InnoDB 内部数据类型；
3. 建立外键关系的对应列，必须建立了索引；
4. 假如显式的给出了 CONSTRAINT symbol，那 symbol 在数据库中必须是唯一的。假如没有显式的给出，InnoDB 会自动的创建。
5. 如果子表试图创建一个在父表中不存在的外键值，InnoDB 会拒绝任何 UPDATE 或 INSERT 操作。
6. 如果父表试图 UPDATE 或 INSERT 任何子表中存在或匹配的外键值，最终动作取决于外键约束定义中的 ON UPDATE 和 ON DELETE 选项。
7. InnoDB 支持 5 种不同的动作，如果没有指定 ON UPDATE 或 ON DELETE，默认的动作为 RESTRICT。

---

5. 外键使用两种情况

1. 父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败：
   ON UPDATE CASCADE、ON DELETE RESTRICT
2. 父表更新时子表也更新，父表删除时子表匹配的项也删除：
   ON UPDATE CASCADE、ON DELETE CASCADE

---

6. 外键使用操作

1. 当执行外键检查之时，InnoDB 对它照看着的子或父记录设置共享的行级锁。InnoDB 立即检查外键约束，检查不对事务提交延迟。
2. 要使得对有外键关系的表重新载入转储文件变得更容易，mysqldump 自动在转储输出中包括一个语句设置 FOREIGN_KEY_CHECKS = 0。这避免在转储被重新装载之时，与不得不被以特别顺序重新装载的表相关的问题。
3. 如果转储文件包含对外键是不正确顺序的表，这就以任何顺序导入该表。这样也加快导入操作。设置 FOREIGN_KEY_CHECKS = 0，对于在 LOAD DATA 和 ALTER TABLE 操作中忽略外键限制也是非常有用的。
4. InnoDB 不允许你删除一个被 FOREIGN KEY 表约束引用的表，除非你做设置 FOREIGN_KEY_CHECKS = 0。当你移除一个表的时候，在它的创建语句里定义的约束也被移除。
5. 如果你重新创建一个被移除的表，它必须有一个遵从于也引用它的外键约束的定义。它必须有正确的列名和类型，并且如前所述，它必须对被引用的键有索引。如果这些不被满足，MySQL 返回错误号 1005 并在错误信息字符串中指向 errno 150。

--手动设置这个变量
mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;

---

二、外键使用案例

1. RESTRICT

CREATE TABLE `borrow`  (
  `borrow_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '借阅ID',
  `staff_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '人员唯一标识',
  PRIMARY KEY (`borrow_id`) USING BTREE,
  INDEX `staff_id`(`staff_id`) USING BTREE,
  --RESTRICT
  CONSTRAINT `staff_id` FOREIGN KEY (`staff_id`) REFERENCES `dam_staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '借阅表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

---

2. CASCADE

CREATE TABLE `staff_education`  (
  `education_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '学历ID',
  `staff_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '人员唯一标识',
  PRIMARY KEY (`education_id`) USING BTREE,
  INDEX `staff_id_education`(`staff_id`) USING BTREE,
  --CASCADE
  CONSTRAINT `staff_id_education` FOREIGN KEY (`staff_id`) REFERENCES `dam_staff` (`staff_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '学历表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

---