如果有人问你 SQL 约束，记得把这篇文章给他

完整性约束

为了维护数据的完整性和一致性，或者为了实现业务需求，SQL 标准定义了完整性约束。以下是常用的 6 种完整性约束：

• 非空约束（NOT NULL），用于确保字段不会出现空值。例如学生信息表中，学生的姓名、出生日期、性别等一定要有数据。
• 唯一约束（UNIQUE），用于确保字段中的值不会重复。例如每个学生的身份证、手机号等需要唯一。
• 主键约束（Primary Key），用于唯一标识表中的每一行数据。例如学生信息表中，学号通常作为主键。主键字段不能为空并且唯一，每个表可以有且只能有一个主键。
• 外键约束（Foreign Key），用于建立两个表之间的参照完整性。例如学生属于班级，学生信息表中的班级字段是一个外键，引用了班级表的主键。对于外键引用，被引用的数据必须存在；学生不可能属于一个不存在的班级。
• 检查约束（CHECK）可以定义更多的业务规则。例如，性别的取值只能为“男”或“女”，用户名必须大写等；
• 默认值（DEFAULT）用于为字段提供默认的数据。例如，玩家注册时的级别默认为一级。

其中，主键代表的是实体完整性；外键定义的是参照完整性；其他属于用户定义的完整性（也称为域完整性）。

SQL 支持在创建表的时候定义约束，或者为已有的表增加新的约束：

CREATE TABLE table_name
(
  column_1 data_type column_constraint,
  column_2 data_type,
  ...,
  table_constraint
);


ALTER TABLE table_name ADD CONSTRAINT constraint_desc;

其中，column_constraint 位于字段的定义之后，被称为列级约束；table_constraint 位于所有字段之后，被称为表级约束。

各种主流数据库对于 SQL 完整性约束的支持如下：

* MySQL 8.0 开始支持检查约束，InnoDB 和 NDB 存储引擎支持外键约束。

📝当我们定义了约束之后，数据库管理系统会在 INSERT、UPDATE、DELETE 等数据修改操作时，或者提交事务时检查数据是否满足完整性约束条件；如果发现用户的操作违反了完整性约束，数据库可能会拒绝执行该操作，或者级联执行其他的修改操作。

虽然以上数据库都提供了 6 种完整性约束的支持，但是在实现和语法上存在一些微小的差异，接下来我们具体讨论一下各种约束。

非空约束

定义了 NOT NULL 约束的字段数据不能为空。例如：

CREATE TABLE t_nn(
  id INT NOT NULL,
  c1 VARCHAR(10)
);

-- Oracle、MySQL
ALTER TABLE t_nn MODIFY c1 VARCHAR(10) NOT NULL;

-- SQL Server
ALTER TABLE t_nn ALTER COLUMN c1 VARCHAR(10) NOT NULL;

-- PostgreSQL
ALTER TABLE t_nn ALTER COLUMN c1 SET NOT NULL;

-- SQLite 不支持修改字段的约束

其中，id 在创建表时指定了非空约束；c1 字段通过 ALTER TABLE 语句增加了非空约束，注意不同数据库的语法实现。接下来我们插入一些数据：

insert into t_nn(id, c1) values (1, 'sql');
insert into t_nn(id, c1) values (2, null);
SQL Error [1048] [23000]: Column 'c1' cannot be null

数据库中的空值（NULL）是一个特殊的值，通常用于表示缺失值或者不适用的值。空值与数字 0 并不相同；空值与空字符串（''）也不相同，但是 Oracle 中的空值与空字符串等价。因此，以下语句在 Oracle 中执行出错，但在其他数据库中执行成功：

-- Oracle 空值与空字符串
insert into t_nn(id, c1) values (2, '');
SQL Error [1400] [23000]: ORA-01400: cannot insert NULL into ("TONY"."T_NN"."C1")

⚠️处理空值时需要特别小心，具体可以参考这篇文章。

唯一约束

唯一约束字段中的值不能重复，但是可以存在多个空值。例如：

CREATE TABLE t_unique(
  id INT UNIQUE,
  c1 INT,
  c2 INT,
  CONSTRAINT uk_t_unique UNIQUE (c1, c2)
);

其中，id 在创建在创建表时指定了字段级别的唯一约束；c1 和 c2 字段指定了表级的唯一约束。在我们指定唯一约束时，数据库会自动创建一个唯一索引来实现该功能。接下来我们插入一些重复的数据：

INSERT INTO t_unique(id, c1, c2) VALUES (1, 1, 1);
INSERT INTO t_unique(id, c1, c2) VALUES (NULL, 2, 2);
INSERT INTO t_unique(id, c1, c2) VALUES (NULL, 3, 3);

-- SQL Server 唯一约束中只允许一个 NULL 值
SQL Error [2627] [23000]: Violation of UNIQUE KEY constraint 'UQ__t_unique__3213E83E85135D71'. Cannot insert duplicate key in object 'dbo.t_unique'. The duplicate key value is (<NULL>).

以上语句为 id 字段插入了 2 个空值；SQL Server 唯一约束中只允许一个 NULL 值（也就是 NULL 和 NULL 相同），提示错误；其他数据库可以执行成功。

我们再看一下多字段的复合唯一约束中部分字段数据为空的情况：

INSERT INTO t_unique(id, c1, c2) VALUES (2, 1, NULL);
INSERT INTO t_unique(id, c1, c2) VALUES (3, 1, NULL);

-- Oracle
SQL Error [1] [23000]: ORA-00001: unique constraint (TONY.UK_T_UNIQUE) violated

-- SQL Server
SQL Error [2627] [23000]: Violation of UNIQUE KEY constraint 'uk_t_unique'. Cannot insert duplicate key in object 'dbo.t_unique'. The duplicate key value is (1, <NULL>).

以上语句为 c2 字段插入了 2 个空值；Oracle 和 SQL Server 唯一约束中如果某个字段不为空，其他字段只允许一个 NULL 值（也就是 NULL 和 NULL 相同）；其他数据库可以执行成功。

还有一种情况，就是复合唯一约束中的所有字段都为空：

INSERT INTO t_unique(id, c1, c2) VALUES (4, NULL, NULL);
INSERT INTO t_unique(id, c1, c2) VALUES (5, NULL, NULL);

-- SQL Server
SQL Error [2627] [23000]: Violation of UNIQUE KEY constraint 'uk_t_unique'. Cannot insert duplicate key in object 'dbo.t_unique'. The duplicate key value is (<NULL>, <NULL>).

只有 SQL Server 执行出错，也就是说：

• SQL Server 会索引 NULL 值，所以唯一索引只能有一个 NULL 值。
• Oracle 索引中如果部分字段为空，会索引其他不为空的字段；如果所有字段都为空，不会建立索引。😕
• MySQL、PostgreSQL、SQLite 不会索引 NULL 值，所以唯一索引可以有多个值。

我们也可在创建表之后增加唯一约束或者唯一索引：

CREATE TABLE t_unique(
  id INT UNIQUE,
  c1 INT,
  c2 INT
);

-- Oracle、MySQL、SQL Server、PostgreSQL
ALTER TABLE t_unique ADD CONSTRAINT uk_t_unique UNIQUE (c1, c2);

-- 所有数据库，包括 SQLite
CREATE UNIQUE INDEX uk_t_unique ON t_unique (c1, c2);

SQLite 不支持创建表之后再增加约束，可以使用唯一索引替代。

唯一索引等于唯一约束吗❓

主键约束

主键（PRIMARY KEY）是表中用于唯一地标识每行记录的字段，构成主键的所有字段都不能为空（NOT NULL）并且唯一（UNIQUE）。一个表只能有一个主键。主键可能是一个或多个字段，多个字段的主键被称为复合主键。

如果主键由单个字段构成，可以定义为列级约束或者表级约束。例如：

CREATE TABLE t_primary1(id INT NOT NULL PRIMARY KEY,
                        c1 INT);
                        
CREATE TABLE t_primary2(id INT NOT NULL,
                        c1 INT,
                        CONSTRAINT pk2 PRIMARY KEY(id));

t_primary1 的 id 字段定义了主键约束，使用系统生成的主键名；t_primary2 的 id 字段定义了主键约束，使用自定义的主键名 pk2。如果是多列主键，只能在表级进行定义：

CREATE TABLE t_primary3(id INT NOT NULL,
                        c1 INT NOT NULL,
                        CONSTRAINT pk3 PRIMARY KEY(id, c1));

⚠️MySQL 中的主键约束忽略用户指定的名称，使用固定的名称 PRIMARY。

另外，我们也可以使用 ALTER TABLE 语句为已有的表增加一个主键约束：

CREATE TABLE t_primary4(id INT NOT NULL,
                        c1 INT);
ALTER TABLE t_primary4 ADD CONSTRAINT pk4 PRIMARY KEY (id);

⚠️SQLite 不支持这种增加主键约束的方法。

数据库通常会自动为主键字段创建一个唯一索引，用于确保主键字段值的唯一性。因此，下面的第二个 INSERT 语句违反了主键约束：

insert into t_primary1(id, c1) values (1, 100);
insert into t_primary1(id, c1) values (1, 200);
SQL 错误 [1062] [23000]: Duplicate entry '1' for key 't_primary1.PRIMARY'

外键约束

外键约束用于建立两个关系表之间的参照引用，通常是一个表中的字段引用另一个表中的主键字段。例如，员工属于部门；因此员工表中的部门字段可以创建外键，引用部门表中的主键。例如：

CREATE TABLE dept
    ( department_id    INTEGER NOT NULL PRIMARY KEY
    , department_name  CHARACTER VARYING(30) NOT NULL
    ) ;

CREATE TABLE emp
    ( employee_id    INTEGER NOT NULL PRIMARY KEY
    , first_name     CHARACTER VARYING(20)
    , last_name      CHARACTER VARYING(25) NOT NULL
    , salary         NUMERIC(8,2)
    , manager_id     INTEGER
    , department_id  INTEGER
	, CONSTRAINT     fk_emp_dept
                     FOREIGN KEY (department_id)
                     REFERENCES dept(department_id)
    ) ;

外键约束中被引用的表称为父表（dept），外键所在的表称为子表（emp）。我们再为 emp 表增加一个外键：

ALTER TABLE emp
ADD CONSTRAINT fk_emp_manager
               FOREIGN KEY (manager_id)
               REFERENCES emp(employee_id)
    ;

⚠️SQLite 不支持这种增加主键约束的方法。

外键约束 fk_emp_manager 引用了 emp 表自身，用于维护员工和经理之间的联系。如果 emp 中已经存在数据，必须满足该外键约束的条件，否则无法添加该约束。

外键约束可以维护数据的参照完整性，员工不会属于一个不存在的部门，例如：

INSERT INTO dept VALUES (1, '办公室');

-- SQLite
-- PRAGMA foreign_keys = ON;
INSERT INTO emp VALUES (100, '大', '刘', 50000, NULL, 1);
INSERT INTO emp VALUES (101, '三', '张', 30000, 1, 2);
SQL Error [2291] [23000]: ORA-02291: integrity constraint (TONY.FK_EMP_DEPT) violated - parent key not found

我们首先创建了一个部门，然后插入两个员工的数据；由于第二个员工的部门（department_id = 2）不存在，违反了外键约束，插入失败。

⚠️如果是 SQLite，需要在编译时启用了外键约束支持，并且需要执行PRAGMA foreign_keys = ON;命令，具体信息可以参考官方文档。

此时，如果我们删除 dept 表中的记录：

DELETE
  FROM dept
 WHERE department_id = 1;
SQL Error [2292] [23000]: ORA-02292: integrity constraint (TONY.FK_EMP_DEPT) violated - child record found

由于 emp 表中存在部门编号为 1 的员工，删除该部门的信息会破坏数据的完整性，因此执行失败。如果我们将 dept 表中的部门编号从 1 修改为其他编号，同样会违法外键约束。

显然，我们需要有一种能够支持这些数据级联操作的方式。SQL 为此提供了可选的外键级联操作选项：

CONSTRAINT constraint_name
FOREIGN KEY (column_name)
REFERENCES parent_name(column_name)
ON DELETE [NO ACTION|RESTRICT|CASCADE|SET NULL|SET DEFAULT]
ON UPDATE [NO ACTION|RESTRICT|CASCADE|SET NULL|SET DEFAULT];

其中：

• NO ACTION 表示如果父表上的 DELETE 或者 UPDATE 操作违反外键约束，返回错误；在事务提交（COMMIT）时检查。
• RESTRICT 表示如果父表上的 DELETE 或者 UPDATE 操作违反外键约束，返回错误；在语句执行时立即检查。
• CASCADE 表示如果父表上执行 DELETE 或者 UPDATE 操作，级联删除或者更新子表上的记录。
• SET NULL 如果父表上执行 DELETE 或者 UPDATE 操作，将子表中的外键字段设置为 NULL。
• SET DEFAULT 如果父表上执行 DELETE 或者 UPDATE 操作，将子表中的外键字段设置为默认值。

如果没有指定级联选项，默认为 NO ACTION。

Oracle 不支持任何外键的级联更新操作；MySQL 中的 NO ACTION 和 RESTRICT 效果相同，都是在语句执行时立即检查。

我们删除 emp 表上的外键约束 fk_emp_dept，然后创建一个支持级联删除的约束：

ALTER TABLE emp DROP CONSTRAINT fk_emp_dept;

ALTER TABLE emp
ADD CONSTRAINT fk_emp_dept
               FOREIGN KEY (department_id)
               REFERENCES dept(department_id)
               ON DELETE CASCADE;

⚠️SQLite 不支持删除外键约束，只能重新创建 emp 表。

接下来我们可以删除 dept 表中的数据，同时 emp 表中的记录也会被级联删除。

DELETE
  FROM dept
 WHERE department_id = 1;

检查约束

检查约束指定了一个类似于 WHERE 子句中的条件，条件中可以使用一个或者多个字段，每一行数据都必须满足这个条件。不过与 WHERE 条件不同的是，如果检查的结果是 NULL，不违反检查约束。例如：

CREATE TABLE t_check(
  id INT PRIMARY KEY,
  c1 INT CHECK (c1 IS NOT NULL),
  c2 VARCHAR(10),
  c3 INT,
  c4 INT,
  CONSTRAINT check_c2 CHECK (c2 IN ('START', 'CLOSE'))
);

ALTER TABLE t_check
    ADD CONSTRAINT check_c3c4 CHECK ( c3 > c4 );

首先，c1 字段上定义了一个列级检查约束，这也是实现非空约束的一种方式；c2 字段上定义了一个表级检查约束，确保取值只能是列表中的值；最后，通过 ALTER TABLE 语句增加了一个检查约束，确保 c3 的值大于 c4，这种引用了多个字段的约束只能是表级约束。

⚠️SQLite 不支持 ALTER TABLE 语句增加约束，可以在创建表时进行定义。

然后我们插入一些数据：

INSERT INTO t_check(id, c1, c2, c3, c4) VALUES (1, 1, 'START', 20, 19);

INSERT INTO t_check(id, c1, c2, c3, c4) VALUES (2, NULL, 'START', 20, 19);
SQL Error [3819] [HY000]: Check constraint 't_check_chk_1' is violated.

INSERT INTO t_check(id, c1, c2, c3, c4) VALUES (2, 2, 'PROC', 20, 19);
SQL Error [3819] [HY000]: Check constraint 'check_c2' is violated.

INSERT INTO t_check(id, c1, c2, c3, c4) VALUES (2, 2, 'START', 20, 20);
SQL Error [3819] [HY000]: Check constraint 'check_c3c4' is violated.

第一条数据没有违反任何约束；第二条数据 c1 字段的数据为空，违反了非空检查约束；第三条数据违反了 c2 字段上的检查约束；第四条数据 c3 没有大于 c4。

如果插入的数据为空，不会违反检查约束。下面数据中的 c4 为空，可以插入成功：

INSERT INTO t_check(id, c1, c2, c3, c4) VALUES (2, 2, 'START', 20, NULL);

SELECT * FROM t_check;
id|c1|c2   |c3|c4|
--|--|-----|--|--|
 1| 1|START|20|19|
 2| 2|START|20|  |

默认值

默认值（DEFAULT）用于为字段提供默认的数据。如果用户插入时没有提供数据，使用该默认值。如果没有指定字段的默认值，默认为 NULL。

DROP TABLE t_default;

CREATE TABLE t_default(
  id INT PRIMARY KEY,
  c1 INT DEFAULT 0 NOT NULL,
  c2 INT
);

-- Oracle、MySQL
ALTER TABLE t_default MODIFY C2 INT DEFAULT 100;

-- SQL Server
ALTER TABLE t_default ADD DEFAULT 100 FOR c2;

-- PostgreSQL
ALTER TABLE t_default ALTER COLUMN c2 SET DEFAULT 100;

-- SQLite 不支持修改字段约束

其中，c1 字段定义了默认值 0；c2 字段通过 ALTER TABLE 语句定义了默认值 100。接下来测试一下数据插入：

INSERT INTO t_default(id) VALUES (1);
SELECT * FROM t_default;
id|c1|C2 |
--|--|---|
 1| 0|100|

其他约束

除了以上常用的完整性约束之外，SQL 还可以通过其他方式实现数据的约束：

• 字段类型，定义字段的数据类型实际上也是一种约束，属于域约束。例如，INT 类型的字段只能存储整数。不过，SQLite 使用动态类型，不受此限制。
• 断言（Assertion），与检查约束类似，但是支持更加宽泛的约束。例如，限制每个部门中最多包含 N 个员工。目前很少有数据库实现了断言。
• 触发器（Trigger），预定义存储的 SQL 语句，当用户对表中的数据执行操作时自动触发。触发器可以用于进行复杂的数据检查和控制。

定期更新数据库领域相关文章，欢迎关注❤️、点赞👍、转发📣！

转载：https://blog.csdn.net/horses/article/details/104393144