当前位置：首页 >数据库 > 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');//可以插入相同的idinsert 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。