添加外键约束

添加外键约束

1.创建数据库

create database mydb;

2.创建分类表表，插入数据

create table category(
  cid int primary key auto_increment,
  cname varchar(10),
  cdesc varchar(31)
);

insert into category values(null,'手机数码','电子产品,黑马生产');
insert into category values(null,'鞋靴箱包','江南皮鞋厂倾情打造');
insert into category values(null,'香烟酒水','黄鹤楼,茅台,二锅头');
insert into category values(null,'酸奶饼干','娃哈哈,蒙牛酸酸乳');
insert into category values(null,'馋嘴零食','瓜子花生,八宝粥,辣条');

3.创建商品表，插入数据

pid:商品ID
pname:商品名称
price:商品的价格
pdate:生产日期
cno:商品分类ID

create table product(
	pid int primary key auto_increment,
  	pname varchar(10),
  	price double,
  	pdate timestamp,
  	cno int
);

insert into product values(null,'小米mix4',998,null,1);
insert into product values(null,'锤子',2888,null,1);
insert into product values(null,'阿迪王',99,null,2);
insert into product values(null,'老村长',88,null,3);
insert into product values(null,'劲酒',35,null,3);
insert into product values(null,'小熊饼干',1,null,4);
insert into product values(null,'卫龙辣条',1,null,5);
insert into product values(null,'旺旺大饼',1,null,5);

多表之间的关系如何来维护 ?

外键约束: foreign key

给product中的这个cno 添加一个外键约束

alter table product add foreign key(cno) references category(cid);

从分类表中,删除分类为5信息

delete from category where cid =5; //删除失败

首先得去product表, 删除所有分类ID为5的商品