数据库（MySQL）—— 约束

我们今天来看MySQL中一个非常重要的点——约束：

主键

主键（Primary Key）是数据库设计中的一个核心概念，它主要用于确保数据库表中的每一行数据具有唯一性。主键的概念可以总结如下：

• 唯一性: 主键的值在表中必须是唯一的，这意味着没有任何两行可以拥有相同的主键值。这是维护数据完整性的关键机制，防止数据重复。
• 非空性: 主键列不能包含NULL值。每个表只能有一个主键，但主键可以由一个字段或多个字段（组合主键）组成。
• 标识性: 主键作为每条记录的唯一标识，使得数据检索更为高效。通过主键，可以迅速定位到表中的特定记录。
• 索引: 主键自动成为所在表上的索引，这有助于提高查询速度，尤其是在进行等值匹配或排序操作时。
• 类型: 主键可以是任何数据类型，只要该类型支持唯一性和非空性约束，常见的如整数（常用于自动递增的ID）、字符串（如UUID）、日期等。
• 分类: 主键可分为自动编号主键和用户定义主键两大类。自动编号主键通常由数据库自动生成，如MySQL中的AUTO_INCREMENT；用户定义主键则是根据实际业务需求，由用户选择合适的字段或字段组合来定义。
• 作用: 主键在数据库操作中扮演重要角色，如关联表间数据（作为外键的引用目标）、确保数据一致性和准确性、优化查询性能等。

什么是约束

在MySQL中，约束（Constraint）是一种规则，用于限制存储在表中的数据，确保数据的正确性、有效性和完整性。约束可以应用于表的一个或多个列上，用来强制执行数据的一致性规则。以下是MySQL中常见的几种约束类型及其概念：

1. 非空约束（NOT NULL）：
   非空约束确保被约束的列不能包含NULL值。这意味着在插入新记录或更新记录时，必须为该列提供一个具体的值。
2. 唯一性约束（UNIQUE）：
   唯一性约束要求该列中的所有值都是唯一的，不允许重复。唯一性约束可以应用到单个列或多列组合上，以确保这些列的值组合起来是唯一的。需要注意的是，唯一性约束允许有多个NULL值，因为NULL不被视为重复值。
3. 主键约束（PRIMARY KEY）：
   主键是一个或多个列的组合，其值必须唯一且不能为空。主键约束为表提供了一种标识每一行的方式，确保表中每一行数据的唯一性。一个表中只能有一个主键。
4. 外键约束（FOREIGN KEY）：
   外键约束用于建立两个表之间的联系，它引用另一个表的主键或唯一键。外键约束确保引用的值必须存在于被引用表的指定列中，以此维护数据间的参照完整性。
5. 默认值约束（DEFAULT）：
   当在插入新记录时，如果没有为某列指定值，该列就会自动被赋予定义的默认值。
6. 检查约束（CHECK）：
   虽然标准的SQL支持CHECK约束用于限制列中的值满足特定条件，但MySQL直到8.0.16版本才开始支持这一功能。CHECK约束允许你定义一个布尔表达式，新插入或更新的行必须满足这个条件才能成功执行。

约束的使用有助于维护数据库的结构化和数据质量，减少数据冗余和错误，从而提高数据库的整体性能和可靠性。在创建表或修改表结构时，可以通过CREATE TABLE或ALTER TABLE语句来添加这些约束。

约束演示

上面我们介绍了数据库中常见的约束，以及约束涉及到的关键字，那这些约束我们到底如何在创建表、
修改表的时候来指定呢，接下来我们就通过一个案例，来演示一下。

我们根据下面的要求来创建表：

CREATE TABLE new_table(
    id int PRIMARY KEY  AUTO_INCREMENT COMMENT '工号',
    name varchar(10) NOT NULL UNIQUE COMMENT'姓名',
    age tinyint CHECK ( age > 0 AND age <=120) COMMENT '年龄',
    status char(1) DEFAULT '1' COMMENT '状态',
    gender char(1) COMMENT '性别'
)COMMENT '新员工表';

上面的id就是一个主键，大家可以拿这段代码来测试一下约束的效果：

insert into new_table(name,age,status,gender) values ('Tom1',19,'1','男'),
('Tom2',25,'0','男');
insert into new_table(name,age,status,gender) values ('Tom3',19,'1','男');
insert into new_table(name,age,status,gender) values (null,19,'1','男');
insert into new_table(name,age,status,gender) values ('Tom3',19,'1','男');
insert into new_table(name,age,status,gender) values ('Tom4',80,'1','男');
insert into new_table(name,age,status,gender) values ('Tom5',-1,'1','男');
insert into new_table(name,age,status,gender) values ('Tom5',121,'1','男');
insert into new_table(name,age,gender) values ('Tom5',120,'男');

外键约束

外键

外键（Foreign Key）是关系型数据库中用于建立和维护表与表之间关联关系的一个概念。其主要特点和作用概括如下：

• 关联性: 外键是一个表（被称为从表或子表）中的一个或多个字段，其值来源于另一个表（被称为主表或父表）的主键或唯一键。这样的设计建立了两个表之间的联系，反映了现实世界中实体间的关联关系。
• 引用完整性: 外键约束是数据库完整性约束的一种，它确保了引用的合法性，即从表中外键的值必须存在于主表的主键或唯一键中。这有助于维持数据的一致性，避免出现无效的关联数据。
• 一对多或多对一: 外键最常见的应用是在实现一对多或多对一的关系模型中，其中从表的记录可以与主表的一个记录对应，体现了现实世界中如订单与客户、学生与班级等关系。
• 可选性: 在某些情况下，外键可以接受NULL值，这意味着从表中的记录不必非要与主表中的某条记录关联。但这也取决于数据库设计者是否设置了外键约束的严格性。
• 级联操作: 数据库管理系统通常允许定义外键约束时设置级联操作，如级联更新或级联删除。这意味着当主表中的相关记录被修改或删除时，从表中的关联记录也会自动跟随修改或删除，以保持数据的一致性。
• 性能考量: 虽然外键能够增强数据的逻辑完整性，但它们也可能影响数据库的写入性能，特别是在需要频繁进行跨表验证的情况下。因此，在设计数据库时需要权衡数据完整性与性能的需求。

简单来说，外键就是用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

我们来看一个例子：

左侧的emp表是员工表，里面存储员工的基本信息，包含员工的ID、姓名、年龄、职位、薪资、入职日期、上级主管ID、部门ID，在员工的信息中存储的是部门的ID dept_id，而这个部门的ID是关联的部门表dept的主键id，那emp表的dept_id就是外键,关联的是另一张表的主键。

我们先准备数据：

create table dept(
    id int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
)comment '部门表';

INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,
'销售部'), (5, '总经办');

create table emp(
    id int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '姓名',
    age int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id int comment '部门ID'
)comment '员工表';

INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),(2, '张无忌', 20,
'项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),(4, '韦一笑', 48, '开
发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程
序员鼓励师',6600, '2004-10-12', 2,1);

接下来，我们可以做一个测试，删除id为1的部门信息。
结果，我们看到删除成功，而删除成功之后，部门表不存在id为1的部门，而在emp表中还有很多的员工，关联的为id为1的部门，此时就出现了数据的不完整性。 而要想解决这个问题就得通过数据库的外键约束。

添加外键

CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);

假设我们有两个表，一个是Employees（员工表），另一个是Departments（部门表）。在这个场景中，每个员工属于一个部门，我们可以这样设计这两个表，并在Employees表中添加一个外键来维护这种关系。

首先，另外举一个例子，我们创建的Departments表可能看起来像这样：

CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50) NOT NULL
);

这里，DeptID是主键，标识每个部门的唯一编号。

接着，我们创建Employees表，并在其上定义一个外键指向Departments表的DeptID：

CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(50) NOT NULL,
    DeptID INT,
    CONSTRAINT FK_EmployeeDepartment FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

在这个例子中：

• EmpID是Employees表的主键。
• DeptID是Employees表中的一个列，用于存储员工所属的部门ID。
• CONSTRAINT FK_EmployeeDepartment是外键约束的名称，它定义了一个外键关系，其中：
• (DeptID)指出Employees表中外键所在的列。
• REFERENCES Departments(DeptID)指明了这个外键关联到Departments表的DeptID列。

除了在创建表的时候添加外键，我们也可以在表创建完之后添加外键：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名)
REFERENCES 主表 (主表列名) ;

比如我们可以为emp表的dept_id字段添加外键约束,关联dept表的主键id：

ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id) REFERENCES dept(id);

这时候如果我们再从dept删除id 为 1的字段：
这个时候，因为dept_id有了外键约束，和dept的id产生了联系，所以不准让我们删了。

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

删除/更新行为

添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行
为有以下几种:

由于NO ACTION 是默认行为，我们前面语法演示的时候，已经测试过了，就不再演示了，这里我们再演示其他的两种行为：CASCADE、SET NULL。

CASCADE

-- CASCADE
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id)
REFERENCES dept(id) ON UPDATE CASCADE;

-- 修改父表id为1的记录，将id修改为6
UPDATE dept SET id = 6 where id = 1;

删除父表id为6的记录，在这之前，我们要删除原来的外键，然后再执行下面的代码：

-- CASCADE
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id)
REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE;

SET NULL

在进行测试之前，我们先需要删除上面建立的外键 fk_emp_dept_id。然后再通过数据脚本，将emp、dept表的数据恢复了。

-- SET NULL
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id)
REFERENCES dept(id) ON UPDATE SET NULL ON DELETE SET NULL;

接下来，我们删除id为1的数据，看看会发生什么样的现象：
我们发现父表的记录是可以正常的删除的，父表的数据删除之后，再打开子表 emp，我们发现子表emp的dept_id字段，原来dept_id为1的数据，现在都被置为NULL了。