详解MySQL 外键约束

官方文档：
https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html

1.外键作用：

MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。

2.外键的使用条件

两个表必须是InnoDB表，MyISAM表暂时不支持外键（据说以后的版本有可能支持，但至少目前不支持）
外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显示建立；
外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以。

3.创建语法

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name, ...)
REFERENCES tbl_name (col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。
ON DELETE、ON UPDATE表示事件触发限制，可设参数：
RESTRICT（限制外表中的外键改动）
CASCADE（跟随外键改动）
SET NULL（设空值）
SET DEFAULT（设默认值）
NO ACTION（无动作，默认的）
CASCADE：表示父表在进行更新和删除时，更新和删除子表相对应的记录
RESTRICT和NO ACTION：限制在子表有关联记录的情况下，父表不能单独进行删除和更新操作
SET NULL：表示父表进行更新和删除的时候，子表的对应字段被设为NULL

4.案例演示

以CASCADE（级联）约束方式

1. 创建势力表（父表）countrycreate table country (id int not null,name varchar(30),primary key(id));2. 插入记录insert into country values(1,'西欧')；insert into country values(2,'玛雅')；insert into country values(3,'西西里');3. 创建兵种表（子表）并建立约束关系create table solider(id int not null,name varchar(30),country_id int,primary key(id),foreign key(country_id) references country(id) on delete cascade on update cascade,);4. 参照完整性测试insert into solider values(1,'西欧见习步兵',1);#插入成功insert into solider values(2,'玛雅短矛兵'，2);#插入成功insert into solider values(3,'西西里诺曼骑士',3)#插入成功insert into solider values(4，'法兰西剑士',4);#插入失败，因为country表中不存在id为4的势力5. 约束方式测试insert into solider values(4，'玛雅猛虎勇士'，2);#成功插入delete from country where id=2；#会导致solider表中id为2和4的记录同时被删除，因为父表中都不存在这个势力了，那么相对应的兵种自然也就消失了update country set id=8 where id=1;#导致solider表中country_id为1的所有记录同时也会被修改为8

1. 创建兵种表（子表）并建立约束关系drop table if exists solider;create table solider(id int not null,name varchar(30),country_id int,primary key(id),foreign key(country_id) references country(id) on delete set null on update set null,);2. 参照完整性测试insert into solider values(1,'西欧见习步兵',1);#插入成功insert into solider values(2,'玛雅短矛兵'，2);#插入成功insert into solider values(3,'西西里诺曼骑士',3)#插入成功insert into solider values(4，'法兰西剑士',4);#插入失败，因为country表中不存在id为4的势力3. 约束方式测试insert into solider values(4，'西西里弓箭手'，3);#成功插入delete from country where id=3；#会导致solider表中id为3和4的记录被设为NULLupdate country set id=8 where id=1;#导致solider表中country_id为1的所有记录被设为NULL

1. 创建兵种表（子表）并建立约束关系drop table if exists solider;create table solider(id int not null,name varchar(30),country_id int,primary key(id),foreign key(country_id) references country(id) on delete RESTRICT on update RESTRICT,);2. 参照完整性测试insert into solider values(1,'西欧见习步兵',1);#插入成功insert into solider values(2,'玛雅短矛兵'，2);#插入成功insert into solider values(3,'西西里诺曼骑士',3)#插入成功insert into solider values(4，'法兰西剑士',4);#插入失败，因为country表中不存在id为4的势力3. 约束方式测试insert into solider values(4，'西欧骑士'，1);#成功插入delete from country where id=1；#发生错误，子表中有关联记录，因此父表中不可删除相对应记录，即兵种表还有属于西欧的兵种，因此不可单独删除父表中的西欧势力update country set id=8 where id=1;#错误，子表中有相关记录，因此父表中无法修改

更多相关文章

随机推荐