MySQL数据库约束从入门到精通_Mysql

一. 什么是数据库约束

数据库约束时数据库中用于强制数据完整性的规则，确保表中数据的准确性、一致性和有效性，通过限制表中数据的输入修改和删除行为，防止无效和不合理数据操作。

二. 数据库约束类型

在数据库中有不同的约束来约束着不同的操作，那么接下来一起看看有哪些数据库约束。

2.1 非空约束（not null）

非空约束的作用：

指定表中的某一个列中的值不能为null

创建一个学生表进行示例演示：

-- 创建student表
create table student(
  id bigint comment'编号',
  name varchar(20) comment'姓名'
);
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| field | type        | null | key | default | extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint      | yes  |     | null    |       |
| name  | varchar(20) | yes  |     | null    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
（此时表中结构中的null都为yes，此时表示当前id列和name列的值是可以为null的）
mysql> select *from student;
+------+------+
| id   | name |
+------+------+
|    1 | null |
+------+------+
1 row in set (0.00 sec)
-- 此时表中name列的值是可以为空的

指定name列不能为空：

-- 重新创建学生表
create table student(
  id bigint comment'编号',
  `name` varchar(20) not null comment'姓名'
);
-- 查看表结构
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| field | type        | null | key | default | extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint      | yes  |     | null    |       |
| name  | varchar(20) | no   |     | null    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
（此时name列的null那就变成了no，此时就表示值不能为null）
-- 添加数据
mysql> insert into student (id,name) values(1,null);
error 1048 (23000): column 'name' cannot be null
（此时就报错提示，不能添加null值）

那么添加数据之前，就可以查看一下当前表结构，看看添加的数据能否为null

2.2 默认值约束（default）

在我们刚才创建的表结构中，可以看到default的值为null，这表示当没有指定数据时，当前默认值是为空的，那么我们的默认值约束就可以将默认值null定义成自己想要的默认值

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| field | type        | null | key | default | extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint      | yes  |     | null    |       |
| name  | varchar(20) | no   |     | null    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

示例演示：

-- 重新创建student表
create table student(
  id bigint comment'编号',
  `name` varchar(20) not null comment'姓名' ,
  age int comment'年龄' default(18)
);
-- 查看表结构
mysql> desc student;
+-------+-------------+------+-----+---------+-------------------+
| field | type        | null | key | default | extra             |
+-------+-------------+------+-----+---------+-------------------+
| id    | bigint      | yes  |     | null    |                   |
| name  | varchar(20) | no   |     | null    |                   |
| age   | int         | yes  |     | 18      | default_generated |
+-------+-------------+------+-----+---------+-------------------+
3 rows in set (0.00 sec)
（此时age列中的default就为18，表中默认值为18）
-- 添加数据
mysql> insert into student(id,name) values(1,'张三');
query ok, 1 row affected (0.01 sec)
mysql> select *from student;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 | 张三   |   18 |
+------+--------+------+
1 row in set (0.00 sec)
（此时我添加数据时并没有指定age列的值，所以此时的默认值为18）

2.3 唯一约束（unique）

指定了唯⼀约束的列，该列的值在所有记录中不能重复，在我们的生活中，每一个人的身份证号一定是唯一的，这个时候记录数据时就要使用唯一约束避免数据重复的错误。

没有唯一约束的状态：

mysql> insert into student(id,name) values(1,'李四');
query ok, 1 row affected (0.01 sec)
mysql> select *from student;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 | 张三   |   18 |
|    1 | 李四   |   18 |
+------+--------+------+
2 rows in set (0.00 sec)
（此时张三和李四的编号是一样的，那么就重复了）

那么默认约束和唯一约束能不能一起使用呢？

-- 重新创建一个student表
create table student(
  id bigint comment'编号',
  `name` varchar(20) not null comment'姓名' ,
  age int comment'年龄' default 18 unique;
);
（这种写法是可以通过的，但是不建议这样使用）
mysql> desc student;
+-------+-------------+------+-----+---------+-------------------+
| field | type        | null | key | default | extra             |
+-------+-------------+------+-----+---------+-------------------+
| id    | bigint      | yes  |     | null    |                   |
| name  | varchar(20) | no   |     | null    |                   |
| age   | int         | yes  | uni | 18      | default_generated |
+-------+-------------+------+-----+---------+-------------------+
3 rows in set (0.00 sec)
mysql> insert into student(id,name) values(1,'张三');
query ok, 1 row affected (0.01 sec)
mysql> insert into student(id,name) values(1,'李四');
error 1062 (23000): duplicate entry '18' for key 'student.age'
age 列同时使用 default 和 unique 约束可能会引发问题。因为默认值是固定的（这里是 18），要是插入新记录时没有指定 age 值，就会都使用默认值，这会违反唯一约束。（所以这里发生了报错）

将id列加上唯一约束：

-- 重新创建student表
create table student(
  id bigint comment'编号' unique,
  `name` varchar(20) not null comment'姓名' ,
  age int comment'年龄' default(18) 
);
-- 查看表结构
mysql> desc student;
+-------+-------------+------+-----+---------+-------------------+
| field | type        | null | key | default | extra             |
+-------+-------------+------+-----+---------+-------------------+
| id    | bigint      | yes  | uni | null    |                   |
| name  | varchar(20) | no   |     | null    |                   |
| age   | int         | yes  |     | 18      | default_generated |
+-------+-------------+------+-----+---------+-------------------+
3 rows in set (0.00 sec)
（此时id列上的key就为uni，这个uni就是唯一约束的标识）
--添加数据
mysql> insert into student (id,name) values(1,'张三');
query ok, 1 row affected (0.00 sec)
mysql> insert into student (id,name) values(1,'李四');
error 1062 (23000): duplicate entry '1' for key 'student.id'
（此时就会报错：说明id列只能存在唯一值）
mysql> insert into student (id,name) values(2,'李四');
query ok, 1 row affected (0.00 sec)
(此时的记录就能添加成功)
mysql> select *from student;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 | 张三   |   18 |
|    2 | 李四   |   18 |
+------+--------+------+
2 rows in set (0.00 sec)

2.4 主键约束（primary key）

主键约束的特点必须包含唯一的值并且不能包含null值（可以理解为unique和not null的特点的结合）
每个表中只能有一个主键，可以由单个列或者多个列组成
建议为每张表都指定一个主键，主键列建议使用bigint类型（防止数据过大发生错误）

主键包含了unique和not null的特点，但是不能使用这两个约束代替主键约束：

注意：有些博客中会用unique和not null来代替主键约束，虽然mysql中是支持这样的语法，但是严格意义上来说这是不对的。

unique和not null是业务规则，对数据本身就有这样的要求，但是主键约束时数据库表中的一个特性，决定了数据如何组织依赖主键（这个在下文的外键就会说到），主键通常是搭配外键使用的。

由于存在上述区别，在实际应用中建议使用primary key来定义表的主键。

示例演示：

-- 创建student表，使用primary key主键
create table student(
  id bigint comment'编号'primary key,
  `name` varchar(20) not null comment'姓名' 
);
-- 查看表结构
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| field | type        | null | key | default | extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint      | no   | pri | null    |       |
| name  | varchar(20) | no   |     | null    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
（此时的key那里就显示主键约束的标识了）
-- 重新创建student表，使用not null+unique代替主键
create table student(
  id bigint comment'编号' not null unique,
  `name` varchar(20) not null comment'姓名' 
);
-- 查看表结构
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| field | type        | null | key | default | extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint      | no   | pri | null    |       |
| name  | varchar(20) | no   |     | null    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec
（此时key这里显示的也是pri主键标识，但是还是不建议这么使用）

添加数据测试：

-- 添加数据
mysql> insert into student values(1,'张三');
query ok, 1 row affected (0.00 sec)
（这是使用null not+unique代替主键的方式报的错误）
mysql> insert into student values(1,'李四');
error 1062 (23000): duplicate entry '1' for key 'student.id'
（这是使用主键方式报的错误）
mysql> insert into student values(1,'李四');
error 1062 (23000): duplicate entry '1' for key 'student.primary'
（所以还是会有些许差别，再次强调能用主键的地方用主键，不要使用null not+unique）
（此时添加成功）
mysql> insert into student values(2,'李四');
query ok, 1 row affected (0.00 sec)
-- 查看数据
mysql> select *from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
+----+--------+
2 rows in set (0.00 sec)

注意：表中是不能有多个主键的：

-- 创建student表
mysql> create table student(
    ->   id bigint comment'编号' primary key,
    ->   `name` varchar(20) comment'姓名' primary key
    -> );
error 1068 (42000): multiple primary key defined
（一个表是不能创建多个主键的）

但是可以由多个列共同组成一个主键（复合主键），主键是否冲突以多个列的组成进行判断：

-- 创建student表（使用复合主键）
create table student(
  id bigint comment'编号',
  `name` varchar(20) comment'姓名',
  primary key (id, name)
);
-- 查看表结构
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| field | type        | null | key | default | extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint      | no   | pri | null    |       |
| name  | varchar(20) | no   | pri | null    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
（此时id列和name列中的key都带有pri主键标识）
-- 添加数据
mysql> insert into student values(1,'张三');
query ok, 1 row affected (0.01 sec)
mysql> insert into student values(1,'李四');
query ok, 1 row affected (0.00 sec)
mysql> select *from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  1 | 李四   |
+----+--------+
2 rows in set (0.00 sec)
（此时id列是可以重复的，因为现在是根据id和name的值一起判断的）
-- id和name一起重复
mysql> insert into student values(1,'张三');
error 1062 (23000): duplicate entry '1-张三' for key 'student.primary'
（此时就会报错）

auto_increment（自动增长）

在之前创建的student表中，id列通常需要我们手动进行输入数据，每次添加前都需要查看一下当前id数到哪里了，这就很影响我们的效率，所以引入的 auto_increment（自动增长）

-- 创建学生表
create table student(
  id bigint comment'编号' primary key auto_increment,
  `name` varchar(20) comment'姓名'
);
-- 查看表结构
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| field | type        | null | key | default | extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint      | no   | pri | null    | auto_increment |
| name  | varchar(20) | yes  |     | null    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)mysql> desc student;
（此时extra那里就多了一个auto_increment（自动增长））
-- 添加数据
mysql> insert into student (name) values('张三');
query ok, 1 row affected (0.03 sec)
mysql> insert into student (name) values('李四');
query ok, 1 row affected (0.00 sec)
mysql> select *from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
+----+--------+
2 rows in set (0.00 sec)
（通过查看表中的数据就可以发现，id列的编号自己进行增加和添加了）
-- 在有自动增加的情况下，null值为一个占用符
mysql> insert into student (id,name) values(null,'李四');
query ok, 1 row affected (0.00 sec)
mysql> select *from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 李四   |
+----+--------+
3 rows in set (0.00 sec)
-- 手动输入id列的值
mysql> select *from student;
+-----+--------+
| id  | name   |
+-----+--------+
|   1 | 张三   |
|   2 | 李四   |
|   3 | 李四   |
| 100 | 王五   |
+-----+--------+
4 rows in set (0.00 sec)
（此时id列的值就是我们手动添加的值，但是现在自增的值又会是多少呢）
-- 查看自增的值
mysql> show create table student;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table   | create table                                                                                                                                                                                                                                    |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | create table `student` (
  `id` bigint not null auto_increment comment '编号',
  `name` varchar(20) default null comment '姓名',
  primary key (`id`)
) engine=innodb auto_increment=101 default charset=utf8mb4 collate=utf8mb4_0900_ai_ci     |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
（此时auto_increment=101，所以下一次再进行添加的时候，自增的值就会变成101）
-- 添加数据
mysql> insert into student (name) values('赵六');
query ok, 1 row affected (0.01 sec)
mysql> select *from student;
+-----+--------+
| id  | name   |
+-----+--------+
|   1 | 张三   |
|   2 | 李四   |
|   3 | 李四   |
| 100 | 王五   |
| 101 | 赵六   |
+-----+--------+
5 rows in set (0.00 sec)

主键或唯⼀键冲突时的更新操作

当我们提前预测到当前添加的数据可能会出现主键冲突的情况下，我们可以对之前插入的数据进行修改，这样就避免了主键冲突的现象：

更新数据的方式一：

-- 创建student表
create table student(
  id bigint comment'编号' primary key,
  `name` varchar(20) comment'姓名',
  sno varchar(20) comment'学号'
);
-- 添加数据
mysql> insert into student(id,name,sno) values(1,'张三','10001');
query ok, 1 row affected (0.01 sec)
mysql> insert into student(id,name,sno) values(1,'张三',10002);
error 1062 (23000): duplicate entry '1' for key 'student.primary'
（此时直接添加就会发生报错，但是此时两条数据的差别就在于学号，那么我就可以进行更新）
-- 更新数据
mysql> insert into student(id,name,sno) values(1,'张三',10002) on duplicate key update sno='10002';
query ok, 2 rows affected (0.00 sec)
（此时为什么有2条数据进行了改变呢，这是因为当前更新数据的操作的本质其实是：先将原来的数据进行了删除，再将更新的数据进行添加，所以这里是2次操作）
-- 查看
mysql> select *from student;
+----+--------+-------+
| id | name   | sno   |
+----+--------+-------+
|  1 | 张三   | 10002 |
+----+--------+-------+
1 row in set (0.00 sec)

更新数据的方式二：

-- 更新数据
mysql> replace into student(id,name,sno) values(1,'李四','10003');
query ok, 2 rows affected (0.01 sec)
（此时修改的记录也是两条，原理和方式一是一样的）
-- 查看
mysql> select *from student;
+----+--------+-------+
| id | name   | sno   |
+----+--------+-------+
|  1 | 李四   | 10003 |
+----+--------+-------+
1 row in set (0.00 sec)

这里需要注意的是，当没有发生主键冲突的时候，更新操作就会变成添加操作：

-- 更新数据
mysql> replace into student(id,name,sno) values(2,'王五','10004');
query ok, 1 row affected (0.01 sec)
（可以发现此时对数据就只进行了1次操作）
-- 查看
mysql> select *from student;
+----+--------+-------+
| id | name   | sno   |
+----+--------+-------+
|  1 | 李四   | 10003 |
|  2 | 王五   | 10004 |
+----+--------+-------+
2 rows in set (0.00 sec)

虽然mysql是支持这样的更新操作的，但是不建议这样进行更新数据，因为不管表中有没有主键冲突的数据，此时都会扫描一遍表中是否存在主键冲突，如果存在主键冲突的情况，那么就会进行更新操作（先删除再添加）,如果没有则进行添加操作，这个效率是非常低的

那么在工作中我们应该怎么去更新数据呢：

1. 先按条件去查询一下数据，看看有没有相应的记录

2. 没有的话就insert一条新纪录

3. 有的话，要么进行update操作（手动更新），要么将原数据的删除标识置为已删除，再写一条新数据

（在工作中是不建议直接替换老数据的，所以自己进行手动更新并存一档备份更加保险）

2.5 外键约束（foreign key）

外键约束用于定义主表（存在主键）和从表（存在外键）之间的关系

外键约束定义在从表的列上，主表关联的列必须是主键或者唯一约束

当定义外键后，要求从表中的外键列数据必须在主表的主键或唯一列存在或为null

那么为什么要将两张表之间建立这样的关系呢？直接将两张表写成一张表不是更方便吗？

外键约束也是对数据的一种校验，从表中使用了主表中的某个值，这个值必须要存在于主表中

创建外键的语法格式：

foreign key 字段名（哪个列）references 主表 （哪个列）

创建一个学生表（从表）和班级表（主表）------学生表依赖班级表：

-- 创建class表（主表）
create table class(
  id bigint primary key auto_increment,
  class_name varchar(20) comment'班级名称'
);
-- 添加数据
insert into class(class_name)values('软件工程1班');
insert into class(class_name)values('软件工程2班');
insert into class(class_name)values('软件工程3班');
insert into class(class_name)values('软件工程4班');
insert into class(class_name)values('软件工程5班');
-- 查看
mysql> select* from class;
+----+------------------+
| id | class_name       |
+----+------------------+
|  1 | 软件工程1班      |
|  2 | 软件工程2班      |
|  3 | 软件工程3班      |
|  4 | 软件工程4班      |
|  5 | 软件工程5班      |
+----+------------------+
5 rows in set (0.00 sec)
--创建student表（从表）
create table student(
  id bigint primary key auto_increment,
  `name` varchar(20) not null,
  class_id bigint,
  foreign key (class_id) references class(id)
);
-- 添加数据
insert into student(name,class_id) values('张三',1);
insert into student(name,class_id) values('李四',1);
insert into student(name,class_id) values('王五',2);
insert into student(name,class_id) values('赵六',3);
insert into student(name,class_id) values('钱七',4);
-- 查看
mysql> select* from student;
+----+--------+----------+
| id | name   | class_id |
+----+--------+----------+
|  1 | 张三   |        1 |
|  2 | 李四   |        1 |
|  3 | 王五   |        2 |
|  4 | 赵六   |        3 |
|  5 | 钱七   |        4 |
+----+--------+----------+
5 rows in set (0.00 sec)

此时如果学生表中的添加一个class_id这个列的数据不在班级表中的id中会发生什么呢？

mysql> insert into student(name,class_id) values('小明',6);
error 1452 (23000): cannot add or update a child row: a foreign key constraint fails (`test`.`student`, constraint `student_ibfk_1` foreign key (`class_id`) references `class` (`id`))
（此时就会发生报错）

主表中数据和从表中数据的关系：

主表和从表的删除：

-- 删除主表中的软件工程1班这条数据
mysql> delete from class where class_name='软件工程1班';
error 1451 (23000): cannot delete or update a parent row: a foreign key constraint fails (`test`.`student`, constraint `student_ibfk_1` foreign key (`class_id`) references `class` (`id`))
（此时是删除不了的，因为在从表中还有数据和主表中的软件工程1班这条数据建立着关系）
-- 删除主表中的软件工程5班这条数据
mysql> delete from class where class_name='软件工程5班';
query ok, 1 row affected (0.01 sec)
（此时是可以删除的 ，因为之前主表中的数据和从表中的数据中，软件工程5班这条数据是没有和从表的数据有建立关系的，所以此时是可以删除的）

那么此时我应该怎么删除有关联关系的数据呢？

先删除从表中的数据，让从表中数据和主表中的数据断开关联关系
mysql> delete from student where name='钱七';
query ok, 1 row affected (0.01 sec)
mysql> delete from class where class_name='软件工程4班';
query ok, 1 row affected (0.01 sec)
（此时先删除student表中的钱7这条数据，那么从表中就没有数据和主表中的软件工程4班这条数据有关系了，所以此时就可以删除这条数据了）

那么假设我将从表的数据全部删除后，能不能将主表进行删除呢？

-- 删除从表数据
mysql> delete from student;
query ok, 4 rows affected (0.01 sec)
-- 删除主表
mysql> drop table class;
error 3730 (hy000): cannot drop table 'class' referenced by a foreign key constraint 'student_ibfk_1' on table 'student'.
（其实现在还是不能直接删除主表，因为从表和主表之间还是存在联系，只有将从表和主表这层关系断开才能够删除主表）
方法1: 删除从表，再删除主表
mysql> drop table student;
query ok, 0 rows affected (0.01 sec)
mysql> drop table class;
query ok, 0 rows affected (0.01 sec)
方法2：删除从表中的外键
mysql> drop table class;
error 3730 (hy000): cannot drop table 'class' referenced by a foreign key constraint 'student_ibfk_1' on table 'student'.
mysql> alter table student drop foreign key student_ibfk_1;
query ok, 0 rows affected (0.01 sec)
records: 0  duplicates: 0  warnings: 0
（这里的删除外键的名称就是上面报错系统自己生成的名称）

工作中数据库层面其实是不创建外键的（数据量大的情况下），我们一般是在java这种层面处理关联关系，保证数据正确，校验好之后才进行入库的

2.6 check约束

check约束其实就是对我们插入的数据进行判断的最直接的约束

check约束可以应用于一个或者多个列，限制可以接受的数据值，保证数据的准确性，但是check约束在8.0.16版本才开始支持，之前的版本会忽略check的定义，为了保证版本兼容问题，我们几乎不用check约束

示例演示：

-- 创建student类
mysql> create table student(
    -> id bigint primary key auto_increment,
    -> name varchar(20)not null,
    -> age int default 18,
    -> check(age>=16),
    -> gender char(1),
    -> check(gender ='男' or gender ='女')
    -> );
query ok, 0 rows affected (0.02 sec)
-- 添加数据
mysql> insert into student values(null,'张三',15,'男');
error 3819 (hy000): check constraint 'student_chk_1' is violated.
（当我们添加的数据中年龄<16就会触发第一个check约束）
mysql> insert into student values(null,'张三',19,'x');
error 3819 (hy000): check constraint 'student_chk_2' is violated.
（当我们添加的数据中性别不是男或者女其中一个就会触发第二个check约束）
-- 正确添加
mysql> insert into student values(null,'张三',19,'男');
query ok, 1 row affected (0.01 sec)
-- 查看
mysql> select *from student;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 张三   |   19 | 男     |
+----+--------+------+--------+
1 row in set (0.00 sec)

到此这篇关于mysql数据库约束的文章就介绍到这了,更多相关mysql数据库约束内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！