Appearance

MySQL教程 - 11 约束 ​

在 SQL 中，约束（Constraints）是对数据库表中的字段添加的规则，限制保存的数据，从而保证数据的完整性、一致性和准确性。如果不满足列设置的约束条件，那么在插入数据的时候将会失败。

约束可以在表创建时添加，也可以在表创建后通过修改表结构来添加。

常用的约束有以下几种：

  • 主键约束

  • 非空约束

  • 默认约束

  • 唯一约束

  • 检查约束

  • 外键约束

下面分别介绍一下。

11.1 主键约束 ​

主键约束使用 PRIMARY KEY 设置，用来设置表的主键，主键字段必须非空，且数据是唯一的。

之前在建表的时候，已经使用过了，如下：

sql
-- 创建员工表tb_employee
CREATE TABLE tb_employee (
    id INT AUTO_INCREMENT PRIMARY KEY,    -- 设置主键
  	emp_number VARCHAR(16),
    name VARCHAR(100),
    age INT,
    email VARCHAR(100),
    salary DECIMAL(10, 2),
  	entry_time datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 或者
CREATE TABLE tb_employee (
    id INT,
    emp_number VARCHAR(16),
    name VARCHAR(100),
    age INT,
    email VARCHAR(100),
    salary DECIMAL(10, 2),
  	entry_time datetime,
    PRIMARY KEY (id)  -- 设置主键为id
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • 通过 PRIMARY KEY 设置 id 字段为主键，设置为之间，默认值就是为空且唯一；
  • AUTO_INCREMENT 设置主键策略为自动增长，随着插入的数据递增；
  • 设置了某一列为主键，那么该列就是非空且唯一的。

11.2 非空约束 ​

非空约束通过 NOT NULL 来设置，非空约束用来确保该字段（列）不能存储空值。

举个栗子：

sql
CREATE TABLE tb_employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    emp_number VARCHAR(16),
    name VARCHAR(100) NOT NULL,       -- 设置为非空
    age INT,
    email VARCHAR(100),
    salary DECIMAL(10, 2),
    entry_time datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • 上面将 name 字段设置为 NOT NULL ，那么在插入数据的时候，必须给 name 设置值。
  • '' 空字符串算是有值的，NULL 是空值。

建表后，非空约束设置如下：

sql
# 建表后，设置字段非空
ALTER TABLE tb_employee ALTER COLUMN name VARCHAR(100) NOT NULL;

# 建表后，删除非空约束
ALTER TABLE tb_employee ALTER COLUMN name VARCHAR(100) NULL;

11.3 唯一约束 ​

唯一约束使用 UNIQUE 来设置，用来确保表中的所有的数据，唯一约束的字段的值都是唯一、不重复的。

1 单列唯一约束 ​

在创建唯一约束的时候，可以为单个列创建唯一约束，那么表中所有数据，该列的值是唯一不重复的。

举个栗子：

sql
CREATE TABLE tb_employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    emp_number VARCHAR(16) UNIQUE,        -- 设置为唯一约束
    name VARCHAR(100) NOT NULL,        
    age INT,
    email VARCHAR(100),
    salary DECIMAL(10, 2),
    entry_time datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • 上面给 emp_number 字段设置了 UNIQUE ，表示所有员工的 工号 不能重复。
  • UNIQUE 表示唯一不重复，但是上面并没有给 emp_number 设置为 NOT NULL ，UNIQUE 唯一约束允许列中有空值，但空值之间不比较唯一性，所以可以存在多个员工的工号为 NULL 。
  • 可以给 emp_number 设置为 NOT NULL UNIQUE，这样就非空且唯一了，所以多个约束可以同时使用。

建表后，唯一约束设置如下：

sql
-- 建表后，设置唯一约束
ALTER TABLE tb_employee ADD CONSTRAINT emp_number_unique_constraint UNIQUE (emp_number);

-- 建表后，删除唯一约束
ALTER TABLE tb_employee DROP CONSTRAINT emp_number_unique_constraint;
  • 设置唯一约束的时候，需要给唯一约束起个名字，上面叫 emp_number_unique_constraint （自定义），表中可能有多个唯一约束，不重复即可。
  • 删除唯一约束的时候，通过唯一约束的名字删除。

我在建表的时候，也没指定唯一约束的名字，后面如何通过唯一约束的名字删除约束呢？

可以通过如下语句查询一个表中唯一约束的名字：

sql
-- 查询 tb_employee 表的唯一约束
SELECT *
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'tb_employee' AND CONSTRAINT_TYPE = 'UNIQUE';

可以在建表的时候，指定唯一约束的名字：

sql
CREATE TABLE tb_employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    emp_number VARCHAR(16) UNIQUE(emp_number),        -- 设置唯一约束，并设置唯一约束的名称也是emp_number
    name VARCHAR(100) NOT NULL,        
    age INT,
    email VARCHAR(100),
    salary DECIMAL(10, 2),
    entry_time DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • 上面设置唯一约束，并设置了唯一约束的名字。
  • 其实当没有显式指定唯一约束名称时，MySQL 会使用列名作为唯一约束的名称。

2 多列唯一约束 ​

也可以为多个列创建唯一约束，那么表中所有的数据，多个列的数据是唯一不重复的。

举个栗子：

sql
CREATE TABLE tb_employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    emp_number VARCHAR(16),
    name VARCHAR(100) NOT NULL,
    age INT,
    email VARCHAR(100),
    salary DECIMAL(10, 2),
    entry_time DATETIME,
    -- 定义多列唯一约束
    CONSTRAINT unique_emp_email UNIQUE (emp_number, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • 上面定义了多列唯一索引，那么可能存在 emp_number 值相同的列，也可以存在 email 相同的列，但是不会存在 emp_number 和 email 同时相同的数据；
  • unique_emp_email 是指定了唯一索引的名称，可以通过名称删除该唯一索引。

建表后，唯一约束设置如下：

sql
-- 建表后，设置多列唯一约束
ALTER TABLE tb_employee ADD CONSTRAINT unique_emp_email UNIQUE (emp_number, email);

-- 建表后，删除多列唯一约束
ALTER TABLE tb_employee DROP CONSTRAINT unique_emp_email;

11.4 默认约束 ​

默认约束使用 DEFAULT 来设置，为字段（列）指定默认值。如果在插入记录时没有指定该列的值，则使用该默认值。

举个栗子：

sql
CREATE TABLE tb_employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    emp_number VARCHAR(16) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    age INT DEFAULT 18,               -- 设置默认值为18
    email VARCHAR(100) DEFAULT NULL,  -- 设置默认值为NULL
    salary DECIMAL(10, 2),
    entry_time datetime DEFAULT CURRENT_TIMESTAMP     -- 设置默认值为当前时间
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • 在上面使用 DEFAULT 18 设置 age 的默认值为 18，在插入数据的时候，如果不指定 age 的值，那么 age 的值就是18 ；
  • 使用 DEFAULT NULL 设置 email 的默认值为 NULL， DEFAULT NULL 可以写成 NULL，或者整个省略，因为字段不设置默认值，就是默认为 NULL ；
  • 使用 DEFAULT CURRENT_TIMESTAMP 设置 entry_time 的默认值为 插入数据时候的时间 ；

建表后，设置默认约束：

sql
-- 设置默认约束
ALTER TABLE tb_employee MODIFY COLUMN age INT DEFAULT 20;

-- 删除默认约束，其实就是将默认值设置为NULL
ALTER TABLE tb_employee MODIFY COLUMN age INT DEFAULT NULL;

11.5 检查约束 ​

检查约束使用 CHECK 来设置，用来限制列中的值，使其符合指定条件。只有在 MYSQL 版本 8.0.16 之后才支持。

举个栗子：

sql
CREATE TABLE tb_employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    emp_number VARCHAR(16) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    age INT DEFAULT 18 CHECK (age >= 0 AND age <= 120),     -- 设置检查约束          
    email VARCHAR(100) DEFAULT NULL,  
    salary DECIMAL(10, 2) CHECK (salary > 0),      -- 设置检查约束
    entry_time datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • 上面通过 CHECK (age >= 0 AND age <= 120) 限制 age 的值在 0 ~ 120 之间；
  • 通过 CHECK (salary > 0) 设置 salary 的值必须大于 0 ；

再举个栗子：

如果想限制一个值是1、3、5，那么可以设置如下

sql
CREATE TABLE tb_employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    emp_number VARCHAR(16) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    age INT DEFAULT 18 CHECK (age >= 0 AND age <= 120),
    email VARCHAR(100) DEFAULT NULL,  
    salary DECIMAL(10, 2) CHECK (salary > 0), 
    entry_time datetime DEFAULT CURRENT_TIMESTAMP,
    status INT CHECK (status IN (1, 3, 5)),      -- 设置检查约束，值只能是1,3,5
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • 上面通过 CHECK (status IN (1, 3, 5)) 设置 status 的值只能是 1、3、5 。

设置了检查约束，如果插入时候设置的值不满足约束条件，会报如下的错误：

Error synchronizing data with database
Reason:
SQL 错误 [3819] [HY000]: Check constraint 'tb_employ_chk_3' is violated.
  • Check constraint 就表示检查约束报错。

建表后，检查约束设置如下：

sql
-- 建表后，设置检查约束
ALTER TABLE tb_employee ADD CONSTRAINT check_age CHECK (age >= 18);

-- 建表后，删除约束
ALTER TABLE tb_employee DROP CONSTRAINT check_age;
  • check_age 是设置检查约束的名称，然后可以根据检查约束的名称删除检查约束；
  • 如果要修改检查约束，需要先删除，然后再添加。

建表的时候，没指定唯一约束的名字，后面如何通过唯一约束的名字删除约束呢？

可以通过如下语句查询一个表中唯一约束的名字：

sql
-- 查询 tb_employee 表的检查约束
SELECT *
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'tb_employee' AND CONSTRAINT_TYPE = 'CHECK';

执行结果：

通过名称不太好区分，所以建表的时候，可以指定检查约束的名称：

sql
-- 或者
CREATE TABLE tb_employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    emp_number VARCHAR(16) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    age INT DEFAULT 18,
    email VARCHAR(100) DEFAULT NULL,  
    salary DECIMAL(10, 2),
    entry_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_age CHECK (age >= 0 AND age <= 120),  -- 指定检查约束的名称
    CONSTRAINT chk_salary CHECK (salary > 0)            -- 指定检查约束的名称
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • 上面指定了检查约束的名称，这样就可以通过名称删除约束了。

11.6 外键约束 ​

外键约束涉及到两张表，用来让两张表之间的数据建立联系，并保证表之间数据的一致性和完整性。

什么意思呢？

举个栗子，现在有两张表，部门表和员工表，在员工表中有部门表的 id，这样就知道这个员工是哪个部门的了，如下图：

现在这两张表之间存在关联关系，在员工表中储存部门表的主键ID，员工表中的 部门id 称之为外键，此时我们一般称部门表为父表或主表，员工表为子表或从表。


虽然两张表的数据存在关联关系，但是此时修改员工表的 部门id 字段，将其值修改为一个不存在的部门ID，也是可以的；同样，删除部门表的一条数据，哪怕这个部门有关联的员工，部门 ID 正在被员工表的数据使用，也是可以删除的。这样就存在了关联表之间数据的不一致和不完整。

而外键约束就是保证关联表之间的一致性和完整性。


首先使用 SQL 创建这两张表，并创建一些数据：

sql
-- 删除表，重新创建
DROP TABLE IF EXISTS tb_department;

-- 创建部门表
CREATE TABLE tb_department (
    id INT AUTO_INCREMENT PRIMARY KEY,         -- 部门ID，自增主键
    name VARCHAR(64)                           -- 部门名称，使用 VARCHAR 类型，长度可变
);

-- 插入部门表数据
INSERT INTO tb_department VALUES
(1, '市场部'), 
(2, '技术部'),
(3, '财务部');

-- 删除表，重新创建
DROP TABLE IF EXISTS tb_employee;

-- 创建员工表
CREATE TABLE tb_employee (
    id INT AUTO_INCREMENT PRIMARY KEY,         -- 员工ID，自增主键
    dept_id INT,         											 -- 部门ID
    emp_number VARCHAR(16),
    name VARCHAR(64),
    age TINYINT UNSIGNED,
    gender CHAR(3),
    salary DECIMAL(10, 2),
    entry_time DATE
);

-- 插入员工表数据
INSERT INTO tb_employee VALUES
(1, 1, '1', '罗辑', 42, '男', 12000.50, '2022-03-15'),
(2, 2, '2', '叶文杰', 68, '女', 15000.00, '2022-05-20'), 
(3, 2, '3', '程心', 25, '女', 28000.75, '2023-01-10'),
(4, 3, '4', '史强', 48, '男', 8000, '2022-08-22');

上面的两张表有了数据，而且这些数据逻辑上是存在关联关系的。但是可以随意删除部门数据，或者将员工表的 dept_id 字段的值设置为无效的部门 ID 也可以。

1 创建外键约束 ​

外键约束可以在创建表的时候创建，是在从表创建的，主表不用变化。

举个栗子：

sql
CREATE TABLE tb_employee (
    id INT AUTO_INCREMENT PRIMARY KEY,         -- 员工ID，自增主键
    dept_id INT,         											 -- 部门ID
    emp_number VARCHAR(16),
    name VARCHAR(64),
    age TINYINT UNSIGNED,
    gender CHAR(3),
    salary DECIMAL(10, 2),
    entry_time DATE,
    CONSTRAINT fk_department FOREIGN KEY(dept_id) REFERENCES tb_department(id)
);
  • foreign_key_deptid 表示定义的是外键的名称，自定义。

但是在上面已经创建了表，而且已经存在了数据，那么可以使用如下方式设置外键约束：

sql
-- 创建外键约束
ALTER TABLE tb_employee ADD CONSTRAINT foreign_key_deptid FOREIGN KEY(dept_id) REFERENCES tb_department(id);

-- 删除外键约束，根据外面名称删除
ALTER TABLE tb_employee DROP FOREIGN KEY fk_department;

-- 查询指定表的外键信息
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'tb_employee' AND CONSTRAINT_TYPE = 'FOREIGN KEY';

如果已经创建了表，然后再添加约束，如果从表中已经存在了数据，那么要保证从表外键字段，例如 dept_id 中的值都是有效的，都是在 tb_department 中有对应数据的，或者外键的值为 NULL，否则添加外键约束会报错。


当添加了外键约束后，如果主表中的数据被子表中的数据关联，那么主表中的数据默认是无法删除的。除非将子表中的关联数据删掉或者将外键值设置为 NULL，那么才可以删掉主表中的对应的数据。

就像上面的表，此时是无法删除部门的数据的，因为每个部门都有员工，除非将员工的 部门id 设置为 NULL，或者先删除部门下面的员工数据，才能删除对应的部门数据。同样从表中的外键的值必须在主表中真实存在的有效数据，或者设置值为 NULL 也可以，但是不能设置不存在的 部门id。

2 级联操作 ​

在 MySQL 中，外键约束可以通过 级联操作（Cascading Actions） 来定义数据完整性规则，也就是说，可以通过规则来设置当父表的数据发生变化（删除或更新）时，子表的数据应该如何处理。

前面设置外键约束的时候并没有设置级联操作的规则，所以父表数据和子表数据存在关联的时候，是无法删除父表的数据的。


举个栗子：

sql
CREATE TABLE tb_employee (
    id INT AUTO_INCREMENT PRIMARY KEY,         -- 员工ID，自增主键
    dept_id INT,         											 -- 部门ID
    emp_number VARCHAR(16),
    name VARCHAR(64),
    age TINYINT UNSIGNED,
    gender CHAR(3),
    salary DECIMAL(10, 2),
    entry_time DATE,
    CONSTRAINT fk_department FOREIGN KEY(dept_id) REFERENCES tb_department(id) ON DELETE CASCADE ON UPDATE SET NULL  -- 建表的时候设置外键和级联操作规则
);

-- 建表后设置外键和级联操作规则
ALTER TABLE tb_employee ADD CONSTRAINT foreign_key_deptid FOREIGN KEY(dept_id) REFERENCES tb_department(id) ON DELETE CASCADE ON UPDATE SET NULL;
  • 上面分别在建表时和建表后设置外键和级联操作的规则，其中 ON DELETE CASCADE 设置了在删除主表数据时的级联操作规则为 CASCADE ，CASCADE 表示父表的数据被删除时，会删除子表关联的数据。
  • ON UPDATE SET NULL 设置主表主键数据发生更新的时候，会将子表的外键字段设置为 NULL。
  • 通过上面设置级联操作的规则，删除了部门表的数据，属于该部门的员工数据会被删除；修改部门id，员工表的外键 dept_id 会被设置为 NULL。

上面设置了在删除和更新主表数据时候的级联操作是不同的，也可以设置为相同：

sql
ON DELETE CASCADE ON UPDATE CASCADE;

级联操作主要包括以下几种类型，可以根据需要设置：

级联操作描述
CASCADE当父表的行被删除或更新时，子表中的对应行会自动删除或更新，保持一致性。
也就是部门的ID被修改了，员工表的外键（dept_id）也会跟着变化，如果删除部门数据，会将部门下的员工数据也删除了。
SET NULL当父表的行被删除或更新时，子表中的对应外键字段会被设置为 NULL。子表字段必须允许 NULL 值。
修改或删除部门数据后，员工数据的外键（dept_id）会被设置为NULL。
SET DEFAULT当父表的行被删除或更新时，子表中的对应外键字段会被设置为默认值。MySQL 当前不支持此操作。
RESTRICT拒绝删除或更新父表中的行，如果它们在子表中有对应的行（默认行为之一）。
NO ACTION类似于 RESTRICT，即如果子表中存在引用，不允许删除或更新父表记录，但执行检查的时间点可能略有不同（标准 SQL 语义）。

RESTRICT和NO ACTION的不同点：

特性RESTRICTNO ACTION
检查时机在执行删除或更新语句时立即检查外键约束。在事务提交时才会检查外键约束（符合标准 SQL 语义）。
SQL 标准支持是 MySQL 扩展的行为，不是标准 SQL 的一部分。是标准 SQL 定义的行为，MySQL 兼容该行为。
约束检查的灵活性无论是否处于事务中，约束检查立即生效。如果处于事务中，可以在后续语句中修正违反约束的操作。

关于外键约束的使用实践：

外键约束适合规模较小、复杂度较低的系统，对复杂和大规模系统不推荐，对性能会有影响，另外在数据维护的时候，降低了操作的自由度，操作数据会有外键约束。根据《阿里巴巴开发手册》的规范，不推荐在开发中直接使用外键约束，而是通过其他手段确保数据的完整性，例如在业务逻辑的代码层面进行数据完整性的控制。

3 自连接外键约束 ​

有些时候，一些数据结构是树形结构的，存在父子关系，例如一个系统的菜单，会有父子菜单；部门会有父子部门。这种存在层级关系的数据，也是存在一张表中的。

以菜单为例，那么需要在表中存储父菜单的 ID，数据可能如下：

idnameparent_id
1首页NULL
2用户管理1
3系统设置1
4添加用户2
5删除用户2
6权限管理3

上面的表中，parent_id 就是父菜单的 id，也就是对应这个表的 id 的值，parent_id 值为NULL 表示顶级菜单。

使用 SQL 创建这张表，并添加数据：

sql
CREATE TABLE tb_menu (
    id INT PRIMARY KEY,         											-- 菜单唯一标识
    name VARCHAR(50) NOT NULL,  											-- 菜单名称
    parent_id INT,                   									-- 父菜单的 id，NULL 表示顶级菜单
    FOREIGN KEY (parent_id) REFERENCES tb_menu(id)    -- 还可以添加外键约束，指向父菜单
);

INSERT INTO tb_menu (id, name, parent_id) VALUES
(1, '首页', NULL), 
(2, '用户管理', 1),
(3, '系统设置', 1),
(4, '添加用户', 2),
(5, '删除用户', 2),
(6, '权限管理', 3);

有了外键约束，删除数据的时候，需要删除子数据，才能删除父数据。

内容未完......
查看完整内容 →