数据库约束

目录

一、概念

二、约束类型

2.1 not null 非空约束

2.2 default 默认值

2.3 unique 唯一约束

2.4 primary key 主键约束

冲突处理：

复合主键：

2.5 foreign key 外键约束

---

一、概念

        数据库约束是指对数据库表中的数据所施加的规则或条件，用于确保数据的准确性和可靠性。这些约束可以是基于数据类型、值范围、唯一性、非空等规则，以确保数据的正确性和相容性。

二、约束类型

类型	说明
not null 非空约束	指定非空约束的列不能存储 null 值
default 默认约束	当没有给列赋值时使用的默认值
unique 唯一约束	指定唯一约束的列每行数据必须有唯一的值
primary key 主键约束	not null 和 unique 的结合，可以指定一个列或多个列，有助于防止数据重复和提高数据的查询性能
foreign key 外键约束	外键约束是一种关系约束，用于定义两个表之间的关联关系，可以确保数据的完整性和一致性

2.1 not null 非空约束

        创建表时定义某个列不允许为 null （必填）时，则插入数据时，必须在该列写入有效数据。

2.2 default 默认值

        default 约束用于向列中插入默认值，如果在插入数据时没有设置该列的数据，那么新一行的这一列将会填入设置的默认值。

示例：向学生表新插入一列 age，并设置默认值：

在没有指定年龄实际值为多少时，默认插入是18；当手动指定是 null 值的时候就是  null：

2.3 unique 唯一约束

        指定唯一约束的列，那么该列的值在所有的记录中不能重复，但是 null 可以重复插入。这一约束可以应用到身份证、学生学号等场景。

本例新增一列学生学号，对比没有加约束时和加了约束时能否插入相同的数据

没加约束前，容易造成数据冗余或者不符合逻辑的情况：

-- 插入一列学号，不设置任何约束
mysql> alter table stu add sno varchar(20) after age;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint      | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | int         | YES  |     | 18      |       |
| sno   | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


-- 新增一条记录
mysql> insert into stu values (2,'学生1',22,'100011');
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu;
+------+---------+------+--------+
| id   | name    | age  | sno    |
+------+---------+------+--------+
|    1 | Tom     |   18 | NULL   |
|    3 | 赵武    |   18 | NULL   |
|    2 | 张三    | NULL | NULL   |
|    2 | 学生1   |   22 | 100011 |
+------+---------+------+--------+
4 rows in set (0.00 sec)

-- 新增同一条记录
mysql> insert into stu values (2,'学生1',22,'100011');
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu;
+------+---------+------+--------+
| id   | name    | age  | sno    |
+------+---------+------+--------+
|    1 | Tom     |   18 | NULL   |
|    3 | 赵武    |   18 | NULL   |
|    2 | 张三    | NULL | NULL   |
|    2 | 学生1   |   22 | 100011 |
|    2 | 学生1   |   22 | 100011 |
+------+---------+------+--------+
5 rows in set (0.00 sec)

-- 将原来设定的学号增加约束前需要修改或者删除原来重复的记录
mysql> alter table stu modify sno varchar(20) UNIQUE;
ERROR 1062 (23000): Duplicate entry '100011' for key 'stu.sno'
mysql> delete from stu where sno = '100011';
Query OK, 2 rows affected (0.01 sec)


-- 约束sno字段只能有唯一值
mysql> alter table stu modify sno varchar(20) UNIQUE;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0


-- 约束id字段只能有唯一值
mysql> alter table stu modify id bigint UNIQUE;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0


-- 插入已经存在id为1的数据行时，报错
mysql> insert into stu values (1,'Jerry',15,'1001');
ERROR 1062 (23000): Duplicate entry '1' for key 'stu.id'

-- 只有插入不同id才能成功
mysql> insert into stu values (4,'Jerry',15,'1001');
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu;
+------+--------+------+------+
| id   | name   | age  | sno  |
+------+--------+------+------+
|    1 | Tom    |   18 | NULL |
|    3 | 赵武   |   18 | NULL |
|    2 | 张三   | NULL | NULL |
|    4 | Jerry  |   15 | 1001 |
+------+--------+------+------+
4 rows in set (0.00 sec)


-- 插入已经存在 1001 学号的记录，报错
mysql> insert into stu values (5,'Rose',26,'1001');
ERROR 1062 (23000): Duplicate entry '1001' for key 'stu.sno'
mysql> insert into stu values (5,'Rose',26,'1005');
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu;
+------+--------+------+------+
| id   | name   | age  | sno  |
+------+--------+------+------+
|    1 | Tom    |   18 | NULL |
|    3 | 赵武   |   18 | NULL |
|    2 | 张三   | NULL | NULL |
|    4 | Jerry  |   15 | 1001 |
|    5 | Rose   |   26 | 1005 |
+------+--------+------+------+
5 rows in set (0.00 sec)

2.4 primary key 主键约束

主键约束唯一标识数据库表中的每条记录；

主键必须包含唯一的值，且不能包含 null 值，相当于 unique 和 not null 约束的叠加；

每一个表只能有一个主键，可以由单个列或多个列组成；

通常为每张表都指定一个主键，主键列建议使用 bigint 类型；

好处：有利于更快更容易找出表中的一个特定的记录。

1、primary key 相当于 unique 和 not null 叠加：

2、通常在设计表时把主键列设置为自动增长，auto_increment

让数据库维护主键值的增长，不需要程序员自己计算了；

在插入时是先找到最大的值，然后在这个基础上加1；

如果某条记录写入失败，新生成的主键值将会作废 => 主键值可以不连续。

重新创建一张符合逻辑的表：

-- 插入数据设置主键列的值为 null
mysql> insert into stu values (null,'Tom',3,'1001');
Query OK, 1 row affected (0.07 sec)

-- 不指定主键
mysql> insert into stu (name,age,sno) values ('Jerry',1,'1002');
Query OK, 1 row affected (0.06 sec)

-- 即使设置主键值为null或者不指定主键，数据库会自动生成它们的主键
mysql> select * from stu;
+----+-------+------+------+
| id | name  | age  | sno  |
+----+-------+------+------+
|  1 | Tom   |    3 | 1001 |
|  2 | Jerry |    1 | 1002 |
+----+-------+------+------+
2 rows in set (0.00 sec)

-- 由于学号冲突，产生了唯一冲突，导致插入作废，id为3的主键值作废
mysql> insert into stu (name,age,sno) values ('Jerry',1,'1002');
ERROR 1062 (23000): Duplicate entry '1002' for key 'stu.sno'

-- 但如果出现数据为空的错误是不会作废主键值的
mysql> insert into stu (name,age,sno) values (null,4,'1002');
ERROR 1048 (23000): Column 'name' cannot be null

mysql> insert into stu (name,age,sno) values ('Rose',null,'1003');
Query OK, 1 row affected (0.07 sec)

-- 新插入的记录id是4，不是3，也不是5
mysql> select * from stu;
+----+-------+------+------+
| id | name  | age  | sno  |
+----+-------+------+------+
|  1 | Tom   |    3 | 1001 |
|  2 | Jerry |    1 | 1002 |
|  4 | Rose  | NULL | 1003 |
+----+-------+------+------+
3 rows in set (0.00 sec)


-- 主键值可以不连续
mysql> insert into stu values (99,'Tim',23,'1004');
Query OK, 1 row affected (0.06 sec)

mysql> select * from stu;
+----+-------+------+------+
| id | name  | age  | sno  |
+----+-------+------+------+
|  1 | Tom   |    3 | 1001 |
|  2 | Jerry |    1 | 1002 |
|  4 | Rose  | NULL | 1003 |
| 99 | Tim   |   23 | 1004 |
+----+-------+------+------+
4 rows in set (0.00 sec)


-- 下一次的自增从主键的最大值开始
mysql> insert into stu (name,age,sno) values ('Luce',5,'1005');
Query OK, 1 row affected (0.06 sec)

mysql> select * from stu;
+-----+-------+------+------+
| id  | name  | age  | sno  |
+-----+-------+------+------+
|   1 | Tom   |    3 | 1001 |
|   2 | Jerry |    1 | 1002 |
|   4 | Rose  | NULL | 1003 |
|  99 | Tim   |   23 | 1004 |
| 100 | Luce  |    5 | 1005 |
+-----+-------+------+------+
5 rows in set (0.00 sec)

---

冲突处理：

        1、当插入相同的 id 时，会报主键冲突的错误；可以使用下面的语法，当发生冲突时，可以更新插入数据，使程序继续运行。

语法：

INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ...

示例：

-- 插入已经存在id值为100的记录时，报主键冲突
mysql> insert into stu values (100,'Sunny',18,'1006');
ERROR 1062 (23000): Duplicate entry '100' for key 'stu.PRIMARY'


-- 使用上面的语法，表示如果发生冲突则更新当前列的值
mysql> insert into stu values (100,'Sunny',18,'1006')
    -> on duplicate key update name = 'Sunny',age = 18,sno = '1006';
Query OK, 2 rows affected (0.01 sec)
# 两行受到了影响，表示删除了原来id为100的记录，又新写入了一条记录
# 与update stu set name = 'Sunny',age = 18,sno = '1006'; 语句效果等效


mysql> select * from stu;
+-----+-------+------+------+
| id  | name  | age  | sno  |
+-----+-------+------+------+
|   1 | Tom   |    3 | 1001 |
|   2 | Jerry |    1 | 1002 |
|   4 | Rose  | NULL | 1003 |
|  99 | Tim   |   23 | 1004 |
| 100 | Sunny |   18 | 1006 |  # 原先Luce的数据被修改了
+-----+-------+------+------+
5 rows in set (0.00 sec)

        2、也可以采用替换的方法，如果存在冲突则替换，不存在冲突则插入

语法：

REPLACE [INTO] table_name
 [(column [, column] ...)]
 VALUES 
(value_list) [, (value_list)] ...


 value_list: value, [, value] ...

示例：

mysql> select * from stu;
+-----+-------+------+------+
| id  | name  | age  | sno  |
+-----+-------+------+------+
|   1 | Tom   |    3 | 1001 |
|   2 | Jerry |    1 | 1002 |
|   4 | Rose  | NULL | 1003 |
|  99 | Tim   |   23 | 1004 |
| 100 | Sunny |   18 | 1006 |
+-----+-------+------+------+
5 rows in set (0.00 sec)

mysql> replace into stu values (100,'Herry',14,'1005');
Query OK, 2 rows affected (0.02 sec)  # 两行受影响，表示被替换

mysql> replace into stu values (101,'Sunny',18,'1006');
Query OK, 1 row affected (0.06 sec)   # 一行受影响，表示无冲突，直接插入

mysql> select * from stu;
+-----+-------+------+------+
| id  | name  | age  | sno  |
+-----+-------+------+------+
|   1 | Tom   |    3 | 1001 |
|   2 | Jerry |    1 | 1002 |
|   4 | Rose  | NULL | 1003 |
|  99 | Tim   |   23 | 1004 |
| 100 | Herry |   14 | 1005 |
| 101 | Sunny |   18 | 1006 |
+-----+-------+------+------+
6 rows in set (0.00 sec)

---

复合主键：

        一张表中不能存在多个主键，但一个主键可以由多个列共同组成。由多个列共同组成的主键被称为复合主键，主键是否冲突以多个列的组成进行判断。

-- 正常插入一条记录
mysql> insert into stu(id,name) values (1,'Tom');
Query OK, 1 row affected (0.06 sec)

-- 插入重复记录
mysql> insert into stu(id,name) values (1,'Tom');
ERROR 1062 (23000): Duplicate entry '1-Tom' for key 'stu.PRIMARY'  # 显示主键冲突，是由id和name两个列共同决定冲突与否的


-- 修改id值则插入成功
mysql> insert into stu(id,name) values (2,'Tom');
Query OK, 1 row affected (0.06 sec)

mysql> select * from stu;
+----+------+
| id | name |
+----+------+
|  1 | Tom  |
|  2 | Tom  |
+----+------+
2 rows in set (0.00 sec)

2.5 foreign key 外键约束

外键用于定义主表与从表之间的关系；

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

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

1、创建班级表（主表），并初始化数据

mysql> create table if not exists class(
    -> id bigint primary key auto_increment,
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.16 sec)

mysql> insert into class (name) values ('java1'), ('java2'), ('java3'), ('C++');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from class;
+----+-------+
| id | name  |
+----+-------+
|  1 | java1 |
|  2 | java2 |
|  3 | java3 |
|  4 | C++   |
+----+-------+
4 rows in set (0.00 sec)

2、重构学生表（从表），加入外键约束

-- 正常插入数据
mysql> insert into student(name,class_id) values ('Tom',1),('Jerry',2);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-------+----------+
| id | name  | class_id |
+----+-------+----------+
|  1 | Tom   |        1 |
|  2 | Jerry |        2 |
+----+-------+----------+
2 rows in set (0.00 sec)

-- 插入班级号为5，因为没有class表中没有对应的班级，因此插入失败
mysql> insert into student(name,class_id) values ('Rose',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`learnsql`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

-- 插入班级id可以是null，表示当前学生还未分配班级
mysql> insert into student(name,class_id) values ('Rose',null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+-------+----------+
| id | name  | class_id |
+----+-------+----------+
|  1 | Tom   |        1 |
|  2 | Jerry |        2 |
|  4 | Rose  |     NULL |
+----+-------+----------+
3 rows in set (0.00 sec)

在关系型数据库中删除主表中某条记录，得先删除从表中的记录：

-- 删除学生表（从表）中没有引用的记录，可以成功
mysql> delete from class where name = 'java3';
Query OK, 1 row affected (0.07 sec)

mysql> select * from class;
+----+-------+
| id | name  |
+----+-------+
|  1 | java1 |
|  2 | java2 |
|  4 | C     |
+----+-------+
3 rows in set (0.00 sec)


-- 删除从表中引用的记录，失败
mysql> delete from class where name = 'java1';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`learnsql`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))


-- 得先删除从表中的记录
mysql> delete from student where class_id = 1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+-------+----------+
| id | name  | class_id |
+----+-------+----------+
|  2 | Jerry |        2 |
|  4 | Rose  |     NULL |
+----+-------+----------+
2 rows in set (0.00 sec)

-- 才能删除主表中的记录
mysql> delete from class where name = 'java1';
Query OK, 1 row affected (0.06 sec)

mysql> select * from class;
+----+-------+
| id | name  |
+----+-------+
|  2 | java2 |
|  4 | C     |
+----+-------+
2 rows in set (0.00 sec)

从表存在时是不能直接删除主表的，得先删除从表才能删除主表：

mysql> drop table class;
ERROR 3730 (HY000): Cannot drop table 'class' referenced by a foreign key constraint 'student_ibfk_1' on table 'student'.
mysql> drop table student;
Query OK, 0 rows affected (0.10 sec)

mysql> drop table class;
Query OK, 0 rows affected (0.10 sec)