正确性和约束

导言

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

约束绝不会使其他输入验证技术变得毫无用处，即使它们测试相同的断言也是如此。花费时间和精力尝试存储无效数据是浪费时间。违反消息传递，就像系统和应用程序编程语言中的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表维护一个library_id外键到libraries（图书馆拥有书籍），以及一个author_id到authors（作者写书）。但是，如果插入的书籍的author_id在authors中不存在，会发生什么？

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

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

关于正确的特定值

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

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

常见问题解答