MySQL_08约束
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字段约束)
