【一】什么是约束条件

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

【二】非空约束

• 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 null mysql> 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】说明

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

【2】作用

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

【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: 0 mysql> 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.介绍

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

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: 0 mysql> 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 specified mysql> 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: 0 mysql> 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】谨慎使用：

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

今天的文章 数据库之四 约束条件和级联分享到此就结束了，感谢您的阅读。