数据库基础体系 · 第 4/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

数据库 Schema 设计：数据类型、主外键、约束、NULL 与演进

Schema（模式）不是“把表建出来”的 SQL 文件，而是对数据结构、取值范围、实体关系和允许状态的可执行声明。它同时回答几个问题：

一列数据是什么类型，允许多大范围，如何比较和排序？
一行记录由什么键标识？
两张表之间的引用是否必须指向真实存在的记录？
哪些数据状态合法，哪些状态应由数据库拒绝？
“没有值”与“值为空”如何区分？
业务变化时，如何在旧代码、新代码和旧数据同时存在的阶段保持可用？

本文以 PostgreSQL 当前稳定版本和 MySQL 8.4 的公开语义为边界。除非特别说明，SQL 示例中的 DDL 偏向 PostgreSQL；涉及差异时会单独指出。生产执行还必须结合具体版本、存储引擎、事务设置、表规模和部署拓扑验证。

一、先建立 Schema 的基本模型

1. 表不是二维对象，而是受约束的关系

关系模型中的一张表可以抽象为：

R(A_1, A_2, \ldots, A_n)

其中：

$R$ 是关系名；
$A_i$ 是属性，也就是列；
一行是一个元组（tuple）；
每个属性都有定义域（domain），即允许出现的值集合。

例如：

customer(
    customer_id: 正整数,
    email: 字符串或 NULL,
    created_at: 时间戳
)

仅仅声明 email 是字符串，并不能表达：

邮箱是否必须存在；
两个客户是否允许使用同一个邮箱；
邮箱是否区分大小写；
邮箱长度是否有上限；
删除客户时，其订单是否也必须删除。

这些语义由数据类型、主键、唯一约束、检查约束、外键和 NULL 规则共同表达。

2. Schema 约束的是“允许状态”和“允许转移”

数据库约束可以看成对数据库状态 $D$ 的谓词：

C(D) = \text{true}

合法数据库状态必须满足所有约束：

C_1(D) \land C_2(D) \land \cdots \land C_k(D)

一次 INSERT、UPDATE 或 DELETE 不是只修改一行，而是尝试把数据库从状态 $D_0$ 转移到 $D_1$ 。如果提交时 $D_1$ 不满足约束，数据库必须拒绝这次转移，或者在特定约束行为下执行级联动作。

这解释了一个重要边界：

应用校验可以改善错误提示；
数据库约束负责阻止并发请求、脚本、后台任务和其他服务写入非法状态。

应用层检查“邮箱不存在”后再插入，并不能阻止两个并发事务同时通过检查；UNIQUE 约束才能提供最终的并发安全保证。

二、数据类型：先表达事实，再考虑便利性

1. 类型决定哪些值存在，以及数据库如何处理它们

数据类型至少影响：

可接受的输入；
比较、排序和聚合语义；
存储格式和空间；
索引行为；
与客户端驱动程序的映射；
隐式转换和错误边界。

例如，年龄如果声明为字符串：

age VARCHAR(10)

那么 '9'、'09'、'unknown' 和 ' 9 ' 都可能进入数据库。之后进行范围查询、排序或求平均值时，应用必须重新解析这些字符串。

如果声明为整数：

age INTEGER

数据库至少可以保证它是整数，但仍不能保证它是合法年龄。还需要：

CHECK (age BETWEEN 0 AND 150)

因此，类型和约束是两层不同的语义：

类型回答“这是什么类别的值”；
约束回答“该类别中哪些值允许出现”。

2. 精确数值与近似数值不能混用

金额、税率和计费数量通常需要精确数值。

amount NUMERIC(12, 2)

其中：

12 是总有效数字位数；
2 是小数位数；
可表示范围约为 -9999999999.99 到 9999999999.99。

实际项目中常配合非负约束：

amount NUMERIC(12, 2) NOT NULL CHECK (amount >= 0)

FLOAT、REAL、DOUBLE PRECISION 是近似数值类型。二进制浮点数通常无法精确表示许多十进制小数，例如 0.1。这意味着：

SELECT 0.1 + 0.2 = 0.3;

不能被当作可靠的金额相等判断。浮点类型适合测量值、科学计算或对误差有明确容忍度的场景；金额通常使用 NUMERIC/DECIMAL 或以最小货币单位存储的整数。

但“用整数存金额”也要明确单位：

amount_cent BIGINT NOT NULL CHECK (amount_cent >= 0)
currency CHAR(3) NOT NULL

100 到底是 100 元还是 100 分，必须由列名、注释或领域模型明确表达。

3. 整数类型不是“越小越好”

常见整数类型的选择依据是业务生命周期，而不是当前数据量。

例如 PostgreSQL 中：

smallint：16 位；
integer：32 位；
bigint：64 位。

MySQL 也提供相应的整数类型，并允许 UNSIGNED，但 UNSIGNED 的范围和跨数据库迁移语义需要单独处理。

如果用户 ID 未来可能跨多个数据中心生成，或者会合并多个系统，使用 BIGINT 或 UUID 可能比从 INTEGER 迁移更稳妥。另一方面，UUID 并不自动意味着更高性能；随机 UUID 可能导致索引写入局部性较差，具体还取决于版本、生成方式、索引和负载。

4. 字符串类型要区分长度、语义和排序规则

name VARCHAR(200)

通常表达“字符长度不超过 200”，不是“最多 200 个字节”。多字节字符集下，字符数和字节数不同。

TEXT 与 VARCHAR(n) 的差异也不能简单归结为“一个快、一个慢”。在 PostgreSQL 中，二者通常没有普遍性的性能差异；VARCHAR(n) 主要额外表达长度约束。MySQL 中也应根据实际字符集、行格式、索引前缀和版本规则验证，而不是把长度类型当成性能开关。

字符串还受排序规则（collation）和字符集影响：

"A" 与 "a" 是否相等；
重音字符如何排序；
ORDER BY 的顺序；
LIKE 或比较运算是否大小写敏感。

如果业务要求邮箱唯一，直接写：

UNIQUE (email)

并不自动定义大小写策略。以下两条记录是否应视为同一邮箱，应先成为业务规则，再映射到数据库：

Alice@example.com
alice@example.com

在 PostgreSQL 中，可以使用表达式唯一索引实现一种明确的规范化规则：

CREATE UNIQUE INDEX uq_user_email_lower
ON app_user (lower(email))
WHERE email IS NOT NULL;

这表示：非 NULL 邮箱按 lower(email) 的结果唯一。它不等价于所有语言和所有邮箱规则下的“大小写不敏感邮箱”，也不自动处理 Unicode 规范化。

5. 日期时间必须明确时区模型

常见的两个业务问题是：

这个事件发生在全球同一个时刻吗？
这个日期只是当地日历日期吗？

如果是同一个绝对时刻，例如订单创建时间，通常需要带时区语义的时间戳：

created_at TIMESTAMP WITH TIME ZONE NOT NULL

如果是“生日”“账单周期起始日”等日历日期，应使用：

birth_date DATE NOT NULL

不要把日期存成字符串：

birth_date VARCHAR(20)

这样会失去合法性检查、日期比较和日期函数能力。

需要注意 PostgreSQL 的 timestamp with time zone 并不是保存原始时区名称；它表示一个绝对时刻，显示时会根据会话时区转换。MySQL 的 TIMESTAMP 和 DATETIME 具有不同的时区转换行为，不能仅凭名称进行跨数据库替换。跨时区系统应明确：

写入时采用什么时区；
数据库会话时区是什么；
API 返回什么格式；
是否需要保留用户输入的原始时区。

6. `BOOLEAN`、枚举和状态机

布尔列适合只有两个业务状态的事实：

is_enabled BOOLEAN NOT NULL DEFAULT TRUE

如果状态有多个值，应考虑显式状态集合：

status VARCHAR(20) NOT NULL
    CHECK (status IN ('pending', 'paid', 'cancelled'))

状态列不是任意字符串。更重要的是，CHECK 只限制“当前值属于集合”，不限制状态迁移顺序。例如它允许：

pending -> cancelled
pending -> paid
paid -> cancelled

如果只有前两种迁移合法，必须额外实现状态机规则，例如在事务中由服务层控制并发更新，或使用触发器等机制；单列 CHECK 无法表达“旧值到新值”的一般迁移关系。

7. JSON 不应替代所有结构化列

JSON/JSONB 适合：

结构变化频繁的外部载荷；
不适合拆成固定列的扩展属性；
需要保留原始文档的场景。

但如果一个字段参与：

连接；
唯一性；
范围查询；
外键关系；
稳定报表；

它通常应成为结构化列，或者至少建立明确的生成列、表达式索引和约束。把所有内容塞入 JSON 会把数据库约束重新转移到每个应用调用方，导致数据质量依赖纪律而不是 Schema。

三、主键、候选键与外键：从唯一性到引用完整性

1. 候选键和主键

如果一组列能够唯一标识关系中的每一行，它是候选键（candidate key）。候选键可以不止一个：

user_id             -- 系统生成的候选键
email               -- 如果业务保证全局唯一，也是候选键

其中被选作主要标识的候选键是主键（primary key）。

主键包含两个核心性质：

唯一；
不允许为 NULL。

例如：

CREATE TABLE app_user (
    user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL,
    CONSTRAINT uq_app_user_email UNIQUE (email)
);

这里：

user_id 是主键；
email 是另一个候选键或业务唯一键；
UNIQUE 并不自动把 email 变成主键；
主键只能有一个，但唯一约束可以有多个。

在 MySQL 中，常见写法是：

CREATE TABLE app_user (
    user_id BIGINT NOT NULL AUTO_INCREMENT,
    email VARCHAR(320) NOT NULL,
    PRIMARY KEY (user_id),
    UNIQUE KEY uq_app_user_email (email)
) ENGINE=InnoDB;

AUTO_INCREMENT 和 PostgreSQL 的 identity 都只是生成键值的机制，不是唯一性本身。真正阻止重复的是主键或唯一约束。

2. 代理键不等于没有业务键

代理键（surrogate key）是没有直接业务含义的键，例如自增整数或 UUID。自然键（natural key）来自业务事实，例如国家代码、外部系统编号。

代理键常用于：

让引用列稳定；
避免业务字段变化导致大量外键更新；
缩短连接键；
隔离外部编码规则。

但代理键不能替代业务唯一约束：

CREATE TABLE product (
    product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku TEXT NOT NULL,
    CONSTRAINT uq_product_sku UNIQUE (sku)
);

如果只保留 product_id 主键而不约束 sku，数据库会允许两个产品使用同一个 SKU。应用“通常不会这样做”不是数据完整性保证。

3. 复合键表达复合身份

中间表经常使用复合主键：

CREATE TABLE user_role (
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL,
    granted_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES app_user(user_id),
    FOREIGN KEY (role_id) REFERENCES role(role_id)
);

主键表达的是：

(user\_id, role\_id) \text{ 的组合不能重复}

它不表达 user_id 单独唯一，也不表达 role_id 单独唯一。若再添加：

UNIQUE (user_id)

就会错误地把一个用户限制为只能拥有一个角色。

复合键的列顺序也影响索引的左前缀查询。例如 (user_id, role_id) 通常适合按 user_id 查询所有角色，但不一定适合只按 role_id 查询所有用户；后者可能需要另一个索引。

4. 外键表达引用完整性

外键约束要求子表中的非空引用值必须在父表的被引用键中存在。

CREATE TABLE customer (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE customer_order (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id)
        REFERENCES customer(customer_id)
);

允许的状态是：

customer = { 1, 2 }
order.customer_id = 1

不允许：

order.customer_id = 99

因为父表中不存在 customer_id = 99。

外键的基本检查方向有两个：

插入或更新子表

事务执行：

INSERT INTO customer_order(customer_id) VALUES (99);

如果父表没有 99，数据库拒绝语句。

删除或更新父表

若已有订单引用客户 1：

DELETE FROM customer WHERE customer_id = 1;

默认通常会被拒绝，因为这会产生悬挂引用。可以显式选择其他动作：

FOREIGN KEY (customer_id)
REFERENCES customer(customer_id)
ON DELETE CASCADE

常见动作包括：

RESTRICT：拒绝删除；
NO ACTION：约束检查失败时拒绝；在支持延迟约束的系统中，检查时点可能不同；
CASCADE：级联删除或更新；
SET NULL：将子表外键设为 NULL；
SET DEFAULT：设为默认值。

ON DELETE CASCADE 不是“方便清理数据”这么简单。它定义了删除图上的传播行为。例如删除一个客户可能删除订单，订单又可能删除订单明细。如果删除入口没有严格控制，影响范围会远大于单条 DELETE。

5. 外键列必须与被引用键语义匹配

外键列和被引用列应使用兼容的数据类型、符号范围、字符集和排序规则。尤其在 MySQL 中，整数类型的长度和 UNSIGNED 属性会影响外键兼容性；不同字符集或排序规则也可能导致创建失败或比较语义不一致。

工程上应优先引用：

父表主键；
明确的 UNIQUE 键。

不要把“当前碰巧唯一的普通索引”当作业务引用目标。不同数据库版本对非唯一被引用索引的支持和限制并不完全相同，使用主键或唯一约束更具可移植性，也更准确地表达关系模型。

6. 外键不只是逻辑约束，也会影响并发和锁

外键检查不是静态元数据操作。插入子表时，数据库必须确认父键存在；删除或更新父表时，数据库必须确认没有违反引用关系或执行级联。

因此，外键列通常应建立索引：

CREATE INDEX idx_order_customer_id
ON customer_order(customer_id);

有些数据库会为主键和唯一约束自动创建索引，但不会在所有情况下自动为外键列创建适合查询和级联检查的索引。没有外键索引时，父表删除、级联和关联查询可能扫描大量子表记录，放大锁等待和执行时间。

四、约束：把可证明的规则放在数据边界

1. `NOT NULL`

NOT NULL 表示该列不能处于 SQL 的 NULL 状态：

quantity INTEGER NOT NULL CHECK (quantity > 0)

它不表示：

字符串不能是 ''；
数字不能是 0；
时间不能是某个默认日期；
JSON 不能是空对象；
外键引用的父记录一定存在。

这些需要分别由类型、检查约束或外键表达。

2. `DEFAULT` 不等于必填，也不等于自动修复

created_at TIMESTAMP WITH TIME ZONE
    NOT NULL DEFAULT CURRENT_TIMESTAMP

DEFAULT 只在插入语句没有提供该列时使用。它通常不覆盖显式写入的 NULL：

INSERT INTO t DEFAULT VALUES;       -- 使用默认值
INSERT INTO t(created_at) VALUES (NULL); -- 若 NOT NULL，则失败

在不同数据库和 SQL 语句形式下，默认值的具体求值时机也需按文档确认。它不是历史数据回填工具，也不是应用传入错误值后的校正机制。

3. `CHECK` 约束与三值逻辑

一个常见误解是：

CHECK (price > 0)

等于“每一行都必须满足 price > 0”。

SQL 的 CHECK 通常要求表达式结果不能为 FALSE；结果为 TRUE 或 UNKNOWN 时可通过。若 price 可以为 NULL：

price = 10  -> TRUE
price = 0   -> FALSE，拒绝
price = NULL -> UNKNOWN，可能通过

因此完整约束通常写成：

price NUMERIC(12, 2) NOT NULL
    CHECK (price > 0)

或者：

CHECK (price IS NULL OR price > 0)

后者明确表达“允许空值，但有值时必须大于 0”。

PostgreSQL 文档强调，CHECK 约束应当是只依赖当前行的稳定条件。不要把跨行查询、当前时间变化或数据库外部状态偷偷塞进 CHECK，否则备份恢复、约束验证或后续更新可能出现不一致。跨行规则应考虑唯一约束、排他约束、外键、触发器或事务级业务逻辑。

MySQL 8.0.16 及之后的版本开始真正执行 CHECK 约束；MySQL 8.4 属于执行约束的版本。不能把旧版本中“语法接受了 CHECK”误认为“数据库实际拒绝了非法数据”。

4. `UNIQUE` 的 `NULL` 语义

UNIQUE 约束保证非 NULL 值不能重复，但 NULL 不是普通的相等值。PostgreSQL 和 MySQL 的常见默认语义都允许多个 NULL：

CREATE TABLE invite (
    invite_code TEXT UNIQUE
);

以下数据通常可以同时存在：

NULL
NULL
ABC

但两个 ABC 不允许同时存在。

如果业务要求“有值时唯一，且最多一个空值”，需要更明确的设计。在 PostgreSQL 中可以使用：

CREATE UNIQUE INDEX uq_invite_code_not_null
ON invite(invite_code)
WHERE invite_code IS NOT NULL;

这仍然允许多个 NULL，只是把唯一规则明确限定在非空值。若要限制最多一个空值，可使用表达式索引或单独建模，但必须谨慎处理类型和并发语义。

在 MySQL 中，使用生成列、表达式索引或业务结构实现此类规则时，需按 8.4 的具体索引和 NULL 语义验证，不能直接照搬 PostgreSQL 的部分索引语法。

5. 多列唯一约束是“组合唯一”

UNIQUE (tenant_id, external_id)

表达的是：

(tenant\_id, external\_id)

这个二元组不能重复，而不是 tenant_id 全局唯一，也不是 external_id 全局唯一。

这是多租户系统中的典型规则：

租户 A + 外部编号 100 允许存在
租户 B + 外部编号 100 也允许存在
同一租户 A + 外部编号 100 不允许第二次存在

如果还要求外部编号全局唯一，应另加：

UNIQUE (external_id)

6. 约束名称是诊断接口

应为约束指定稳定名称：

CONSTRAINT ck_order_amount_positive CHECK (amount > 0),
CONSTRAINT uq_order_tenant_external UNIQUE (tenant_id, external_id),
CONSTRAINT fk_order_customer FOREIGN KEY (customer_id)
    REFERENCES customer(customer_id)

这样应用可以根据约束名、SQLSTATE 或驱动程序错误信息区分：

唯一冲突；
外键冲突；
非空冲突；
检查约束冲突。

不要只解析人类语言错误消息。错误消息可能随版本、本地化和驱动程序变化；SQLSTATE 和约束名更适合机器处理，但仍应在目标数据库和驱动版本上验证。

五、NULL：它不是零、空字符串或“未知值的普通对象”

1. `NULL` 表达缺失或未知，而不是一个普通值

设：

email = NULL

它可能代表：

用户尚未填写；
数据尚未采集；
该字段不适用；
原始数据未知。

这些含义并不总是相同。如果业务需要区分，应建模为不同状态，而不是让所有情况都落入 NULL。

例如：

phone TEXT,
phone_status TEXT NOT NULL
    CHECK (phone_status IN ('unprovided', 'provided', 'invalid'))

或者通过单独的资料表表达“用户还没有资料记录”。

2. `NULL` 的比较使用 `IS NULL`

错误写法：

WHERE deleted_at = NULL

NULL 与任何值比较，包括另一个 NULL，结果通常是 UNKNOWN，而不是 TRUE。

正确写法：

WHERE deleted_at IS NULL
WHERE deleted_at IS NOT NULL

3. 三值逻辑会改变 `AND`、`OR` 和 `NOT`

SQL 布尔结果有三个值：

TRUE
FALSE
UNKNOWN

关键规则包括：

条件	结果
`TRUE AND UNKNOWN`	`UNKNOWN`
`FALSE AND UNKNOWN`	`FALSE`
`TRUE OR UNKNOWN`	`TRUE`
`FALSE OR UNKNOWN`	`UNKNOWN`
`NOT UNKNOWN`	`UNKNOWN`

WHERE 只保留结果为 TRUE 的行，因此 FALSE 和 UNKNOWN 都会被过滤。

例如：

SELECT *
FROM product
WHERE price > 100;

price = NULL 的行不会被返回，但这不表示它被判断为“价格不大于 100”；实际结果是 UNKNOWN。

4. `NOT IN` 与 NULL 的陷阱

假设：

-- customer_id 集合中包含 NULL
SELECT customer_id
FROM blocked_customer;

执行：

SELECT *
FROM customer
WHERE customer_id NOT IN (
    SELECT customer_id FROM blocked_customer
);

逻辑上，某个客户 ID 需要与集合中的每个值比较。只要其中一个比较结果涉及 NULL，总体可能变成 UNKNOWN，导致查询返回空集或少于预期。

更稳妥的写法通常是：

SELECT c.*
FROM customer AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_customer AS b
    WHERE b.customer_id = c.customer_id
);

NOT EXISTS 表达的是“没有找到匹配行”，不受子查询中无关 NULL 值以同样方式污染。

5. 聚合和连接也受 NULL 影响

常见规则：

COUNT(*)       -- 统计行数
COUNT(column)  -- 只统计 column 非 NULL 的行
SUM(column)    -- 忽略 NULL；若没有可聚合的非 NULL 值，结果可能为 NULL

因此：

SELECT COUNT(*), COUNT(email)
FROM app_user;

前者是用户总数，后者是有邮箱值的用户数。

COALESCE 可以把 NULL 转换为默认展示值：

SELECT COALESCE(display_name, '未命名')
FROM app_user;

但 COALESCE 是查询表达式，不会改变列本身的可空性，也不会修复历史数据。

6. 外键中的 NULL

外键列允许 NULL 时：

customer_id BIGINT NULL
    REFERENCES customer(customer_id)

NULL 通常表示“该订单没有指定客户”，而不是引用了某个 ID 为 NULL 的客户。因为父表主键本身不能是 NULL。

若业务要求每个订单都必须有客户，应写：

customer_id BIGINT NOT NULL
    REFERENCES customer(customer_id)

ON DELETE SET NULL 只有在子表外键允许 NULL 时才有意义；如果同时声明 NOT NULL，删除父行时就无法把子列置空。

六、关系模型、规范化与反规范化边界

1. 函数依赖说明“一个值决定另一个值”

函数依赖记作：

X \rightarrow Y

含义是：只要两行的 $X$ 相同，它们的 $Y$ 就必须相同。

例如：

customer_id -> customer_name, customer_email

同一个 customer_id 只能对应一个客户名称和邮箱。

订单明细可能有：

(order_id, product_id) -> quantity
product_id -> product_name, current_price

这表示产品名称由 product_id 决定，而不是由 (order_id, product_id) 的组合额外决定。

2. 完整算例：从重复组到规范化表

错误设计：

order_line(
    order_id,
    product_id,
    product_name,
    quantity,
    unit_price,
    customer_id,
    customer_name
)

假设函数依赖为：

(order\_id, product\_id) \rightarrow quantity, unit\_price

product\_id \rightarrow product\_name

order\_id \rightarrow customer\_id

customer\_id \rightarrow customer\_name

主键是 (order_id, product_id)。但：

product_name 只依赖复合键的一部分 product_id；
customer_id 只依赖 order_id；
customer_name 依赖 customer_id，形成传递依赖。

这会造成异常：

更新异常

同一个产品出现在 1000 个订单行中，产品名称修改时必须更新 1000 行。漏改一行就出现数据矛盾。

插入异常

还没有订单时，无法保存一个产品，因为产品信息被绑定在订单明细中。

删除异常

删除该产品的最后一个订单行时，产品名称等信息也一并丢失。

拆分为：

customer(
    customer_id PRIMARY KEY,
    customer_name
)

sales_order(
    order_id PRIMARY KEY,
    customer_id REFERENCES customer(customer_id)
)

product(
    product_id PRIMARY KEY,
    product_name
)

order_line(
    order_id REFERENCES sales_order(order_id),
    product_id REFERENCES product(product_id),
    quantity,
    unit_price,
    PRIMARY KEY (order_id, product_id)
)

拆分后，函数依赖更接近各自关系的键：

customer_id -> customer_name
order_id -> customer_id
product_id -> product_name
(order_id, product_id) -> quantity, unit_price

这正是规范化的核心：让非键属性依赖于正确的键，减少重复和异常。

3. 范式不是“表越多越好”

实际设计通常关注：

第一范式：属性值保持原子性，不在单列中塞入无法独立处理的重复组；
第二范式：在已经存在复合候选键时，非键属性不能只依赖候选键的一部分；
第三范式：非键属性不应通过另一个非键属性传递决定；
BCNF：每个非平凡函数依赖 $X \rightarrow Y$ 中， $X$ 都应是超键。

但范式依赖于真实函数依赖，不是机械套规则。例如：

country_code -> country_name

只有在业务上保证国家代码唯一决定国家名称时，这个拆分才成立。

4. 订单中的价格是合理的反规范化

产品表有当前价格：

product.current_price

订单明细通常还应保存成交时价格：

order_line.unit_price

这看似重复，但两者语义不同：

product.current_price：当前目录价格；
order_line.unit_price：该订单成立时的价格。

这里不是违反规范化，而是保存了历史事实。若只在查询订单时读取当前产品价格，产品调价后历史订单金额会被错误改写。

反规范化应满足一个可说明的条件：

重复列表示不同的业务事实，或为了特定读模型；
能明确谁是事实来源；
有写入和校验机制；
能接受同步失败、回填和修复成本；
读性能收益足以覆盖一致性复杂度。

“查询需要 JOIN”本身不是反规范化理由。先确认函数依赖和业务语义，再决定是否复制。

七、一个可运行的 Schema 示例

以下示例以 PostgreSQL 为边界，使用事务创建订单相关表：

BEGIN;

CREATE TABLE customer (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY,
    email TEXT NOT NULL,
    display_name TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_customer PRIMARY KEY (customer_id),
    CONSTRAINT uq_customer_email UNIQUE (email),
    CONSTRAINT ck_customer_display_name_nonempty
        CHECK (length(btrim(display_name)) > 0)
);

CREATE TABLE product (
    product_id BIGINT GENERATED ALWAYS AS IDENTITY,
    sku TEXT NOT NULL,
    product_name TEXT NOT NULL,
    current_price NUMERIC(12, 2) NOT NULL,

    CONSTRAINT pk_product PRIMARY KEY (product_id),
    CONSTRAINT uq_product_sku UNIQUE (sku),
    CONSTRAINT ck_product_name_nonempty
        CHECK (length(btrim(product_name)) > 0),
    CONSTRAINT ck_product_current_price_nonnegative
        CHECK (current_price >= 0)
);

CREATE TABLE sales_order (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY,
    customer_id BIGINT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_sales_order PRIMARY KEY (order_id),
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id)
        REFERENCES customer(customer_id)
        ON DELETE RESTRICT,
    CONSTRAINT ck_order_status
        CHECK (status IN ('pending', 'paid', 'cancelled'))
);

CREATE TABLE order_line (
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price NUMERIC(12, 2) NOT NULL,

    CONSTRAINT pk_order_line PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_order_line_order
        FOREIGN KEY (order_id)
        REFERENCES sales_order(order_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_order_line_product
        FOREIGN KEY (product_id)
        REFERENCES product(product_id)
        ON DELETE RESTRICT,
    CONSTRAINT ck_order_line_quantity_positive
        CHECK (quantity > 0),
    CONSTRAINT ck_order_line_unit_price_nonnegative
        CHECK (unit_price >= 0)
);

CREATE INDEX idx_sales_order_customer_id
    ON sales_order(customer_id);

CREATE INDEX idx_order_line_product_id
    ON order_line(product_id);

COMMIT;

每一步为什么成立

customer_id、product_id、order_id 使用 identity 自动生成值，但最终唯一性由主键保证。
客户邮箱和产品 SKU 是业务唯一键，因此使用 UNIQUE。
btrim(display_name) 防止只包含空格的名称，但因为列同时 NOT NULL，不会出现 length(NULL) 被 CHECK 放过的问题。
订单必须引用真实客户，因此 customer_id NOT NULL + FOREIGN KEY 同时表达“必填”和“必须存在”。
order_line 的 (order_id, product_id) 主键防止同一订单重复添加同一产品。
unit_price 复制产品价格是为了保存成交时事实，不应在产品调价后自动跟随。
删除订单时删除明细是局部的 CASCADE；删除客户和产品则拒绝，以避免无意丢失业务历史。
sales_order(customer_id) 和 order_line(product_id) 索引服务于连接、父表删除检查和常见查询；主键 (order_id, product_id) 的索引主要服务于按订单读取明细。

插入数据：

INSERT INTO customer(email, display_name)
VALUES ('alice@example.com', 'Alice')
RETURNING customer_id;

假设返回 1，再插入产品和订单：

INSERT INTO product(sku, product_name, current_price)
VALUES ('BOOK-001', 'Database Basics', 59.90)
RETURNING product_id;

假设返回 1：

INSERT INTO sales_order(customer_id)
VALUES (1)
RETURNING order_id;

假设返回 1：

INSERT INTO order_line(order_id, product_id, quantity, unit_price)
VALUES (1, 1, 2, 59.90);

以下操作会失败：

-- 违反数量检查约束
INSERT INTO order_line(order_id, product_id, quantity, unit_price)
VALUES (1, 1, 0, 59.90);

-- 违反复合主键
INSERT INTO order_line(order_id, product_id, quantity, unit_price)
VALUES (1, 1, 1, 59.90);

-- 违反外键
INSERT INTO sales_order(customer_id)
VALUES (999999);

错误的具体 SQLSTATE、约束名和驱动异常类型依赖数据库驱动，但失败原因应分别对应 CHECK、主键/唯一约束和外键约束。

八、约束检查、事务与并发

1. 约束防止的是提交到非法状态

在事务中：

BEGIN;

UPDATE product
SET current_price = 69.90
WHERE product_id = 1;

INSERT INTO order_line(order_id, product_id, quantity, unit_price)
VALUES (1, 1, 1, 69.90);

COMMIT;

订单明细的 unit_price 是显式传入的历史价格；产品当前价格变化不会自动修改订单历史。

如果一个事务插入子表，另一个事务同时删除父表，数据库必须根据外键约束协调两者。具体等待、锁模式和可见性取决于引擎的事务隔离级别和实现，但结果不能允许提交后留下非法引用。

2. 应用层“先查再插”不是唯一性保证

错误模式：

事务 A：SELECT 检查 email 不存在
事务 B：SELECT 检查 email 不存在
事务 A：INSERT email
事务 B：INSERT email

如果没有唯一约束，两者都可能提交。正确设计是：

ALTER TABLE customer
ADD CONSTRAINT uq_customer_email UNIQUE (email);

然后由应用处理唯一冲突。PostgreSQL 可使用：

INSERT INTO customer(email, display_name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

MySQL 常见对应语法是：

INSERT INTO customer(email, display_name)
VALUES ('alice@example.com', 'Alice')
ON DUPLICATE KEY UPDATE
    display_name = VALUES(display_name);

但两者的返回值、触发器行为、受影响行数和并发语义并不完全相同，迁移代码时不能只替换关键字。

3. 可延迟约束不是所有数据库都有

PostgreSQL 支持某些约束声明为 DEFERRABLE，可以把检查推迟到事务提交，例如处理暂时不满足最终顺序的相互引用更新。典型形式：

CONSTRAINT fk_example
    FOREIGN KEY (parent_id)
    REFERENCES parent(parent_id)
    DEFERRABLE INITIALLY DEFERRED

MySQL InnoDB 不提供 PostgreSQL 同等的可延迟外键约束语义。不能把“事务中执行”误认为“所有约束都可以延迟到 COMMIT 检查”。

九、Schema 演进：不是修改文件，而是迁移多个运行中组件

Schema 演进时至少有三个参与者：

数据库中的旧结构和旧数据；
旧版本应用；
新版本应用。

如果新代码先发布，而旧代码不能理解新结构，或者迁移先删除旧列，而旧代码仍在读取，就会出现发布窗口错误。因此，迁移必须按兼容性设计。

1. Expand-Contract 模式

Expand-Contract（扩展—收缩）把不兼容变更拆成兼容阶段。

假设要把：

customer.full_name

拆成：

customer.first_name
customer.last_name

直接删除 full_name 的风险是旧应用仍然执行：

SELECT full_name FROM customer;

会立即失败。稳妥流程如下。

阶段一：Expand

添加新列，先允许 NULL：

ALTER TABLE customer
ADD COLUMN first_name TEXT,
ADD COLUMN last_name TEXT;

此时旧应用仍可使用 full_name，新应用也可以开始读取新列。

阶段二：Backfill

分批填充历史数据：

UPDATE customer
SET first_name = split_part(full_name, ' ', 1),
    last_name  = NULLIF(
        substr(full_name, length(split_part(full_name, ' ', 1)) + 2),
        ''
    )
WHERE first_name IS NULL
  AND full_name IS NOT NULL;

这个例子只适用于非常简单的“按第一个空格拆分”规则。真实姓名可能包含多个姓氏、空格、单名和非西文命名结构，不能把此 SQL 当作通用姓名解析器。

生产回填通常需要：

按主键范围或批次执行；
每批控制事务大小；
记录已完成位置；
监控锁等待、复制延迟和 WAL/binlog 增长；
失败后能够从上次位置继续。

阶段三：双写或切换写入来源

如果新应用将姓名写入两列，而旧应用仍只写 full_name，就会产生不一致。可选方案包括：

应用层双写；
数据库触发器同步；
先改造旧应用使其兼容新列；
通过新表或视图提供兼容接口。

双写必须规定权威来源。否则两个方向都能覆盖对方，冲突无法判断。

阶段四：验证并收紧约束

确认所有历史行和在线写入都满足规则后，再考虑：

ALTER TABLE customer
ALTER COLUMN first_name SET NOT NULL;

PostgreSQL 可使用 NOT VALID 对某些新增 CHECK 或外键先只约束新写入，再单独验证旧数据；但 NOT VALID 并不是所有约束或所有数据库都支持，且“已创建”和“已验证”是两个不同状态。验证前必须先清理冲突数据。

阶段五：Contract

当所有应用版本都不再读写旧列后，才删除：

ALTER TABLE customer
DROP COLUMN full_name;

删除是不可逆的结构变化。即使数据库支持事务性 DDL，也不能把它视为业务可回滚：旧应用回滚后可能仍然依赖已删除列。因此，数据库回滚和应用回滚必须分别设计。

2. 约束增加的顺序

给已有大表增加约束时，不能只考虑最终 DDL，还要考虑验证期间的数据和锁。

例如，已有数据中可能存在：

customer_id = NULL

直接执行：

ALTER TABLE sales_order
ALTER COLUMN customer_id SET NOT NULL;

会失败。正确顺序通常是：

查询并修复违规数据；
阻止新写入继续制造违规数据；
验证全表满足条件；
设置 NOT NULL；
应用切换到新假设。

PostgreSQL 中，增加已验证的检查约束可采用：

ALTER TABLE sales_order
ADD CONSTRAINT ck_sales_order_customer_id_not_null
CHECK (customer_id IS NOT NULL) NOT VALID;

该约束会对新增或修改行执行检查，但历史数据尚未被验证。清理完历史数据后：

ALTER TABLE sales_order
VALIDATE CONSTRAINT ck_sales_order_customer_id_not_null;

之后是否再使用 SET NOT NULL，取决于目标 PostgreSQL 版本及具体迁移策略。不要把这种 PostgreSQL 机制直接套到 MySQL。

3. 增加索引的锁风险

PostgreSQL 中：

CREATE INDEX CONCURRENTLY idx_order_customer_id
ON sales_order(customer_id);

可以减少创建普通索引时对写入的阻塞，但有重要限制：

不能在事务块中执行；
失败后可能留下无效索引，需要诊断和清理；
构建期间仍会消耗 CPU、I/O 和资源；
完成时间取决于表规模和并发负载。

MySQL 8.4 的在线 DDL 使用 ALGORITHM、LOCK 等能力时，实际是否无阻塞取决于操作类型、存储引擎、表结构和版本；即使指定了较宽松的锁要求，也可能因为无法满足而报错。生产变更应先在同版本、相似规模和相同引擎的环境演练，并观察元数据锁。

4. 元数据锁与“很快的 DDL”

某些 DDL 本身扫描很少，但仍需等待元数据锁。例如：

长事务正在使用目标表；
另一个连接有未提交的查询；
连接池中存在空闲但未提交的事务。

执行迁移前应检查：

活跃事务持续时间；
锁等待链；
复制延迟；
连接池是否有悬挂事务。

DDL 等待锁时可能形成连锁阻塞：迁移等待旧事务，后续查询又排在迁移之后等待，最终表现为整个服务延迟升高。设置合理的锁超时、迁移窗口和失败恢复策略比“命令最终会成功”更重要。

5. 删除、重命名和类型扩大不能假设可回滚

以下操作都应被视为高风险：

DROP COLUMN
RENAME COLUMN
ALTER COLUMN TYPE
DROP CONSTRAINT

风险包括：

旧应用无法启动；
ORM 生成的 SQL 仍引用旧名字；
视图、触发器、报表和 ETL 作业失效；
类型转换失败或精度丢失；
索引重建导致资源峰值；
复制链路和只读副本延迟。

通常应优先采用“添加新对象、复制数据、切换读取、停止旧写入、最后删除旧对象”的方式。回滚计划不能只写“执行 down migration”，而应明确：

数据是否已从旧列迁移到新列；
新写入是否能回写旧列；
回滚期间哪个字段是权威来源；
删除的历史数据是否有备份或可重放日志；
应用版本是否仍兼容旧 Schema。

十、常见失败设计及诊断路径

1. 把所有列都设置为可空

表现：

customer_id = NULL
quantity = NULL
status = NULL

应用随后只能在每个查询和业务分支中猜测含义。诊断方法是先为每个 NULL 问三个问题：

这表示未知、缺失还是不适用？
该状态是否允许业务继续？
是否应由另一列或另一张表表达？

如果“不允许继续”，应尽早使用 NOT NULL 或外键，而不是让错误延迟到业务代码。

2. 用默认值掩盖未知值

例如：

status TEXT NOT NULL DEFAULT 'unknown'

这可能把“旧数据没有状态”“数据采集失败”“业务确实未知”混为一个状态。默认值适合具有明确自然初值的字段，例如创建时间、启用标志；不适合替代尚未知道的事实。

3. 只在应用层做唯一性和外键校验

失败表现通常是：

并发下出现重复业务编号；
删除父记录后出现孤儿记录；
数据导入脚本绕过服务导致脏数据。

诊断时应直接检查数据库元数据和约束，而不是只搜索应用代码。PostgreSQL 可通过 pg_constraint、pg_indexes 等系统目录检查；MySQL 可使用 SHOW CREATE TABLE、INFORMATION_SCHEMA.TABLE_CONSTRAINTS、KEY_COLUMN_USAGE 等接口。不同数据库系统目录结构不同，不应把查询脚本跨库复用。

4. 外键存在但查询仍然很慢

外键保证正确性，不保证所有查询性能。检查：

子表外键列是否有索引；
连接条件是否匹配索引前缀；
是否存在高频按外键过滤；
父表删除是否触发大范围检查或级联；
执行计划是否使用了预期索引。

可在 PostgreSQL 使用 EXPLAIN (ANALYZE, BUFFERS)，在 MySQL 使用 EXPLAIN 或相应的分析工具验证计划。但不要仅凭“有索引”推断一定会使用索引，选择性、统计信息和查询条件都会影响计划。

5. 用 `CHECK` 表达跨行规则

错误示例：

CHECK (
    (SELECT count(*) FROM ...)
)

即使某些系统在语法层面允许类似表达，也不应依赖它表达跨表一致性。如下规则：

一个部门最多有一个当前负责人
会议时间不能与同一房间的其他会议重叠
账户余额不能低于所有已提交扣款之和

需要唯一索引、排他约束、事务锁、触发器或专门的业务事务设计。单行 CHECK 的职责是验证当前行可直接计算的条件。

6. 忽略部署边界，导致迁移中断服务

如果应用部署是滚动发布，同一时刻可能存在：

旧实例 + 新实例 + 迁移任务

此时 Schema 必须同时兼容旧实例和新实例。一个只兼容新代码的变更，在滚动发布环境中就是不兼容变更。

验证方法包括：

用旧版本应用连接新 Schema；
用新版本应用连接扩展后的旧 Schema；
并发执行读写、回填和迁移；
模拟迁移中途失败；
模拟应用回滚；
检查副本、备份恢复和 CDC/ETL 读取。

十一、设计 Schema 时的推导顺序

面对新领域，不要从“我要建几张表”开始，而应按以下顺序推导：

第一步：列出事实和对象

例如订单系统中：

客户有客户编号、邮箱、名称
产品有产品编号、SKU、名称、当前价格
订单属于一个客户
订单包含多个产品
订单行保存购买数量和成交价格

第二步：为每个事实确定定义域

数量：正整数
价格：非负精确数值
创建时间：绝对时间
状态：有限集合

第三步：找候选键和函数依赖

customer_id -> customer attributes
product_id -> product attributes
(order_id, product_id) -> order-line attributes

第四步：把业务不变量变成约束

客户编号必须唯一       -> PRIMARY KEY
SKU 必须唯一           -> UNIQUE
数量必须大于 0        -> CHECK
订单必须属于客户       -> FOREIGN KEY + NOT NULL
名称不能全为空格       -> CHECK

第五步：明确每个 NULL 的含义

不要只问“这个字段是不是可选”，还要问：

不存在、未知、尚未填写、不适用，是否是同一状态？

若不是，就不要让一个 NULL 承担多个业务含义。

第六步：设计历史和变更语义

当前价格会变化，但历史成交价格不能变化
客户邮箱可能修改，但旧订单仍需保留客户身份
状态只能按规定路径迁移

这一步决定哪些值应复制保存、哪些值应通过外键读取、哪些变更需要审计表。

第七步：为演进设计兼容阶段

每一个非加法变更都应回答：

旧代码能否读写？
新代码能否在迁移完成前运行？
历史数据如何填充？
约束何时变为强制？
失败后如何恢复？
什么时候可以删除旧结构？

Schema 设计的核心不是选择某个“标准模板”，而是把领域事实、函数依赖、合法状态和生命周期变化准确地映射为数据库能够执行和验证的结构。数据类型限制值的性质，主键和唯一约束确定身份，外键维护引用完整性，CHECK 和 NOT NULL 限制局部状态，NULL 明确表达缺失语义，而迁移策略则保证这些规则能够在持续运行的系统中逐步落地。

系列导航与关联阅读

官方资料

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。