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

SQL编程思想 2021-09-16

1115

大家好！我是只谈技术不剪发的 Tony 老师。

为了维护数据的完整性和一致性，或者为了实现业务需求，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 语句，当用户对表中的数据执行操作时自动触发。触发器可以用于进行复杂的数据检查和控制。

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

数据库

文章转载自SQL编程思想，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

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

评论