字段与约束设计

NULL 与 NOT NULL

NULL 表示没有值 / 未知 / 未设置，与空字符串、0 不同。

NULL → 没有值
'' → 有值，只是字符串为空
0 → 有值，数值为 0

字段是否允许 NULL，应根据业务语义决定：

一条合法记录中，这个字段是否允许不存在？

必须存在：

用户名
订单金额
库存数量
创建时间

通常使用：

sql

NOT NULL

允许不存在：

头像
个人简介
发布时间（未发布时）
删除时间（未删除时）

可以允许 NULL。

不要使用无意义的默认值代替真正的「不存在」，例如：

published_at = ''
deleted_at = 0
parent_id = 0

如果业务含义就是「没有」，通常使用 NULL 更准确。

UNIQUE 唯一约束

UNIQUE 用于保证字段值不能重复。

例如：

username → UNIQUE
email → UNIQUE

主键已经具有唯一性，因此：

sql

id PRIMARY KEY

通常不需要再额外设置：

sql

id UNIQUE

联合唯一

有时单个字段允许重复，但多个字段的组合不能重复。

例如项目成员：

project_member

- project_id
- user_id

一个用户不能重复加入同一个项目：

sql

UNIQUE (project_id, user_id)

允许：

(项目1, 用户1)
(项目1, 用户2)
(项目2, 用户1)

不允许：

(项目1, 用户1)
(项目1, 用户1)

联合唯一可以包含多个字段：

sql

UNIQUE (student_id, course_id, semester)

表示同一个学生在同一学期不能重复选择同一门课程，但可以在不同学期再次选择。

核心：

UNIQUE 约束的是业务定义的「唯一组合」，不一定是单个字段。

条件唯一

有些业务唯一性只针对满足特定条件的数据。

例如软删除用户：

user

- id
- email
- deleted_at

业务规则：

当前有效用户的 email 不能重复，但已删除用户不占用 email。

真正的规则是：

deleted_at IS NULL
→ email 必须唯一

deleted_at IS NOT NULL
→ 不参与唯一性限制

SQLite / PostgreSQL 可以通过部分唯一索引实现：

sql

CREATE UNIQUE INDEX idx_user_email_active
ON user (email)
WHERE deleted_at IS NULL;

注意这与：

UNIQUE(email, deleted_at)

不是同一个概念。

前者是：

先根据条件过滤记录，再保证指定字段唯一。

CHECK 检查约束

CHECK 用于限制字段值必须满足指定条件。

范围限制

sql

CHECK (price >= 0)

sql

CHECK (age >= 0 AND age <= 150)

固定值范围

例如订单状态只能是：

pending
paid
cancelled

可以：

sql

CHECK (status IN ('pending', 'paid', 'cancelled'))

CHECK 与 NOT NULL

两者职责不同：

NOT NULL
→ 是否必须有值

CHECK
→ 值是否合法

例如：

sql

age INTEGER CHECK (age >= 0)

仍可能允许：

age = NULL

如果要求必须填写且不能小于 0：

sql

age INTEGER NOT NULL CHECK (age >= 0)

因此不要把 CHECK 当成 NOT NULL 使用。

DEFAULT 默认值

DEFAULT 表示插入记录时，如果没有提供字段，则自动使用默认值。

例如：

sql

stock INTEGER NOT NULL DEFAULT 0

sql

status TEXT NOT NULL DEFAULT 'active'

SQLite 当前时间：

sql

created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP

DEFAULT 与 NULL

DEFAULT 只在没有提供字段时生效。

例如：

sql

status TEXT DEFAULT 'active'

未提供：

sql

INSERT INTO user (username)
VALUES ('Tom');

得到：

status = 'active'

显式提供：

sql

status = NULL

不会自动替换成默认值。

因此：

DEFAULT
→ 未提供字段时使用什么值

NOT NULL
→ 是否允许最终值为 NULL

常见组合：

sql

status TEXT NOT NULL DEFAULT 'active'

表示：

字段必须有值；未指定时使用 active。

PRIMARY KEY 主键

主键用于唯一标识一条记录。

例如：

user

- id PRIMARY KEY
- email UNIQUE

虽然 id 和 email 都具有唯一性，但语义不同：

id
→ 记录身份标识

email
→ 业务字段，只是业务要求不能重复

因此：

主键一定具有唯一性，但唯一字段不一定应该作为主键。

自然主键与代理主键

自然主键

直接使用具有业务意义的字段作为主键：

email PRIMARY KEY

这种字段称为自然主键（Natural Key）。

代理主键

增加一个与业务内容无直接关系的字段作为主键：

id PRIMARY KEY
email UNIQUE

这种主键称为代理主键（Surrogate Key）。

业务系统通常更倾向使用稳定的代理主键：

id
→ 负责稳定标识记录

email / phone / sku_code
→ 负责业务唯一性

原因是业务字段可能变化。

例如：

email:
a@example.com
↓
b@example.com

如果其他表引用的是：

user.id

修改邮箱不会影响表之间的关系。

自然主键并非错误设计，是否使用仍取决于业务特征。

FOREIGN KEY 外键约束

外键用于保证表之间的引用完整性。

例如：

user

- id

article

- id
- user_id

关系：

article.user_id → user.id

外键约束保证：

article.user_id 引用的用户必须真实存在。

SQLite 示例：

sql

FOREIGN KEY (user_id) REFERENCES user(id)

SQLite 需要确保当前数据库连接启用了外键约束：

sql

PRAGMA foreign_keys = ON;

外键不仅表达表之间存在关系，还负责防止产生无效引用。

联合外键

有时引用的不是单个字段，而是父表中必须同时成立的字段组合。

例如选课必须对应一条真实开课记录：

course_offering

- course_id
- semester

enrollment

- course_id
- semester

父表：

sql

PRIMARY KEY (course_id, semester)

子表：

sql

FOREIGN KEY (course_id, semester)
REFERENCES course_offering (course_id, semester)

允许：

course_offering 已有 (课程1, 2026春)
enrollment (课程1, 2026春)

不允许：

enrollment (课程1, 2026秋)

(课程1, 2026秋) 这组引用不存在。

被引用字段必须共同构成主键或唯一约束。两侧字段个数相同、类型兼容，按书写顺序对应，不按字段名对应。父表唯一约束的字段顺序也要与 REFERENCES 中的顺序一致。

这与多个独立外键不同：

sql

FOREIGN KEY (course_id) REFERENCES course(id)
FOREIGN KEY (semester) REFERENCES semester(code)

独立外键只分别保证课程存在、学期存在。联合外键保证的是组合本身存在。

核心：

联合外键约束的是「这一组引用必须真实存在」，不是每个字段各自存在。

默认情况下，组合里任意一个字段为 NULL，这条外键不检查。

(课程1, NULL)
→ 通过，即使没有对应开课记录

组合必须完整时，引用字段使用 NOT NULL。

PostgreSQL 可用 MATCH FULL：要么全部为 NULL，要么全部非空且组合存在。SQLite 不执行 MATCH，始终按上述默认规则处理。

删除和更新策略作用于整组引用。ON DELETE SET NULL 会把参与该外键的字段都置空，这些字段必须允许 NULL。

单字段主键不能表达「引用必须落在同一业务范围内」时，仍然使用联合外键。

例如员工只能属于本租户的部门：

department

- id PRIMARY KEY
- tenant_id

employee

- tenant_id
- department_id

父表需要：

sql

UNIQUE (tenant_id, id)

子表：

sql

FOREIGN KEY (tenant_id, department_id)
REFERENCES department (tenant_id, id)

FOREIGN KEY (department_id) REFERENCES department(id) 只能保证部门存在，不能阻止引用其他租户的部门。id 已是主键时，仍要单独声明 UNIQUE (tenant_id, id)，外键才能引用这个组合。

删除与引用完整性

删除被其他数据引用的记录时，需要明确关联数据如何处理。

常见策略：

RESTRICT / NO ACTION
CASCADE
SET NULL

RESTRICT

存在关联数据时，禁止删除父记录。

适用于：

子数据不能因为父数据被删除而随意消失，同时又要求父子关系保持有效。

例如某些情况下：

分类仍存在商品
→ 禁止直接删除分类
→ 必须先迁移或处理商品

CASCADE

删除父记录时，同时删除依赖它的子记录。

适用于：

父记录不存在后，子记录也完全失去业务意义。

例如：

order
↓
order_item

如果业务允许物理删除订单：

删除 order
→ 自动删除对应 order_item

SET NULL

删除父记录后保留子记录，但解除关联：

employee.department_id = NULL

适用于：

子实体仍然具有独立存在的意义，只是原来的关系消失。

例如：

删除部门
→ 员工保留
→ department_id = NULL
→ 员工进入“未分配部门”状态

使用 SET NULL 时，对应外键字段必须允许 NULL。

删除策略的判断原则

不要根据表名或关系类型机械选择删除策略。

核心问题是：

父记录消失以后，子记录在业务上应该处于什么状态？

子记录也应该消失
→ CASCADE

父记录不能消失，因为关联数据要求它存在
→ RESTRICT

子记录继续存在，但允许解除关系
→ SET NULL

软删除

数据库中的「删除」不一定意味着物理 DELETE。

对于需要保留历史身份、业务关系、审计数据的实体，可以使用软删除。

常见设计：

deleted_at = NULL
→ 正常数据

deleted_at != NULL
→ 已删除数据

也可以使用：

is_deleted

但 deleted_at 还能记录删除时间，通常信息量更高。

例如用户注销：

user

- id
- email
- deleted_at

order

- id
- user_id

注销后：

user 记录仍然存在
deleted_at 设置删除时间

order.user_id
→ 仍然引用原 user.id

这样可以同时实现：

用户无法正常使用账号
历史订单继续存在
订单仍能关联原用户
外键关系保持完整

软删除与物理删除的选择

删除数据前应先判断：

这条数据是否真的应该从数据库中消失？

如果需要保留：

历史关系
交易记录
审计信息
原始身份
恢复能力

通常应考虑软删除，而不是直接物理删除。

数据库约束与程序校验

数据库约束和程序校验不是二选一，两者职责不同。

数据库约束

负责维护数据完整性，作为最终防线。

典型规则：

PRIMARY KEY
UNIQUE
NOT NULL
FOREIGN KEY
必要的 CHECK

即使程序出现 Bug、并发问题或存在多个数据写入入口，也不能让数据库进入非法状态。

程序校验

更适合：

复杂业务规则
容易变化的规则
流程相关规则
用户体验相关校验

例如：

密码至少 12 位且包含数字
用户每天最多发布 10 篇文章
不同地区具有不同注册年龄要求

两层同时校验

部分规则适合程序提前判断，同时由数据库最终兜底。

例如 email 唯一：

程序：
→ 提前查询 email 是否存在
→ 返回友好的错误信息

数据库：
→ UNIQUE(email)
→ 最终保证不会产生重复数据

程序层的「先查询再插入」不能替代数据库唯一约束。

并发情况下可能出现：

请求 A → 查询 email 不存在
请求 B → 查询 email 不存在

请求 A → INSERT
请求 B → INSERT

只有数据库的唯一约束能够最终保证不会出现重复记录。

约束设计速查

PRIMARY KEY
→ 唯一标识一条记录

NOT NULL
→ 字段必须有值

UNIQUE
→ 字段或字段组合不能重复

联合 UNIQUE
→ 多个字段组成的业务组合不能重复

条件唯一
→ 只对满足特定条件的数据保证唯一

CHECK
→ 字段值必须满足合法范围

DEFAULT
→ 未提供字段时使用默认值

FOREIGN KEY
→ 保证引用的数据真实存在

联合 FOREIGN KEY
→ 多个字段组成的引用必须整体存在

RESTRICT
→ 存在关联数据时禁止删除

CASCADE
→ 父数据删除时一起删除依赖数据

SET NULL
→ 父数据删除后保留子数据并解除关系

软删除
→ 数据保留，通过状态表示业务上的删除

约束设计原则

设计字段和约束时重点判断：

1. 字段是否允许没有值？
2. 哪些字段或字段组合必须唯一？
3. 唯一性是否只针对特定状态的数据？
4. 字段值是否存在明确的合法范围？
5. 未提供字段时是否存在合理默认值？
6. 哪个字段负责稳定标识记录？
7. 业务字段是否可能发生变化？
8. 外键引用的是单个字段，还是必须同时存在的字段组合？
9. 父数据删除后，关联数据应该如何处理？
10. 数据应该物理删除还是软删除？
11. 规则属于数据完整性约束还是业务流程规则？
12. 哪些规则需要程序校验，同时由数据库最终兜底？

字段与约束设计 ​

NULL 与 NOT NULL ​

UNIQUE 唯一约束 ​

联合唯一 ​

条件唯一 ​

CHECK 检查约束 ​

范围限制 ​

固定值范围 ​

CHECK 与 NOT NULL ​

DEFAULT 默认值 ​

DEFAULT 与 NULL ​

PRIMARY KEY 主键 ​

自然主键与代理主键 ​

自然主键 ​

代理主键 ​

FOREIGN KEY 外键约束 ​

联合外键 ​

删除与引用完整性 ​

RESTRICT ​

CASCADE ​

SET NULL ​

删除策略的判断原则 ​

软删除 ​

软删除与物理删除的选择 ​

数据库约束与程序校验 ​

数据库约束 ​

程序校验 ​

两层同时校验 ​

约束设计速查 ​

约束设计原则 ​

字段与约束设计

NULL 与 NOT NULL

UNIQUE 唯一约束

联合唯一

条件唯一

CHECK 检查约束

范围限制

固定值范围

CHECK 与 NOT NULL

DEFAULT 默认值

DEFAULT 与 NULL

PRIMARY KEY 主键

自然主键与代理主键

自然主键

代理主键

FOREIGN KEY 外键约束

联合外键

删除与引用完整性

RESTRICT

CASCADE

SET NULL

删除策略的判断原则

软删除

软删除与物理删除的选择

数据库约束与程序校验

数据库约束

程序校验

两层同时校验

约束设计速查

约束设计原则