数据库之约束条件和级联

本文主要是介绍数据库之约束条件和级联，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

【一】什么是约束条件

在数据库中，约束条件是一组规则或条件，用于限制表中数据的值，以确保数据的完整性和一致性。
约束条件可以应用于表的列级别或表级别，它们定义了数据库中数据的有效性和规范性。
约束条件在创建表时可以使用，也可以修改表的时候添加约束条件。
凡是不符合约束的数据，插入时就会失败。

【二】非空约束

NOT NULL

【1】作用

限制字段不能为NULL

【2】示例

创建表

mysql> create table t1(-> name varchar(6) not null,	--名字不能为空-> another_name varchar(6)	--别名可以为空-> );

插入，并查看

mysql> insert t1 values('bruce', null);
Query OK, 1 row affected (0.01 sec)mysql> select * from t1;
+-------+--------------+
| name  | another_name |
+-------+--------------+
| bruce | NULL         |
+-------+--------------+
1 row in set (0.00 sec)

【三】唯一性约束

UNIQUE

【1】作用

唯一约束确保列或一组列中的所有值都是唯一的，但允许包含空值。
- 空值可以有多个
- 具体值唯一

【2】示例

修改表

mysql> alter table t1 add id int unique;	--添加唯一性约束id
Query OK, 0 rows affected (0.07 sec)

插入，并查看

mysql> insert t1 (id, name) values (null, 'kan'), (1, 'lanny');	--插入一个空值和一个1
Query OK, 2 rows affected (0.00 sec)mysql> select id, name, another_name from t1;	--发现空值确实可以有多个
+------+-------+--------------+
| id   | name  | another_name |
+------+-------+--------------+
| NULL | bruce | NULL         |
| NULL | kan   | NULL         |
|    1 | lanny | NULL         |
+------+-------+--------------+

【四】组合使用

NOT NULL && UNIQUE

【1】作用

同时满足多个约束，不能为空且唯一

【2】示例

将name添加唯一性约束

mysql> alter table t1 modify name varchar(6) not null unique;
Query OK, 0 rows affected (0.08 sec)

查看表结构

mysql> desc t1;
+--------------+------------+------+-----+---------+-------+
| Field        | Type       | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| name         | varchar(6) | NO   | PRI | NULL    |       |
| another_name | varchar(6) | YES  |     | NULL    |       |
| id           | int(11)    | YES  | UNI | NULL    |       |
+--------------+------------+------+-----+---------+-------+

【五】查看约束条件

【1】表级约束

TABLE_CONSTRAINTS 表是 information_schema 数据库中的一张表，它存储了有关表级约束（例如主键、唯一键、外键等）的信息。

show databases;	--查看所有数据库
use information_schema;	--切换数据库
show tables;	--查看所有表
desc table_constraints;	--查看表的结构

mysql> desc table_constraints;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME    | varchar(64)  | NO   |     |         |       |
| TABLE_SCHEMA       | varchar(64)  | NO   |     |         |       |
| TABLE_NAME         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_TYPE    | varchar(64)  | NO   |     |         |       |
+--------------------+--------------+------+-----+---------+-------+

【2】参数说明

CONSTRAINT_CATALOG： 约束所属的目录名称。
CONSTRAINT_SCHEMA： 约束所属的数据库名称。
CONSTRAINT_NAME： 约束的名称。
TABLE_SCHEMA： 表所属的数据库名称。
TABLE_NAME： 表的名称。
CONSTRAINT_TYPE： 约束的类型，可能是 'PRIMARY KEY'、'UNIQUE'、'FOREIGN KEY' 等。

【3】查看指定表的表级约束

语法

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = '表名';

【五】表级约束和列级约束

【1】定义位置不同

表级约束：在表的定义末尾声明的，通常包含对表中多个列的约束条件
列级约束：在定义每个列的时候声明的，它与特定列相关

【2】适用范围

表级约束：表级约束提供更大的灵活性和复杂性，适用于需要涉及多列可以跨表的约束条件。
列级约束：在定义每个列的时候声明的，它与特定列相关。

【3】示例

创建新表
- name1和hobby1都是用的列级别约束
- name2和hobby2用的是表级约束

mysql> create table t2(-> name1 varchar(6) not null,-> hobby1 varchar(10) not null,-> name2 varchar(6),-> hobby2 varchar(10),-> constraint my_not_null check(name2 is not null and hobby2 is not null)-> );

插入数据

mysql> insert t2 values('burce','lily','kan',null);
Query OK, 1 row affected (0.01 sec)mysql> insert t2 values(null, 'cici', null, 'lulu');
ERROR 1048 (23000): Column 'name1' cannot be nullmysql> insert t3 values('lily', 'eat', null, null);
Query OK, 1 row affected (0.01 sec)

在你的表定义中，你创建了一个名为 my_not_null 的约束，该约束使用 CHECK 关键字来确保 name2 和 hobby2 的值都不为空。然而，MySQL 在处理 CHECK 约束时，并不会强制检查这些条件。
查看试试

mysql> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE-> FROM information_schema.TABLE_CONSTRAINTS-> WHERE TABLE_NAME = 't2';
Empty set (0.01 sec)

【六】主键约束

【1】说明

主键约束（Primary Key Constraint）： 主键约束是一种数据库约束，它确保表中的特定列（或列的组合）包含唯一且非空的值。主键约束是通过定义主键来实现的，表中只能有一个主键约束。
主键字段（Primary Key Field）： 主键字段是被定义为主键的表中的特定列。这个字段的值在整个表中必须是唯一的且不能为空。
主键值（Primary Key Value）： 主键值是主键字段中的具体数据值。每一行数据都必须有一个唯一的主键值，它用来标识该行数据。

【2】作用

唯一性和非空性： 主键确保了主键字段的值在整个表中是唯一的，且不能为空。每一行数据都必须具有唯一的主键值。
索引效果： 定义主键约束的字段会自动创建索引（index），提高数据检索的效率。这使得按主键进行检索或连接操作更为快速。
数据完整性： 主键确保了表中的每一行都有一个唯一标识，这有助于维护数据的完整性。通过主键，可以避免表中出现重复或空值的情况。
表的有效性： 一张合理设计的表应该有主键。如果表没有主键，可能导致数据关联和查询的困难，因此主键可以被视为表的有效性标志。

【3】根据个数进行分类

不论时单一主键还是复合主键，一张表只能有一个主键约束。

（1）单一主键约束

给一个字段添加一个主键约束，被称为单一主键

mysql> create table t1(	--列级约束-> id int primary key,-> name varchar(6)-> );
Query OK, 0 rows affected (0.03 sec)mysql> create table t2(	--表级约束-> id int,-> name varchar(6),-> primary key(id)-> );
Query OK, 0 rows affected (0.02 sec)mysql> create table t3(	--给主键重命名-> id int,-> name varchar(6),-> constraint my_id primary key(id)-> );
Query OK, 0 rows affected (0.03 sec)

（2）复合主键

给多个字段联合添加一个主键约束，被称为复合主键

mysql> create table t4(	--复合主键-> id int,-> name varchar(6),-> constraint my_id primary key(id, name)-> );
Query OK, 0 rows affected (0.03 sec)

【4】根据性质分类

（1）自然主键：

特点： 自然主键的值通常是一个自然数，且与业务无直接关系。这种主键是数据库中的一个自然属性，不是为了满足业务需求而人为设计的。
例子： 表中的自增长整数列、日期时间戳等都可以作为自然主键的候选。

（2）业务主键：

特点： 业务主键的值与当前表中的业务紧密相关，通常是为了满足业务需求而人为设计的。业务主键可能是某个实际业务中的标识符，如订单号、产品代码等。
影响： 如果业务发生改变，业务主键可能会受到影响，因此在设计数据库时，使用业务主键要谨慎，避免对数据库的可维护性产生负面影响。
例子： 订单表中的订单号、产品表中的产品代码等都可以作为业务主键的候选。

【5】自增主键

（1）说明

在 MySQL 中，可以通过使用 auto_increment 属性来创建自增主键列。
MySQL数据库管理系统中提供了一个自增数字，专门用来自动生成主键值，主键值不需要用户去维护，也不需要用户生成，MySQL会自动生成。

（2）示例

创建表

mysql> create table user_info(-> id int primary key auto_increment,-> name varchar(6)-> ) auto_increment=1;	--默认值为1，可以不写
Query OK, 0 rows affected (0.02 sec)

插入数据

mysql> insert user_info (name) values('bruce'), ('tom');
Query OK, 2 rows affected (0.00 sec)

查看结果

mysql> select * from user_info;
+----+-------+
| id | name  |
+----+-------+
|  1 | bruce |
|  2 | tom   |
+----+-------+

【6】重置主键

（1）为什么要

在删除一行记录的时候，主键还会自增，导致主键值跳跃
在清空数据表的时候，主键值仍将自增，但是主键会自动重置，因为是重构的表

（2）示例

删除数据

mysql> truncate user_info;	--清空
Query OK, 0 rows affected (0.02 sec)

插入一个查看

mysql> insert user_info (name) values('lily');
Query OK, 1 row affected (0.01 sec)mysql> select *from user_info;		--确实会自动重置
+----+------+
| id | name |
+----+------+
|  1 | lily |
+----+------+
1 row in set (0.00 sec)

删除一个再添加

mysql> delete from user_info where id=1;	--删除记录
Query OK, 1 row affected (0.00 sec)mysql> insert user_info (name) values('kan');	-- 插入新纪录
Query OK, 1 row affected (0.01 sec)mysql> select *from user_info;		--主键仍沿着之前的值增加
+----+------+
| id | name |
+----+------+
|  2 | kan  |
+----+------+
1 row in set (0.00 sec)

删除记录，重置主键

mysql> delete from user_info where id=2;	--删除记录
Query OK, 1 row affected (0.01 sec)mysql> alter table user_info auto_increment=1;	--重置主键
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> insert user_info (name) values('lulu');	--插入新记录
Query OK, 1 row affected (0.01 sec)mysql> select *from user_info;	--查看
+----+------+
| id | name |
+----+------+
|  1 | lulu |
+----+------+
1 row in set (0.00 sec)

【七】外键约束

外键会消耗额外的资源并且会增加表的复杂度
多数通过SQL语句的形式建立逻辑意义上的表关系

【1】说明

外键约束： 用于确保表中某个字段的值与另一表中的字段值相匹配，建立了表与表之间的关系。
外键字段： 被定义为外键的表中的特定列，存储与另一表中关联字段相匹配的值。
外键值： 外键字段中的具体数据值，对应着另一表中的关联字段值，建立了数据之间的关联关系。

【2】语法

foreign key(在当前表的字段名) references 关联的表名(关联的表中需要建立外键关系的字段名)

【3】分类和创建示例

（1）一对一关系

1.介绍

在一对一关系中，通常会将数据划分到两个表中，以优化数据库的性能和降低冗余。
- 用户表： 存储使用频率较高的数据字段，如 QQ 号码、座右铭、个人简介、爱好等。
- 用户详情表： 存储使用频率较低的数据字段，如邮箱、电话、学校等。
- 这样的设计可以节省资源，并降低数据库的压力，使得常用数据更容易访问。
外键字段建在任意一方都可以，但是推荐建在查询频率较高的较好的一方

2.示例

创建表

mysql> create table user_info(-> id int primary key auto_increment,-> phone int(11),-> sex char,-> age int-> );
Query OK, 0 rows affected (0.03 sec)mysql> create table user(-> id int primary key auto_increment,-> name varchar(6),-> detail_id int,-> foreign key(detail_id) references user_info(id)-> );
Query OK, 0 rows affected (0.03 sec)

（2）一对多关系

1.介绍

一对多关系是数据库中的一种关联关系，表示一个记录在一个表中可以对应多个记录，在另一个表中同样只对应一个记录。这是数据库中最常见的关联关系之一。
假设有部门和员工两个实体，一个部门可以有多个员工，但一个员工只属于一个部门。这就是一个典型的一对多关系。
- 部门表： 包含部门的基本信息，例如部门ID（主键）、部门名称等。
- 员工表： 包含员工的基本信息，例如员工ID（主键）、姓名、部门ID（外键）等。
一对多关系通过在多的一方中添加一个外键

2.示例

创建表

mysql> create table department(-> id int primary key auto_increment,-> name varchar(10)-> );
Query OK, 0 rows affected (0.03 sec)mysql> create table employee(-> id int primary key auto_increment,-> name varchar(6),-> department_id int,-> foreign key(department_id) references department(id)-> );
Query OK, 0 rows affected (0.03 sec)

（3）多对多关系

1.介绍

多对多关系是数据库中的一种关联关系，表示两个表之间存在多对多的对应关系。在这种关系中，一个记录在一个表中可以对应多个记录，在另一个表中同样可以对应多个记录。
典型的多对多关系需要通过**中间表（连接表）**来实现，这个中间表包含两个表的主键作为外键，用于建立两者之间的关联。
- 学生表： 包含学生的基本信息，例如学生ID（主键）、姓名等。
- 课程表： 包含课程的基本信息，例如课程ID（主键）、课程名称等。
- 选课表（中间表）： 包含学生ID和课程ID作为外键，用于记录学生选修的课程关系。

2.示例

创建表

mysql> create table student(-> id int primary key auto_increment,-> name varchar(6)-> );
Query OK, 0 rows affected (0.03 sec)mysql> create table course(-> id int primary key auto_increment,-> name varchar(10)-> );
Query OK, 0 rows affected (0.03 sec)mysql> create table student_course(-> stu_id int,-> c_id int,-> id int primary key auto_increment,-> foreign key (stu_id) references student(id),-> foreign key (c_id) references course(id)-> );
Query OK, 0 rows affected (0.07 sec)

【4】插入数据

（1）说明

先向没有外键的表插入数据
再向含有外键的表插入数据
- 外键不存在会报错

（2）示例

使用一对多的案例
创建部门信息

mysql> insert department (name) values("销售部"), ("开发部"), ("主管部");	--此时部门id有3个
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> select * from department;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 销售部    |
|  2 | 开发部    |
|  3 | 主管部    |
+----+-----------+

创建员工信息

mysql> insert employee (name, department_id) values('bruce', 4);	--插入没有的外键信息4，报错
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`))

mysql> insert employee (name, department_id) values ('bruce', 3), ('lily', 1), ('kan', 1);	--正常插入数据
Query OK, 3 rows affected (0.01 sec)mysql> select * from employee;
+----+-------+---------------+
| id | name  | department_id |
+----+-------+---------------+
|  2 | bruce |             3 |
|  3 | lily  |             1 |
|  4 | kan   |             1 |
+----+-------+---------------+

【八】级联更新和级联删除

【1】说明

在数据库中，级联更新（Cascade Update）和级联删除（Cascade Delete）是一种设置在外键约束上的操作。它们的作用是在父表的数据发生变化时，自动更新或删除相关联的子表中的数据。
添加级联更新和级联删除时，需要在外键约束后面添加相应的操作指令。
在删除父表中的数据时，使用 ON DELETE CASCADE 指令，表示级联删除，即同时删除关联的子表中的数据。
在更新父表中的数据时，使用 ON UPDATE CASCADE 指令，表示级联更新，即同时更新关联的子表中的数据。

【2】使用

（1）说明

删除外键约束的语法：

ALTER TABLE table_name DROP FOREIGN KEY constraint_name;

添加级联删除的外键约束的语法：

ALTER TABLE child_table ADD CONSTRAINT constraint_name FOREIGN KEY (foreign_key_column) REFERENCES parent_table(primary_key_column) ON DELETE CASCADE;

添加级联更新的外键约束的语法：

ALTER TABLE child_table ADD CONSTRAINT constraint_name FOREIGN KEY (foreign_key_column) REFERENCES parent_table(primary_key_column) ON UPDATE CASCADE;

（2）示例

还是使用一对多的案例
删除员工表外键
- 由于没有命名外键，需要先查询命名

mysql> show create table employee \G;	--查询外键名字
*************************** 1. row ***************************Table: employee
Create Table: CREATE TABLE `employee` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(6) DEFAULT NULL,`department_id` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `department_id` (`department_id`),CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)ERROR:
No query specifiedmysql> alter table employee drop foreign key employee_ibfk_1;	--删除外键
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

添加级联删除和更新外键

mysql> alter table employee add constraint employee_ibfk_1 foreign key (department_id) references department(id) on update cascade on delete cascade;
Query OK, 3 rows affected (0.06 sec)

修改部门id，查看员工表对应id是否修改
- 可以

mysql> update department-> set id=5-> where id=1;
Query OK, 1 row affected (0.02 sec)	
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from employee;
+----+-------+---------------+
| id | name  | department_id |
+----+-------+---------------+
|  2 | bruce |             3 |
|  3 | lily  |             5 |
|  4 | kan   |             5 |
+----+-------+---------------+

删除部门id，查看员工表对应id员工是否删除
- 可以

mysql> delete from department-> where id=3;
Query OK, 1 row affected (0.01 sec)mysql> select *from employee;
+----+------+---------------+
| id | name | department_id |
+----+------+---------------+
|  3 | lily |             5 |
|  4 | kan  |             5 |
+----+------+---------------+

员工表是否可以添加未知部门id
- 不行

mysql> insert employee (name, department_id) values('lulu', 0);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

【3】谨慎使用：

级联操作会直接影响相关联的表中的数据，因此在使用时需要谨慎。数据的完整性和一致性是数据库设计的重要考虑因素，因此在设置级联操作时需要明确了解其影响。
在修改约束条件时，建议删除原先的约束再重新添加新的约束条件，以确保操作的准确性。

这篇关于数据库之约束条件和级联的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

数据库之 约束条件和级联

【一】什么是约束条件

【二】非空约束

【1】作用

【2】示例

【三】唯一性约束

【1】作用

【2】示例

【四】组合使用

【1】作用

【2】示例

【五】查看约束条件

【1】表级约束

【2】参数说明

【3】查看指定表的表级约束

【五】表级约束和列级约束

【1】定义位置不同

【2】适用范围

【3】示例

【六】主键约束

【1】说明

【2】作用

【3】根据个数进行分类

（1）单一主键约束

（2）复合主键

【4】根据性质分类

（1）自然主键：

（2）业务主键：

【5】自增主键

（1）说明

（2）示例

【6】重置主键

（1）为什么要

（2）示例

【七】外键约束

【1】说明

【2】语法

【3】分类和创建示例

（1）一对一关系

1.介绍

2.示例

（2）一对多关系

1.介绍

2.示例

（3）多对多关系

1.介绍

2.示例

【4】插入数据

（1）说明

（2）示例

【八】级联更新和级联删除

【1】说明

【2】使用

（1）说明

（2）示例

【3】谨慎使用：

相关文章

数据库之约束条件和级联