正确性和约束

介绍

存储数据是一回事；存储有意义、有用、正确的数据则是另一回事。虽然意义和实用性本身是主观的，但至少可以逻辑地定义和强制正确性。类型已经确保数字是数字，日期是日期，但不能保证重量或距离是正数，也不能防止日期范围重叠。元组、表和数据库约束对要存储的数据应用规则，并拒绝不符合要求的值或值的组合。

约束绝不会使其他输入验证技术变得毫无用处，即使它们测试的是相同的断言。花费时间尝试并失败地存储无效数据是浪费时间。违规消息，就像系统和应用程序编程语言中的 assert 一样，只会比任何不直接参与数据库的人需要更多地详细地揭示第一个候选记录的第一个问题。但就数据的正确性而言，约束就是法律，无论好坏；其他任何内容都是建议。

关于元组：非空、默认值和检查

非空约束是最简单的类别。元组必须具有约束属性的值，或者换句话说，列的允许值集不再包含空集。没有值意味着没有元组：插入或更新被拒绝。

防止空值的保护与在 CREATE TABLE 或 ADD COLUMN 中声明 column_name COLUMN_TYPE NOT NULL 一样简单。空值会导致整个类别的问题在数据库和最终用户之间，因此习惯性地对任何没有充分理由允许空值的列定义非空约束是一个好习惯。

如果在插入或更新中没有指定（通过省略或显式 NULL），则提供默认值并不总是被视为约束，因为候选记录被修改和存储而不是被拒绝。在许多 DBMS 中，默认值可以通过函数生成，尽管 MySQL 不允许为此目的使用用户定义函数。

任何仅依赖于单个元组内的值的验证规则都可以实现为 CHECK 约束。从某种意义上说，NOT NULL 本身是 CHECK (column_name IS NOT NULL) 的简写；违规时收到的错误消息最能说明问题。但是，CHECK 可以应用并强制执行单个元组上任何布尔谓词的真值。例如，存储地理位置的表应 CHECK (latitude >= -90 AND latitude < 90)，经度类似于 -180 到 180 之间——或者，如果可用，则使用和验证 GEOGRAPHY 数据类型。

关于表：唯一性和排除

表级约束测试元组彼此之间的关系。在唯一约束中，只有一条记录可能具有约束列的任何给定值集。可空性会导致问题，因为 NULL 永远不会等于其他任何东西，包括 NULL 本身。因此，对 (batman, robin) 的唯一约束允许无限复制任何没有罗宾的蝙蝠侠。

排除约束仅在 PostgreSQL 和 DB2 中受支持，但它填补了一个非常有用的利基：它们可以防止重叠。指定约束字段以及每个字段将被评估的操作，新记录将仅在没有现有记录成功比较每个字段和操作时被接受。例如，一个 schedules 表可以配置为拒绝冲突

-- text, int, etc. comparisons in exclusion constraints require this
-- Postgres extension
CREATE EXTENSION btree_gist;

CREATE TABLE schedules (
  schedule_id SERIAL NOT NULL PRIMARY KEY,
  room_number TEXT NOT NULL,
  -- a range of TIMESTAMP WITH TIME ZONE provides both start and end
  duration TSTZRANGE,
  -- table-level constraints imply an index, since otherwise they'd
  -- have to search the entire table to validate a candidate record;
  -- GiST (generalized search tree) indexes are usually used in
  -- Postgres
  EXCLUDE USING GIST (
    room_number WITH =,
    duration WITH &&
  )
);

INSERT INTO schedules (room_number, duration)
VALUES ('32A', '[2020-08-20T10:00:00Z,2020-08-20T11:00:00Z)');

-- the same time in a different room: accepted
INSERT INTO schedules (room_number, duration)
VALUES ('32B', '[2020-08-20T10:00:00Z,2020-08-20T11:00:00Z)');

-- a half-hour overlap for an already-scheduled room: rejected
INSERT INTO schedules (room_number, duration)
VALUES ('32A', '[2020-08-20T10:30:00Z,2020-08-20T11:30:00Z)');

Upsert 操作，例如 PostgreSQL 的 ON CONFLICT 子句或 MySQL 的 ON DUPLICATE KEY UPDATE 使用表级约束来检测冲突。就像非空约束可以表示为 CHECK 约束一样，唯一约束可以表示为对等式的排除约束。

主键

唯一约束有一个特别有用的特例。通过对唯一列或列添加额外的非空约束，表中的每条记录都可以通过其约束列的值来唯一识别，这些值统称为键。多个候选键可以共存于一个表中，例如 users 有时仍然具有不同的唯一且非空的 email 和 username；但是，声明主键会建立一个单一标准，根据该标准可以公开且唯一地识别记录。一些 RDBMS 甚至通过主键（为此目的称为聚集索引）在页面上组织行，以使通过主键值进行搜索尽可能快。

主键有两种类型。自然键是在表格数据中“自然”包含的列或列上定义的，而代理键或合成键是专门为成为键而发明的。自然键需要谨慎——从名称到编号方案，许多东西的变化比数据库设计人员通常认为的更多。包含国家和地区名称的查找表可以使用它们各自的 ISO 3166 代码作为安全的自然主键，但使用可变值（如姓名或电子邮件地址）作为自然键的 users 表会招致麻烦。如有疑问，请创建代理键。

如果自然键跨越多个列，则应始终至少考虑代理键，因为多列键的管理需要更多努力。但是，如果自然键适合，则应按索引中的顺序按特异性递增排列列：国家代码*然后*地区代码，而不是相反。

代理键历来是一个单个整数列，或在最终将分配数十亿时使用 BIGINT。关系型数据库可以自动用系列中的下一个整数填充代理键，此功能通常称为 SERIAL 或 IDENTITY。

自动递增的数字计数器并非没有缺点：添加具有预先生成键的记录会导致冲突，如果将顺序值暴露给用户，他们很容易猜测其他有效键是什么。通用唯一标识符 (UUID) 避免了这些缺点，并且已成为代理键的常见选择，尽管它们在页面内比简单的数字大得多。v1（基于 MAC 地址）和 v4（伪随机）UUID 类型最常使用。

关于数据库：外键

关系型数据库只实现一类多表约束，即 "子集要求" 或外键。这种唯一的约束类型是参照完整性的保证者，该原则可以防止表格之间出现不一致，并将关系型数据库与电子表格区分开来。

这个非正式的“实体-关系图”或 ERD 显示了图书馆及其馆藏和读者数据库模式的开始。每条边代表连接它的表格之间的关系。| 符号表示它那一侧的单个记录，而“乌鸦脚”符号代表多个：一个图书馆拥有许多书籍，并拥有许多读者。

外键是另一个表格主键的副本，逐列（有利于代理键：只有一列需要复制和引用），其值将此表格中的记录链接到该表格中的“父”记录。在上图所示的模式中，books 表维护一个指向 libraries 的 library_id 外键，libraries 保存书籍，以及一个指向 authors 的 author_id，作者编写了这些书籍。但是，如果插入一本具有不存在于 authors 中的 author_id 的书籍会发生什么？

如果外键不受约束——也就是说，它只是一列或多列——那么一本书籍可以有一个不存在的作者。这是一个问题：如果有人试图跟踪 books 和 authors 之间的链接，他们会无功而返。如果 authors.author_id 是一个连续整数，那么在最终分配虚假的 author_id 之前没有人注意到这一点的可能性也存在，最终你会发现 *堂吉诃德* 的一个特定版本最初归属于没有记录的作者，然后归属于皮埃尔·梅纳德，而米格尔·德·塞万提斯却无处可寻。

约束外键不能阻止一本书籍被错误归因（如果错误的 author_id 指向 authors 中的现有记录），因此其他检查和测试仍然很重要。但是，现存外键值的集合几乎总是可能外键值的微小子集，因此外键约束将捕获并阻止大多数错误值。使用外键约束，具有不存在作者的 *堂吉诃德* 将被拒绝，而不是被记录下来。

对于正确的某些值

数据可能存在比这里提到的更多错误方式。约束很有帮助，但即使它们也只有那么灵活；许多常见的表内规范，比如对一个值允许在一个列中出现的次数限制为两次或更多次，只能通过 触发器 强制执行。

但是，表格的结构本身也可能导致不一致。为了防止这种情况，我们需要调动主鍵和外鍵，不仅用于定义和验证，而且用于规范化表格之间的关系。不过，首先，我们只是触及了 表格之间的关系如何定义数据库本身的结构 的表面。

常见问题解答