外键(foreign key)
外键:从表的公共字段
外键约束用来保证引用的完整性,主外键的名字可以不一样,但是数据类型必须一样.
特点:
1.主表中不存在的记录,从表中不能插入
2.从表中存在记录,主表中不能先删除
3.必须先删除从表,再删除主表
(1)创建外键
--学生表(主表)
create table stuinfo(
id int auto_increment comment'主键',
stu_name varchar(255),
primary key(id)
)engine=innodb;
#添加一条数据
insert into stuinfo set `stu_name`='王大恒';
--成绩表(从表)
create table stumarks(
stuno int comment'主键',
ch float,
math float
#foreign key(stuno) references stuinfo(id)
#foreign key (stuno) references stuinfo(id) on delete set null on update cascade
)engine=innodb;
#修改表的时候添加一个外键
修改 表 成绩表 增加 外键 学号 关联 学生信息表的id字段
alter table stumarks add foreign key (stuno) references stuinfo(id);
#当主表删除 从表设置为空(指的是外键字段)
#on delete set null
#当主表删除 从表中整条数据跟着删除
#on delete set cascade
#当修改主表时 从表的关联字段也跟着修改
#on update cascade
alter table stumarks add foreign key (stuno) references stuinfo(id) on delete set null on update cascade;
#添加一条数据
insert into stumarks values(1,88,99);
mysql> show create table stumarks\G
*************************** 1. row ***************************
Table: stumarks
Create Table: CREATE TABLE `stumarks` (
`stuno` int(11) NOT NULL COMMENT '主键',
`ch` float DEFAULT NULL,
`math` float DEFAULT NULL,
PRIMARY KEY (`stuno`),
CONSTRAINT `stumarks_ibfk_1` FOREIGN KEY (`stuno`) REFERENCES `stuinfo`(`id`) on delete set null on update cascade ;
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
#CONSTRAINT `stumarks_ibfk_1`:是mysql分配给外键的名字
#指定一个名字给外键(添加一个索引)
alter table stumarks add CONSTRAINT `stuno` foreign key (stuno) references stuinfo(id) on delete cascade on update cascade;
#删除外键
alter table stumarks drop foreign key stumarks_ibfk_1;
#提醒:
如果要在某个字段上添加外键,这个字段必须有索引才可以,如果这个字段没有索引就直接添加外键,那么mysql会自动创建索引.
(2)外键的操作
1.严格约束(外键约束),保证引用的完整性
2.置空操作(set null):主表记录删除或者更新,从表的外键字段设置为null
3.联级操作(cascade): 主表的记录删除或者更新,从表外键字段一起发生变化
4.一般都是删除的时候外键字段置空,修改的时候更新关联
alter table stumarks add foreign key (stuno) references stuinfo(id) on delete set null on update cascade;
注意:从表的关联(外键)字段一定不能是主键
外键在数据量特别小的时候会用上,一般是开发者自己清楚数据库表的设计,你知道那个是主表,那个是从表,然后手动修改,使用'事务',为什么了?
1.mysql表中设置外键会影响效率.
2.一般来说,每个从表都是单独数据,需要用到单独操作.