MySQL外键约束

最新推荐文章于 2024-06-04 00:00:08 发布

代元培

最新推荐文章于 2024-06-04 00:00:08 发布

阅读量541

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： mysql 外键外键约束

本文链接：https://blog.youkuaiyun.com/sosemseo/article/details/117957435

MySQL 专栏收录该内容

20 篇文章

订阅专栏

本文详细介绍了InnoDB表的外键使用条件，包括表类型、索引要求、数据类型匹配，以及如何在建表和运行时动态添加和删除外键。此外，讲解了不同外键约束选项（CASCADE, SETNULL, NOACTION, RESTRICT）及其应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

外键的使用条件：

两个表必须是InnoDB表，MyISAM表暂时不支持外键

外键列必须建立了索引，MySQL4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；

外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

外键可以在创建表的时候添加，也可以在创建表之后修改。

-- 建表时指定外键
-- 部门表
CREATE TABLE dep (
    id int(11) NOT NULL AUTO_INCREMENT COMMENT '部门id',
    depName varchar(50) NOT NULL COMMENT '部门名称',
    description varchar(50) NOT NULL DEFAULT '' COMMENT '部门描述',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表';

-- 职工表
CREATE TABLE emp (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '职工id',
  name varchar(50) NOT NULL COMMENT '职工名称',
  sex set('男','女','保密') NOT NULL DEFAULT '保密' comment '性别',
  depId int(11) NOT NULL COMMENT '部门id',
  PRIMARY KEY (id),
  KEY fk_depid (depId),
  CONSTRAINT fk_depid FOREIGN KEY (depId) REFERENCES dep (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='职工表';

动态创建外键

通过ALTER命令可以动态添加和删除外键，命令如下，需要注意的是，在动态添加外键前，从表中不能有脏值，否则不能添加成功。

-- 建表后增加外键
ALTER TABLE `emp` CONSTRAINT `fk_depid` FOREIGN KEY (`depId`) REFERENCES `dep` (`id`);
-- 删除外键
ALTER TABLE `emp` DROP FOREIGN KEY `fk_depid`;

外键约束的参考操作

CASCADE

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

SET NULL

主表删除或者更新，并设置从表的外键值为NULL，但是需要保证外键可以允许为NULL（即从表外键列不能为NOT NULL），否则添加这个约束会失败。

NO ACTION

如果子表中有匹配的记录，则不允许对主表进行update/delete操作

RESTRICT

同no action，都是立即检查外键约束。

SET NULL

父表有变更时，子表将外键列设置成一个默认的值但InnoDB不能识别

NULL、RESTRICT、NO ACTION

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

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

CASCADE

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

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

SET NULL

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

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

外键约束属性：RESTRICT | CASCADE | SET NULL | NO ACTION

如果子表试图创建一个在父表中不存在的外键值，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 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;