MySQL外键与外键约束

外键

多表查询本质: 把多个表通过主外键关联关系连接(join)合并成一个大表,再去查询

外键概念:在从表(多方)创建一个字段,引用主表(一方)的主键,对应的这个字段就是外键

外键特点:

        1.从表外键的值是对主表主键的引用

        2.从表外键类型,必须与主表主键类型一致

外键约束

知识点

外键约束关键字: foieign key

外键约束的作用:

        限制从表插入:如果从表插入的外键值,在主表中不存在,就插入失败

        限制主表删除:如果主表的主键值已经被从表引用,在主表删除数据的时候,就删除失败

注意:如果想要使用外键约束,存储引擎需要是InnoDB

建表时添加外键约束:create table 从表名(...[CONSTRATNT]) foreign key(外键名) references 主表名(主键名);

建表后添加外键约束:create table 从表名 add [CONSTRATNT] foreign key(外键名) references 主表名(主键名);

示例

# 分类表
CREATE TABLE category
(
    cid   VARCHAR(32) PRIMARY KEY,
    cname VARCHAR(100) #分类名称
);
# 商品表
CREATE TABLE products
(
    pid         varchar(32) PRIMARY KEY,
    pname       VARCHAR(40),
    price       DOUBLE,
    category_id varchar(32)
);
# 演示没有外键约束出现的问题
# 演示往从表中插入主表不存在的数据
insert into products values('p001','联想笔记本',4999,'c001'); # 插入成功,因为没有约束
insert into products values('p002','华为笔记本',4999,'c001'); # 插入成功,因为没有约束
insert into products values('p003','小米笔记本',4999,'c001'); # 插入成功,因为没有约束

# 往主表中插入数据
insert into category values('c001','电脑');
# 演示主表删除从表已经引用的数据
delete from category where cid = 'c001'; # 删除成功,因为没有约束

# 问题1:以上问题如何解决? 添加外键约束
# 问题2:如何添加外键约束? 前提是innodb存储引擎,myisam存储引擎不支持外键约束

# 注意: 如果修改了存储引擎,需要重新建表才能生效,否则还是原来的存储引擎
# 查看之前建表语句
show create table category; -- ENGINE=MyISAM ...
show create table products; -- ENGINE=MyISAM ...

# 添加显示成功,但是实际无效,因为原来建的表还是MyISAM引擎
alter table products add  foreign key(category_id) references category(cid);

# 注意: 修改完存储引擎后,原来建的表都不会生效,除非重新建表
# 删除重建
drop table category;
drop table products;
# 建表
# 分类表
CREATE TABLE category
(
    cid   VARCHAR(32) PRIMARY KEY,
    cname VARCHAR(100) #分类名称
);
# 商品表
CREATE TABLE products
(
    pid         varchar(32) PRIMARY KEY,
    pname       VARCHAR(40),
    price       DOUBLE,
    category_id varchar(32)
);
# 添加约束 成功
alter table products add  foreign key(category_id) references category(cid);

演示外键约束

# 多表查询准备工作
create database day04;
use day04;
# 分类表
CREATE TABLE category
(
    cid   VARCHAR(32) PRIMARY KEY,
    cname VARCHAR(100) #分类名称
);
# 商品表
CREATE TABLE products
(
    pid         varchar(32) PRIMARY KEY,
    pname       VARCHAR(40),
    price       DOUBLE,
    category_id varchar(32),
    foreign key(category_id) references category(cid)
);
# 演示外键约束限制从表插入数据
insert into products values ('p01','联想笔记本',9999,'c001'); # 插入失败
# 如何让从表插入数据成功? 方式1: 主表提前插入从表要引用的c001数据, 方式2: 从表插入的时候外键保留位空
# 方式1: 主表提前插入从表要引用的c001数据
insert into category values('c001','电脑');
# 再次插入数据
insert into products values ('p01','联想笔记本',9999,'c001'); # 插入成功
insert into products values ('p03','小米笔记本',5999,'c001'); # 插入成功
# 方式2: 从表插入的时候外键保留位空
insert into products values ('p02','联想笔记本',9999,null); # 插入成功


# 演示外键约束限制主表删除数据
delete from category where cid = 'c001';
# 如何让主表删除数据成功? 方式1: 直接把引用主表c001的从表数据删除, 方式2: 把引用主表c001d从表外键值改为空
# 方式1: 直接把引用主表c001的从表数据删除
delete from products where pid = 'p01';
# 方式2: 把引用主表c001d从表外键值改为空
update products set category_id = null where pid = 'p03';
# 主表再次删除
delete from category where cid = 'c001';