首先,创建一张学生表和一张班级表,使它们通过班级编号产生关联(班级编号作为学生表的外码)
1.外键的两种创建方式
CREATE TABLE t_class(
cno int(4) primary key auto_increment, -- 班级编号(主码)
cname varchar(10) not null,-- 班级名称
room char(4) -- 教室
);
create table t_student(
sno int(6) primary key auto_increment,
sname varchar(6) not null,
age int (3),
score double(4,1),
classno int(6),
constraint fk_stu_classno foreign key(classno) references t_class(cno)-- 添加外键方式一
);
create table t_student(
sno int(6) primary key auto_increment,
sname varchar(6) not null,
age int (3),
score double(4,1),
classno int(6)
);
alter table t_student add constraint fk_t_classno foreign key(classno) references t_class(cno)-- 添加外键方式二
insert into t_class values(null,"java001","r001"),(null,"java002","r002"),(null,"大数据001","r003");
insert into t_student values(null,"张三",23,98,1),(null,"李四",24,98,2),(null,"王五",25,63,2);
2.外键策略
引入:
-- 问题:某个班级有学生,直接删除班级编号,会报错
delete from t_class where cno=2;
外键策略1:No Action:先保证2班没有学生(删除学生或将学生的班级编号改为null)
update t_student set classno=2 where classno=null;-- 法一
delete from t_student where classno=2;-- 法二
外键策略2:cascade 级联
alter table t_student add constraint fk_t_classno foreign key(classno) references t_class (cno) -- 创建外键
alter table t_student drop foreign key fk_t_classno; -- 删除外键
alter table t_student add constraint fk_t_classno foreign key (classno) references t_class(cno) on update cascade on delete cascade-- 级联操作
外键策略三:set null
alter table t_student drop foreign key fk_t_classno; -- 删除外键
alter table t_student add constraint fk_t_classno foreign key(classno) references t_class(cno) on update set null on delete set null;