正确性与约束

简介

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

约束绝不会使其他输入验证技术变得无用，即使它们测试相同的断言。尝试存储无效数据但失败所花费的时间是浪费时间。违反消息，例如系统和应用程序编程语言中的 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) 的唯一约束允许无限复制任何没有 Robin 的 Batman。

排他性约束仅在 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 展示了图书馆及其藏书和读者数据库的 Schema 的最初形态。每条边代表它所连接的表之间的关系。| 符号表示其一侧的单个记录，而“乌鸦脚”符号表示多个：一个图书馆拥有许多书籍和许多读者。

外键是另一个表主键的副本，逐列复制（支持代理键的一点：只需复制和引用一列），其值将此表中的记录链接到该表中的“父”记录。在上面的 Schema 中，books 表维护一个指向 libraries 的 library_id 外键（图书馆藏书），以及一个指向 authors 的 author_id（作者创作书籍）。但是，如果插入一本书时，其 author_id 在 authors 中不存在，会发生什么？

如果外键没有约束——即，它只是另一列或多列——一本书可能有一个不存在的作者。这是一个问题：如果有人试图跟随 books 和 authors 之间的链接，他们将一无所获。如果 authors.author_id 是一个序列整数，那么也有可能直到最终分配了虚假的 author_id 才有人注意到，结果你发现特定版本的《堂吉诃德》首先归于未知作者，然后归于皮埃尔·梅纳德，而米格尔·塞万提斯却无处可寻。

即使错误的 author_id 指向 authors 中存在的记录，对外键施加约束也无法阻止书籍被错误归属，因此其他检查和测试仍然很重要。然而，现有外键值的集合几乎总是可能外键值的一个微小子集，因此外键约束将捕获并防止大多数错误值。有了外键约束，没有不存在作者的《堂吉诃德》将被拒绝，而不是被记录。

对于某些“正确”的值

数据不正确的方式远不止本文所讨论的这些。约束有所帮助，但它们的灵活性也有限；许多常见的表内规范，例如限制某个值在列中出现的次数不超过两次或更多次，只能通过触发器来强制执行。

但表的结构本身也可能导致不一致。为了防止这些，我们将需要利用主键和外键，不仅用于定义和验证，还要用于规范化表之间的关系。然而，首先，我们才刚刚触及表之间的关系如何定义数据库本身的结构的皮毛。

常见问题