MySQL_08约束
1.什么是约束?
约束对应的英语单词:constraint
在创建表的时候,我们可以给表中的字段加一些约束,来保证这个表中数据的完整性、有效性。
约束的作用就是为了保证表中的数据有效。
2.常用的约束包括哪些?
约束名 | 关键字 |
---|---|
非空约束 | not null |
唯一性约束 | unique |
主键约束 | primary key(简称PK) |
外键约束 | foreign key(简称FK) |
检查约束 | check(MySQL不支持,Oracle支持) |
3.非空约束:not null
3.1创建表,使用not null
非空约束not null约束的字段不能为null。
现创建一个t_vip表,其中有id和name两个字段,使用not null约束name字段,同时插入两条记录。
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) not null
);
insert into t_vip(id,name) values(1,‘Zhangsan‘);
insert into t_vip(id,name) values(2,‘Lisi‘);
3.2使用sql脚本文件批量执行sql语句
我们可以创建一个后缀为.sql的sql脚本文件,将以上命令语句一并粘贴进去。
使用时,使用source命令导入该sql脚本文件,即可执行里面的所有命令。
输入source,然后直接将sql脚本文件拖进窗口,回车。
mysql> source D:\code\MySQL\MySQL_08约束\test01.sql
这种方式适用于执行大量sql语句。
3.3测试
插入如下数据:
insert into t_vip(id) values(3);
报错:
mysql> insert into t_vip(id) values(3);
ERROR 1364 (HY000): Field ‘name‘ doesn‘t have a default value
可见,name的值已经不可为null。
mysql> desc t_vip;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(255) | NO | | NULL | |
+-------+--------------+------+-----+---------+-------+
4.唯一性约束:unique
4.1创建表,使用unique
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) unique
);
insert into t_vip(id,name) values(1,‘Zhangsan‘);
insert into t_vip(id,name) values(2,‘Lisi‘);
4.2测试
查看表结构:
mysql> desc t_vip;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | UNI | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
1)插入一条name重复的记录:
insert into t_vip(id,name) values(3,‘Lisi‘);
插入失败:
mysql> insert into t_vip(id,name) values(3,‘Lisi‘);
ERROR 1062 (23000): Duplicate entry ‘Lisi‘ for key ‘name‘
2)插入两条只设定id的记录:
insert into t_vip(id) values(3);
insert into t_vip(id) values(4);
插入成功:
mysql> select * from t_vip;
+------+----------+
| id | name |
+------+----------+
| 1 | Zhangsan |
| 2 | Lisi |
| 3 | NULL |
| 4 | NULL |
+------+----------+
4 rows in set (0.00 sec)
name字段虽然被unique约束了,但是可以都为null。
4.3两个字段联合唯一
现新建一个表t_vip,有id、name、email三个字段,要求name和email两个字段联合唯一。
也就是说可以插入的记录:
insert into t_vip(id,name,email) values(1,‘Zhangsan‘,‘zhangsan@123.com‘);
insert into t_vip(id,name,email) values(2,‘Zhangsan‘,‘zhangsan@qq.com‘);
不可以插入的记录:
insert into t_vip(id,name,email) values(1,‘Zhangsan‘,‘zhangsan@123.com‘);
insert into t_vip(id,name,email) values(2,‘Zhangsan‘,‘zhangsan@123.com‘);
创建表:
使用unique(字段名1,字段名2)来实现两个字段联合唯一。
约束没有添加在列的后面,这种约束被称为表级约束。
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
email varchar(255),
unique(name,email)
);
insert into t_vip(id,name,email) values(1,‘Zhangsan‘,‘zhangsan@123.com‘);
测试:
1)插入与第一条记录name、email相同的记录。
报错
mysql> insert into t_vip(id,name,email) values(2,‘Zhangsan‘,‘zhangsan@123.com‘);
ERROR 1062 (23000): Duplicate entry ‘Zhangsan-zhangsan@123.com‘ for key ‘name‘
2)插入与第一条记录name相同,email不相同的记录。
插入成功
mysql> insert into t_vip(id,name,email) values(2,‘Zhangsan‘,‘zhangsan@qq.com‘);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_vip;
+------+----------+------------------+
| id | name | email |
+------+----------+------------------+
| 1 | Zhangsan | zhangsan@123.com |
| 2 | Zhangsan | zhangsan@qq.com |
+------+----------+------------------+
2 rows in set (0.00 sec)
结论:当需要给多个字段联合起来添加某一个约束时,需要使用表级约束。
5.not null和unique联合
非空约束和唯一性约束可以联合起来使用。
新建表t_vip,使用not null 和unique联合约束name字段:
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) not null unique
);
查看表结构:
mysql> desc t_vip;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(255) | NO | PRI | NULL | |
+-------+--------------+------+-----+---------+-------+
在mysql中,如果一个字段同时被not null 和 unique联合约束,就自动变成了主键字段。(注意:oracle中不一样)
测试:
insert into t_vip(id,name) values(1,‘ZhangSan‘);
insert into t_vip(id,name) values(2,‘ZhangSan‘);//报错,name不可重复
insert into t_vip(id) values(2);//也报错,name不能为null
6.主键约束(primary key,简称PK)【重要】
6.1主键概述
主键约束的相关术语:
- 主键字段:在一个字段上添加了主键约束,这样的字段就叫:主键字段。
- 主键值:主键字段中每一个值都叫:主键值。
主键有什么用?
- 主键值是每一行记录的唯一标识,相当于人的身份证号。
主键建议使用什么类型?
- int
- bigint
- char
- 等类型
注意:
- 任何一张表都有主键,没有主键,表无效。
主键的特征:唯一且不为null(not null + unique)
如何给一张表添加主键约束?
drop table if exists t_vip;
create table t_vip(
id int primary key,
name varchar(255)
);
插入数据:
insert into t_vip(id,name) values(1,‘ZhangSan‘);
insert into t_vip(id,name) values(2,‘ZhangSan‘);
insert into t_vip(id,name) values(1,‘Lisi‘);//报错,主键不可重复
insert into t_vip(name) values(‘Lisi‘);//报错,主键不可为null
查询表中数据:
mysql> select * from t_vip;
+----+----------+
| id | name |
+----+----------+
| 1 | ZhangSan |
| 2 | ZhangSan |
+----+----------+
2 rows in set (0.00 sec)
此外,还可以通过表级约束来添加主键:
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
primary key(id)
);
6.2复合主键
一个字段做主键叫单一主键,两个字段联合起来做主键叫复合主键。
通过表级约束来添加联合主键:
drop table if exists t_vip;
#id和name联合起来做主键
create table t_vip(
id int,
name varchar(255),
primary key(id,name)
);
插入数据:
insert into t_vip(id,name) values(1,‘ZhangSan‘);
insert into t_vip(id,name) values(1,‘LiSi‘);//可以插入相同的id
insert into t_vip(id,name) values(1,‘ZhangSan‘);//错误,不可插入相同的id和name
查询结果:
mysql> select * from t_vip;
+----+----------+
| id | name |
+----+----------+
| 1 | LiSi |
| 1 | ZhangSan |
+----+----------+
2 rows in set (0.00 sec)
在实际开发中,不建议使用复合主键。建议使用单一主键。
理由:
主键值存在的意义就是这行记录的身份证号,只要意义达到即可。单一主键就可以实现这一意义,复合主键比较复杂,不建议使用。
另外,在一张表中,只能有一个主键,不可重复定义。
drop table if exists t_vip;
mysql> create table t_vip(
-> id int primary key,
-> name varchar(255) primary key
-> );
#报错,主键定义重复
ERROR 1068 (42000): Multiple primary key defined
6.3自然主键和业务主键
主键除了单一主键和复合主建外,还有如下分类:
- 自然主键:主键值是一个自然数,和业务无关。
- 业务主键:主键值和业务紧密关联,例如拿银行卡号来做主键值。
在实际开发中,使用自然主键较多一些,因为主键只需要做到不重复即可,不需要有意义。
业务主键不好,因为主键一旦和业务挂钩,那么当业务发生变动时,可能会影响到主键值,所以业务主键不建议使用。
6.4自动维护主键值机制
在mysql中,有一种机制,可以帮助我们自动维护主键值。
在主键后添加auto_increment:
drop table if exists t_vip;
create table t_vip(
id int primary key auto_increment,
name varchar(255)
);
插入记录,只设定name字段的值:
insert into t_vip(name) values(‘Zhangsan‘);
insert into t_vip(name) values(‘Zhangsan‘);
insert into t_vip(name) values(‘Zhangsan‘);
insert into t_vip(name) values(‘Zhangsan‘);
insert into t_vip(name) values(‘Zhangsan‘);
insert into t_vip(name) values(‘Zhangsan‘);
查看表中数据:
mysql> select * from t_vip;
+----+----------+
| id | name |
+----+----------+
| 1 | Zhangsan |
| 2 | Zhangsan |
| 3 | Zhangsan |
| 4 | Zhangsan |
| 5 | Zhangsan |
| 6 | Zhangsan |
+----+----------+
6 rows in set (0.00 sec)
可见,如果未设定主键的值,主键会从1开始,以1递增地自动赋值。
7.外键约束(foreign key,简称FK)【重要】
外键约束涉及到的相关术语:
- 外键约束:一种约束(foreign key)
- 外键字段:添加了外键约束的字段
- 外键值:外键字段中的每一个值
7.1设计数据库表描述“班级和学生”信息
业务背景:
请设计数据库表,来描述“班级和学生”的信息。
7.1.1第一种方案
班级和学生信息都存于一张表中
drop table if exists t_school;
create table t_school(
no int primary key auto_increment,
name varchar(255),
classno int,
classname varchar(255)
);
insert into t_school(no,name,classno,classname) values
(1,‘Zhangsan‘,100,‘高三1班‘),
(2,‘Lisi‘,100,‘高三1班‘),
(3,‘WangWu‘,101,‘高三2班‘),
(4,‘ZhaoLiu‘,101,‘高三2班‘),
(5,‘Tom‘,100,‘高三1班‘),
(6,‘Jerry‘,101,‘高三2班‘),
(7,‘Lili‘,101,‘高三2班‘),
(8,‘Lorry‘,100,‘高三1班‘);
查看表:
+----+----------+---------+------------+
| no | name | classno | classname |
+----+----------+---------+------------+
| 1 | Zhangsan | 100 | 高三1班 |
| 2 | Lisi | 100 | 高三1班 |
| 3 | WangWu | 101 | 高三2班 |
| 4 | ZhaoLiu | 101 | 高三2班 |
| 5 | Tom | 100 | 高三1班 |
| 6 | Jerry | 101 | 高三2班 |
| 7 | Lili | 101 | 高三2班 |
| 8 | Lorry | 100 | 高三1班 |
+----+----------+---------+------------+
分析以上方案的缺点:有多项数据重复,数据冗余,空间浪费,是比较失败的设计。
7.1.2第二种方案
班级一张表,学生一张表。
班级表:t_class
+---------+------------+
| classno | classname |
+---------+------------+
| 100 | 高三1班 |
| 101 | 高三2班 |
+---------+------------+
学生表:t_student
+----+----------+------+
| no | name | cno |#(cno班级编号)
+----+----------+------+
| 1 | Zhangsan | 100 |
| 2 | Lisi | 100 |
| 3 | WangWu | 101 |
| 4 | ZhaoLiu | 101 |
| 5 | Tom | 100 |
| 6 | Jerry | 101 |
| 7 | Lili | 101 |
| 8 | Lorry | 100 |
+----+----------+------+
当cno字段没有任何约束时,可能会导致数据无效。比如可能出现一个102,但是102班级不存在。
所以为了保证cno字段中的值都是100和101,需要给cno字段添加一个外键约束。
即使用FK引用t_class这张表的classno。
添加后,cno字段就是外键字段,cno字段中的每一个值都是外键值。
注意:
- 被引用的t_class是父表,t_student是字表。
- 创建表的顺序是:
- 先创建父表,再创建子表。
- 删除表的顺序是:
- 先删子表,再删父表。
- 插入数据的顺序是:
- 先插入父,再插入子。
- 删除数据的顺序是:
- 先删除子,再删除父。
开始创建表:
#先删子,再删父
drop table if exists t_student;
drop table if exists t_class;
#先创父,再创子
create table t_class(
classno int primary key,
classname varchar(255)
);
create table t_student(
no int primary key auto_increment,#自增
name varchar(255),
cno int,
foreign key(cno) references t_class(classno)#添加外键约束,引用t_class表中的classno数据
);
#先插父,再插子
insert into t_class(classno,classname) values(100,‘高三1班‘);
insert into t_class(classno,classname) values(101,‘高三2班‘);
insert into t_student(no,name,cno) values
(1,‘Zhangsan‘,100),
(2,‘Lisi‘,100),
(3,‘WangWu‘,101),
(4,‘ZhaoLiu‘,101),
(5,‘Tom‘,100),
(6,‘Jerry‘,101),
(7,‘Lili‘,101),
(8,‘Lorry‘,100);
思考:子表中的外键引用的父表中的某个字段,被引用的这个字段必须是主键吗?
所以被引用的这个字段不一定是主键。但被引用的这个字段必须是唯一的,因为如果t_class表中的classno字段可以重复,那么在t_student表中,cno所引用的classno字段也就不确定了。
那么外键值可以为null吗?
插入一条cno为null的记录:
insert into t_student(name) values(‘Alice‘);
查看表:
mysql> select * from t_student;
+----+----------+------+
| no | name | cno |
+----+----------+------+
| 1 | Zhangsan | 100 |
| 2 | Lisi | 100 |
| 3 | WangWu | 101 |
| 4 | ZhaoLiu | 101 |
| 5 | Tom | 100 |
| 6 | Jerry | 101 |
| 7 | Lili | 101 |
| 8 | Lorry | 100 |
| 9 | Alice | NULL |
+----+----------+------+
9 rows in set (0.00 sec)
可见,外键值可以为null。
7.2总结
- 在设计表时,要考虑空间问题
- 实现父表和子表:
- 创建表:先创父,再创子
- 删除表:先删子,再删父
- 插入数据:先插父,再插子
- 删除数据:先删子,再删父
- 添加外键约束:foreign key(字段名) references 被引用表(被引用字段)。
- 外键值可以为null。
- 父表中被引用的字段不一定是主键,但必须唯一。(用unique字段约束)