外键的使用条件:
两个表必须是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。