正确性和约束

简介

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

即使约束测试相同的断言，约束也不会使其他输入验证技术变得无用。花费时间和精力尝试存储无效数据是浪费时间。与系统和应用程序编程语言中的 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) 避免了这些缺点，并且已成为代理键的常见选择，尽管它们在页面上也比简单的数字大得多。最常用的 UUID 类型是 v1（基于 MAC 地址）和 v4（伪随机）。

关于数据库：外键

关系数据库仅实现一类多表约束，即 “子集要求” 或外键。这种唯一的约束类型是引用完整性的保证者，引用完整性是防止表之间不一致并区分关系数据库和电子表格的原则。

这个非正式的“实体关系图”或 ERD 显示了一个数据库模式的开始，该模式用于图书馆及其馆藏和赞助人。每条边都表示它连接的表之间的关系。| 符号表示其侧面的单个记录，而“鸡爪”符号表示多个：一个图书馆拥有许多书籍并有许多赞助人。

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

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

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

对于某些正确的值

数据可能不正确的方式远不止此处讨论的这些。约束有所帮助，但即使约束也只是如此灵活；许多常见的表内规范（例如，限制一个值在列中允许出现的次数为两次或更高）只能通过 触发器 来强制执行。

但是，表的结构本身也可能导致不一致。为了防止这些情况，我们需要组织主键和外键，不仅要定义和验证，还要规范化表之间的关系。但是，首先，我们几乎没有触及 表之间的关系如何定义数据库本身的结构 的表面。

常见问题解答