继续使用上篇文章https://blog.csdn.net/z714405489/article/details/90524663#_446中的例子,也就是两个存在一定关系的表:分类表与商品表。
表1:商品分类
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,'馋嘴零食','花生瓜子八宝粥+辣条');
表2:商品
create table product(
pid int primary key auto_increment,
pname varchar(10),
price double,
pdate timestamp not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
cno int
);
insert into product (pid,pname,price,cno)values(null,'华为p30',4999,1);
insert into product (pid,pname,price,cno)values(null,'红米note4',1999,1);
insert into product (pid,pname,price,cno)values(null,'耐克',499,2);
insert into product (pid,pname,price,cno)values(null,'老村长',88,3);
insert into product (pid,pname,price,cno)values(null,'劲酒',35,3);
insert into product (pid,pname,price,cno)values(null,'小熊饼干',4,4);
insert into product (pid,pname,price,cno)values(null,'卫龙辣条',2,5);
insert into product (pid,pname,price,cno)values(null,'旺旺雪饼',3,5);
现在往商品表中继续插入数据:
insert into product (pid,pname,price,cno)values(null,'德芙巧克力',10,12);
由于cno为12在分类表中的cid并不存在这个编号,所以这条数据虽然能插入成功,但却是没有意义的,因为我们假定这两个表之间是存在约束关系的。
约束
概念
对表中的数据进行限定,保证数据的正确性、有效性和完整性。
分类
- 非空约束:not null
- 唯一约束:unique
- 主键约束:primary key
- 外键约束:foreign key
非空约束:not null
顾名思义,意味着数据的值不能为null
添加约束的方式有三种:
- 创建表时添加约束
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL -- name为非空
);
- 创建表完后,添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
- 删除非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
唯一约束:unique
意味着被修饰的列,值不能重复。
- 添加唯一约束:
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束
);
或在创建表后添加:
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
- 删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
- 唯一约束限定的列的值可以有多个null
- 唯一约束不可以作为其它表的外键
- 可以有多个唯一约束
主键约束:primary key
含义:非空且唯一。
- 外键都是指向另外一张表的主键
- 主键一张表只能有一个,是表中记录的唯一标识
添加:
create table stu(
id int primary key,-- 给id添加主键约束
name varchar(20)
);
或建表之后添加主键:
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
删除:
ALTER TABLE stu DROP PRIMARY KEY;
自动增长:
- 概念:如果某一列是数值类型的,使用 auto_increment 可以使该列的值实现自动增长。
- 实现:
create table stu(
id int primary key auto_increment,-- 给id添加主键约束
name varchar(20)
);
- 删除:
ALTER TABLE stu MODIFY id INT;
- 修改:
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
外键约束:foreign key
让表与表产生关系,从而保证数据的正确性。A表的主键,在B表中字段出现,就是外键。
外键是用来实现参照完整性的,不同的外键约束方式将可以使两张表紧密的结合起来,特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。
语法:
- 创建表时,可以添加外键
create table 表名(
....
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
还有另一种就是创建完表后再添加外键——
alter table 表名 add foreign key (外键字段名称) references 主表名称(主表列名称);
- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
- 级联操作
含义是若有两个或以上表之间存在关联,当一个表发生改变时,希望关联的表可以跟着进行改变。(使用需谨慎)
1. 添加级联操作
语法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;
- 分类:
- 级联更新:ON UPDATE CASCADE
- 级联删除:ON DELETE CASCADE
外键约束案例:
在本例中,给product中的cno添加一个外键约束,指向的是category中的cid:
alter table product add foreign key(cno) references category(cid);
那么,cno就是product的一个外键。
如果在添加约束前插入下面的数据,添加约束将会报错,因为数据不符合即将建立的约束。
同理,当添加约束后再插入此数据,将会插入失败。
insert into product (pid,pname,price,cno)values(null,'德芙巧克力',10,12);
建立约束后,若想从category表中删除分类为5的信息,将会失败:
若想成功删除,需要先把product表中所有cid为5的数据删除。