MySQL外键约束

本文详细介绍了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;

    mysql> SOURCE dump_file_name;

    mysql> SET FOREIGN_KEY_CHECKS=1;

    如果转储文件包含对外键是不正确顺序的表,这就以任何顺序导入该表。这样也加快导入操作。设置FOREIGN_KEY_CHECKS为0,对于在LOAD DATA和ALTER TABLE操作中忽略外键限制也是非常有用的。

    InnoDB不允许你删除一个被FOREIGNKEY表约束引用的表,除非你做设置SETFOREIGN_KEY_CHECKS=0。当你移除一个表的时候,在它的创建语句里定义的约束也被移除。

    如果你重新创建一个被移除的表,它必须有一个遵从于也引用它的外键约束的定义。它必须有正确的列名和类型,并且如前所述,它必须对被引用的键有索引。如果这些不被满足,MySQL返回错误号1005并在错误信息字符串中指向errno 150。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值