MySQL 外键
使用 SQL 命令,将一个商品表拆分成 3 个表,并介绍了外键的使用。
数据准备
现在我们模拟建一个简单的电商平台数据库,首先新建一个数据库,并使用该数据库:
在数据库中新建一个物品 goods 表,表中各行如下
- 商品编号 id
- 商品名称 name
- 商品类型 cate_name
- 商品品牌 brand_name
- 商品价格 price
- 是否显示 is_show
- 是否售完 is_saleoff
create table goods(
id int unsigned primary key auto_increment not null,
name varchar(150) not null,
cate_name varchar(40) not null,
brand_name varchar(40) not null,
price decimal(10,3) not null default 0,
is_show bit not null default 1,
is_saleoff bit not null default 0
);
以如下命令为例,向 goods 表插入数据
现在所有数据都在一张表里,为了将来便于管理,我们希望将商品类型与品牌拆出来分别建商品分类表 goods_cates 与商品品牌表 goods_brands,而 goods 表中这两列的内容则改为新表中对应信息的主键值。
-- 新建goods_cates表
create table goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null
);
-- 将goods表中所有商品类型插入goods_cates表中的name列
insert into goods_cates (name) select cate_name from goods group by cate_name;
-- 其实上两条命令可以合并为一条,以创建goods_brands表为例
-- 注意:goods_brands表中对应列名为name,所以后面需要将goods表中的brand_name重命名为name。
-- (对于goods_brands表的后续操作仿照与goods_cates)
create table goods_brands(
id int unsigned primary key auto_increment,
name varchar(40) not null
) select brand_name as name from goods group by brand_name;
-- 将goods表中cates_name列的数据改为goods_cates表中对应的主键值
update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;
-- 将goods表中cate_name列的名字与类型约束改了,名字改为cate_id,类型改为与goods_cates主键的类型一样
alter table goods change cate_name cate_id int unsigned;
-- 为了避免goods表中cate_id中出现goods_cates中不存在的主键,我们可以将其设为外键,与goods_cates的主键关联
alter table goods add foreign key (cate_id) references goods_cates(id);
其实,在实际开发中,很少会使用外键约束,因为会降低效率。如果想要取消外键约束可以用如下命令
-- 先查看创建goods表的信息
show create table goods;
-- 可以发现输出中有这么两行
-- CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`cate_id`) REFERENCES `goods_cates` (`id`),
-- CONSTRAINT `goods_ibfk_2` FOREIGN KEY (`brand_id`) REFERENCES `goods_brands` (`id`)
-- 这便是外键的信息,从而知道外键的名字:goods_ibfk_1 与 goods_ibfk2
-- 删除外键
alter table goods drop foreign key goods_ibfk_1;
alter table goods drop foreign key goods_ibfk_2;