键用Foreign key 表示。故名思议:外面的键(不在自己表中),如果一张表中有一个字段(非主键)指向另外一张表中的主键,那么将该字段称之为外键。
1、增加外键。
增加外间的时候,有两种方式。
a)、创建表的时候增加外键,在所有的表之后使用foreign key(外键字段) references 父表(主键字段);
语法:foreign key(外键字段) references 父表(主键字段);
例子:创建一个名叫【my_foreign1】的表,创建一个名叫【class2】的表,将my_foreign1表中的c_id字段关联到class2表中的主键id。
为my_foreign1表中的c_id字段添加外键。
create table if not exists my_foreign1(
id int primary key auto_increment,
name varchar(10),
c_id int,
foreign key(c_id) references class2(id)
)charset utf8;
b)、在新增加表之后,增加外键。
语法:alter table 表名字 add [constract 外键名字] foreign key(外键字段) references 父表(主键字段);
例子:创建一个名叫【my_foreign2】的表,创建好之后再为其添加外键。
create table if not exists my_foreign2(
id int primary key auto_increment,
name varchar(10),
c_id int
)charset utf8;
修改:alter table my_foreign2 add constraint my_foreign2_class2 foreign key(c_id) references class2(id);
2、外键删除。
外键不可以修改,只能先删除后新增。
删除语法:alter table 表名字 drop foreign key 外键名字; 一张表中可以有多个外键,但是外键名字不相同。
例子:删除【my_foreign2】表中的外键。
alter table my_foreign2 drop foreign key my_foreign2_class2;
通过上图查看表结构,亦然看到的是MUL,其实这是一种假象,通过查看表创建语句可以看出来,通过下图看出已删除外键,不要被假象所迷惑。
3、外键的作用。
外键的作用有两点:一个对父表,一个对子表(外键字段所在的表)。
a)、对子表的约束,主要是针对添加数据和修改数据。子表数据进行数据操作的时候,如果对应的字段,在父表中找不到对应的匹配,那么操作会失败。
例子:将my_foreign2表的c_id字段 关联class2表中的id字段,然后进行添加数据操作。
关联:alter table my_foreign2 add constraint my_foreign2_class2 foreign key(c_id) references class2(id);
为my_foreign2表添加数据。
insert into my_foreign2 values(null,'张三',1); -- 插入成功(c_id的值与class2表中的id匹配成功)
insert into my_foreign2 values(null,'李四',2); -- 插入成功(c_id的值与class2表中的id匹配成功)
insert into my_foreign2 values(null,'王五',3); -- 插入成功(c_id的值与class2表中的id匹配成功)
insert into my_foreign2 values(null,'赵六',4); -- 插入失败(c_id的值与class2表中的id匹配失败,因为class表中的id编号是从1到3)。
b)、对父表的约束。
父表数据进行写操作写操作没影响,如果涉及到删除和修改操作的话,需要注意一下,如果对应的主键在父表中已经被数据所引用,那么就不允许操作。
从上图可以看出,my_foreign2表中的c_id的值已经引用了class2表中的id编号为1、2、3的数据。
更新:class2表中的数据,将id字段为1的值修改成4; update class2 set id = 4 where id = 1; 更新失败,因为数据已经被外键表所引用。
4、外键条件。
a)、外键要存在,首先必须保证表的存储引擎是innodb(默认的存储引擎),如果不是innodb存储引擎,那么外键可以创建成功,没有约束效果。
b)、外键的字段(列类型),必须与父表中的主键类型完全一致。
c)、一张表中的外键名不能重复。
d)、增加外键的字段(数据已经存在),必须保证是数据与父表主键要求对应。
5、外键约束。
外键约束:就是指外键的作用。之前讲的外键作用是默认的作用,还可以通过对外键的更改,进行定制操作。
外键约束有三种模式,都是针对父表的约束。分别是distinct、cascade、set null。
a)、distinct。
distinct:严格模式(默认的),父表不能删除或者更新一个已经被子表数据引用的记录。
b)、cascade。
cascade:级联模式,父表的操作,对应的子表关联的数据也跟着被删除。
c)、set null。
set null:置空模式,父表的操作之后,字表对应的数据(外键字段)被置空。
删除置空的前提条件:允许字段为空(如果不满足条件,则无法创建)。
通常的一个合理的做法(约束模式),删除的时候子表置空,更新的时候子表级联操作。
指定模式的语法:foreign key(外键字段) references 父表(主键字段) on delete set null on update cascade;
例子:删除【class2】表中id字段值为3的数据,外键表(my_foreign2)置空的操作。修改class2】表中id字段值为1的数据,外键表(my_foreign2)也跟着修改(级联操作)。
先修改class2表中的外键约束模式。
alter table my_foreign2 drop foreign key my_foreign2_class2; -- 先删除。
alter table my_foreign2 add constraint my_foreign2_class2 foreign key(c_id) references class2(id) on delete set null on update cascade; --后添加
删除class2表中id为3的记录。
修改class2表中id为1的记录。