– 外键约束
– 格式:forign key(外键名) references 主表(参照列名)
– 比如,我们想要使用 clazz_id 字段将 tb_clazz 表和 tb_stu 表联合起来
– clazz_id int references tb_clazz(id)
– 外键是构建于一张表的两个字段或者两张表的两个字段之间的关系
– 主要是用来解决数据冗余(重复的数据太多)问题。
– 简单理解,就是在两张表之间找到一个参照物,然后让它们发生关系。
– 使用注意点:
– 1)子表 tb_stu(从表)外键列 clazz_id 的值必须在父表 tb_clazz(主表)参照列值的范围内
– 或者为空(也可以考虑加非空约束),千万不能是其他的。
– 2)外键参照的只能是主表的主键或者唯一键,保证子表记录可以准确定位到被参照的记录。
– 3)当主表的记录被子表参照时,主表记录不允许被删除。
– 4)建表时可以考虑增加以下设置:(强制性)
– ① on delete cascade:当父表中的行被删除的时候,同时删除在子表中依靠的行。
– ② on delete set null:将依靠的外键值转换为空值。
– 存在问题的 tb_stu
create table tb_stu (
id int primary key,
name varchar(20),
sex char(4) check (sex = ‘男’ or sex = ‘女’),
age int check(age > 0 and age < 65),
address varchar(100) default ‘广州天河’,
phone varchar(30) unique,
clazz varchar(20), – 班级
pro varchar(20), – 学科
bzr varchar(20) – 班主任
);
insert into tb_stu
values (1, ‘翠花’, ‘女’, 18, ‘广州黄埔’, ‘13800138000’, ‘java9’, ‘java’, ‘月圆小姐姐’);
insert into tb_stu
values (2, ‘翠花’, ‘女’, 18, ‘广州黄埔’, ‘13800138001’, ‘java9’, ‘java’, ‘月圆小姐姐’);
– 创建 tb_clazz,抽取出来之后的表,当做是一个用于参照的主表
create table tb_clazz (
id int primary key,
clazz varchar(20), – 班级
pro varchar(20), – 学科
bzr varchar(20) – 班主任
);
insert into tb_clazz
values (1, ‘java9’, ‘java’, ‘月圆小姐姐’);
– 改造 tb_stu
create table tb_stu2 (
id int primary key,
name varchar(20),
sex char(4) check (sex = ‘男’ or sex = ‘女’),
age int check(age > 0 and age < 65),
address varchar(100) default ‘广州天河’,
phone varchar(30) unique,
-- 使用外键的方式来优化数据冗余
-- clazz_id references tb_clazz(id)
-- clazz_id references tb_clazz(id) on delete cascade -- 强删
clazz_id references tb_clazz(id) on delete set null -- 设置为 null
);
– 优化后的插入语句
insert into tb_stu2
values (1, ‘翠花’, ‘女’, 18, ‘广州黄埔’, ‘13800138000’, 1);
select * from tb_stu2;
select * from tb_clazz;
– 假设以后,我们获取外边传入进来的 clazz_id 的值就可以去查对应的数据了
– clazz_id 是一个动态的值,实际开发中,会当做一个参数由外面传进来。
select * from tb_clazz where id = clazz_id;
– 针对上面的注意点,进行演示说明:
– 1)如果传递一个范围以外的值,查不到对应的数据
select * from tb_clazz where id = 2;
– 2)你参照值只能是唯一的,不能是其他的,就是为了避免数据冗余。
– 3)如果我直接删除 tb_clazz 会报表中的主键、唯一键被外键引用异常。
– 如果不解除相关之间的关系,删除就会导致数据的不完整性。
drop table tb_clazz;
– 解决方式1:删掉 tb_clazz 中指定的数据,而不是整张表。
– 先把班级中的学生清空,才能删掉班级。
delete from tb_stu2 where clazz_id = 1;
delete from tb_clazz where id = 1;
– 解决方式2:修改列中的值为 null,或者其他值
– 只要解除两者之间的关系即可
update tb_stu2 set clazz_id = null where id = 1;
delete from tb_clazz where id = 1;
select * from tb_stu2;
select * from tb_clazz;
drop table tb_stu2;