《Java 编程实战》4.1 表设计：约束、范式与 libraryd 的 schema

本节目标：把 3.3 的 SQL 落到 V1__init.sql，理解三张表、主外键、检查约束和部分唯一索引各自挡住什么错误，并用真实插入失败验证它们。
适用版本：JDK 25、PostgreSQL 17、Flyway 13.11.1。

承接的问题

3.3 的仓储已经能读写图书、会员和借阅，但它默认数据库里有一张形状正确的表。如果 schema 允许 due_on 早于 borrowed_on、允许同一本书出现两条活动借阅、允许罚金为负数，那么每个写入路径都要在 Java 里重复检查，漏掉一处就会留下坏数据。

数据库是 libraryd 的最后一道校验。应用层可以给出更友好的错误消息，但最终能不能写入，应该由数据库说了算。这一节把 schema 定稿，重点不是把三张表画出来，而是回答三个问题：

• 哪些规则适合写进数据库约束，哪些属于业务流程。
• 为什么借阅表需要「部分唯一索引」，而不是普通的唯一约束。
• 表结构怎样支撑第 3 章的仓储映射，又不会把历史数据改坏。

核心机制

三张表：先定形状

V1__init.sql 的完整内容如下：

create table books (
    isbn           varchar(20) primary key,
    title          varchar(200) not null,
    author         varchar(120) not null,
    published_year int not null check (published_year between 1000 and 2100),
    created_at     timestamptz not null default now(),
    updated_at     timestamptz not null default now()
);

create table members (
    id         varchar(40) primary key,
    name       varchar(120) not null,
    created_at timestamptz not null default now()
);

create table loans (
    id          bigserial primary key,
    isbn        varchar(20) not null references books (isbn),
    member_id   varchar(40) not null references members (id),
    borrowed_on date not null,
    due_on      date not null,
    returned_on date,
    renew_count int not null default 0,
    fine_cents  bigint not null default 0,
    created_at  timestamptz not null default now(),
    check (due_on >= borrowed_on),
    check (returned_on is null or returned_on >= borrowed_on),
    check (renew_count between 0 and 1),
    check (fine_cents >= 0)
);

create unique index ux_loans_active_isbn on loans (isbn) where returned_on is null;
create index ix_loans_member_active on loans (member_id, due_on) where returned_on is null;
create index ix_loans_due_active on loans (due_on) where returned_on is null;

先看类型选择，它们决定了后面能存什么、不能存什么。

isbn varchar(20) 和 member_id varchar(40) 是业务标识，不是自增数字。ISBN-13 是 13 位，加上可能的前缀也远小于 20；会员 id 当前是外部传入的字符串，40 个字符足够覆盖常见格式。PostgreSQL 的 varchar(n) 按字符计数，不是字节，所以 title varchar(200) 能存 200 个汉字。这个上限是契约，不是性能优化；它让「标题最多 200 字」在数据库层也有定义。

published_year int 带范围检查。卷一的 Book 构造器已经校验过年份，数据库再检查一次不是重复劳动：应用可能有多个版本、脚本导入、手工修数据，数据库是唯一所有写入都经过的地方。

loans.id bigserial 是 64 位自增主键。bigserial 会创建序列并把默认值设为 nextval，插入时可以不给 id。序列不承诺连续，回滚和失败的插入也会消耗值；3.3 里看到的 id 跳号就是这个原因。用 bigint 而不是 int，是因为借阅表会长期增长，32 位上限不值得冒险。

日期类型分成两类。borrowed_on、due_on、returned_on 是日历日期，用 date；created_at 是事件发生的时间点，用 timestamptz。借期是「14 个日历日」，不是「14 × 24 小时」，用 date 可以避开时区和夏令时。审计时间要能跨时区比较，timestamptz 会按 UTC 存储，读出时再按会话时区展示。

fine_cents bigint 用最小货币单位存罚金。第 3 章的 FinePolicy.dailyFineCents() 返回 50，表示每天 0.50 元。金额不进 numeric 或浮点数，是因为这一层的规则只有整数加分；bigint 的范围远超任何真实罚金。字段名带 _cents 后缀，提醒调用方单位，避免把 50 当成 50 元。

约束：把规则写进数据库

三张表的约束可以按「挡住什么」来读：

本机逐条触发后的真实输出（节选）：

ERROR:  duplicate key value violates unique constraint "books_pkey"
DETAIL:  Key (isbn)=(9780132350884) already exists.

ERROR:  new row for relation "books" violates check constraint "books_published_year_check"
DETAIL:  Failing row contains (9780000000001, Too Old, A, 999, ...).

ERROR:  new row for relation "loans" violates check constraint "loans_check"
DETAIL:  Failing row contains (1, 9780132350884, m-001, 2026-10-17, 2026-10-16, ...).

ERROR:  new row for relation "loans" violates check constraint "loans_renew_count_check"
DETAIL:  Failing row contains (3, 9780132350884, m-001, 2026-10-17, 2026-10-31, null, 2, 0, ...).

ERROR:  insert or update on table "loans" violates foreign key constraint "loans_isbn_fkey"
DETAIL:  Key (isbn)=(9780000000009) is not present in table "books".

ERROR:  duplicate key value violates unique constraint "ux_loans_active_isbn"
DETAIL:  Key (isbn)=(9780132350884) already exists.

这些错误消息直接给出了约束名，仓储可以据此翻译成 ErrorCode。注意 loans_check 和 loans_check1 是 PostgreSQL 自动生成的名字：前两个检查没有显式命名，数据库按表名加序号命名。它们能工作，但名字不可读。如果这张表还要继续演进，可以在后续迁移里用 alter table ... rename constraint 改成 ck_loans_due_after_borrowed 这类名字；V1 保持现状，避免和第 3 章已经写好的映射脱节。

外键没有写 on delete cascade，这是有意的。删一本书时，如果它还有借阅历史，数据库默认拒绝删除，而不是把历史一起清掉。图书下架应该是业务状态，不是物理删除；BookRepository.deleteByIsbn 只适合清理从未被借过的测试数据。真实的下架流程要另外设计，不能靠级联删除省事。

部分唯一索引：活动借阅只能有一条

ux_loans_active_isbn 是这份 schema 里最重要的一个设计：

create unique index ux_loans_active_isbn on loans (isbn) where returned_on is null;

它只对 returned_on is null 的行做唯一性检查。同一本《Clean Code》可以被借出、归还、再借出，历史记录里会有多条 isbn 相同的行；但只要当前还有一条没归还，第二条活动借阅就插不进去。

如果写成普通的 unique (isbn)，一本书归还后永远不能再借。如果只在 Java 里做「先查活动借阅，再插入」，两个并发请求可能同时查到「没有活动借阅」，然后都插入成功。数据库的部分唯一索引把检查和写入合成一个原子动作，应用层不需要自己加锁。

本机验证的顺序是：插入第一条活动借阅成功；同一 ISBN 再插一条，报 ux_loans_active_isbn；把第一条的 returned_on 更新掉；同一 ISBN 再插一条，成功。最后一步是本机输出里的 id = 9，说明历史行没有阻止新的借阅。

另外两个索引也是部分的：

create index ix_loans_member_active on loans (member_id, due_on) where returned_on is null;
create index ix_loans_due_active on loans (due_on) where returned_on is null;

它们只索引活动借阅，因为 libraryd 的高频查询是「某会员当前借了什么」和「哪些书快到期」。历史归还记录也会被查询，但频率低得多，不值得让索引一直背着全部行。4.2 会用 EXPLAIN 验证这两个索引确实被用上。

「活动借阅」这个判定同时出现在三个地方，三处必须字面一致：

• 领域方法 Loan.active() 判断 returnedOn == null。
• 仓储 SQL 写 where returned_on is null。
• 部分索引谓词写 where returned_on is null。

如果 Java 侧改成「未归还且未逾期」，而 SQL 仍然只看 returned_on，两边对「活动」的定义就会分叉；如果索引谓词写成 returned_on is null and due_on >= current_date，查询里的 returned_on is null 又无法匹配这个部分索引。规则可以复杂，但只能有一份定义，其他位置引用它。

范式与有意的取舍

books、members、loans 三张表基本符合第三范式：借阅表只存 isbn 和 member_id，不复制书名、作者、会员名。这样修改一本书的标题不会让历史借阅显示旧标题，也不会出现两份不一致的数据。

有几处是刻意的取舍：

• 不存 status 字段。 借阅状态由 returned_on 和 due_on 推导：returned_on 非空是 RETURNED，为空且 due_on 早于今天是 OVERDUE，否则是 ACTIVE。存一个 status 字段会多一份需要同步的真相。
• 存 fine_cents。 罚金在归还时计算并写入借阅行，而不是每次查询时按当前规则重算。这样规则以后调整，已经结算的历史记录不会变；财务对账需要的是「当时收了多少」，不是「按今天的规则应该收多少」。
• 不建 lookup 表。 图书作者、会员状态在当前规模下都是单值字段，拆成独立的表只会增加连接，不会带来一致性收益。等到作者需要多值、需要独立管理时再拆。
• updated_at 只有默认值。 V1 没有写触发器，更新时由仓储显式设置 updated_at = now()。触发器会把「谁在什么时候改了什么」藏进数据库，当前阶段显式更新更容易排查。

这些取舍的共同标准是：先让规则只有一份真相，再考虑查询方便。反范式化、冗余字段、汇总表都应该在有了真实查询和真实数据量之后再做，而不是在 schema 第一版就猜。

在 libraryd 里落地

迁移文件放在：

libraryd-persistence/src/main/resources/db/migration/V1__init.sql

Flyway 会在应用启动时按版本号执行未应用的迁移。V1 一旦在某个环境执行过，就不能再改内容：Flyway 会校验文件的 checksum，改动会让启动失败。后续变更必须新增 V2__...sql，而不是回头编辑 V1。4.2 的 V2__book_search_index.sql 就是按这个规则加的。

schema 和 3.3 的映射一一对应：

• books.published_year 映射到 Book.year。
• loans.id bigserial 映射到 Loan.id long。
• loans.returned_on date 可空，映射到 Loan.returnedOn LocalDate，null 表示未归还。
• loans.fine_cents 不在 Loan record 里，归还时由 FinePolicy 计算，通过 markReturned(id, returnedOn, fineCents) 写入。ReturnReceiptDto 再把它返回给调用方。

fine_cents 不放进 Loan 是一个有意的边界：Loan 描述借阅状态，罚金是归还动作的结果。把罚金塞进 record 会让 Loan.open 也要带一个无意义的 0，并让「未归还」和「罚金为 0」混在一起。数据库保留这一列，是为了审计和查询，不是为了领域模型。

约束和领域校验是两层，不是重复。Book 构造器检查 year 的范围，published_year 检查也做同样的事；前者给调用方快速失败，后者防止绕过 Java 的写入。两层都保留，因为数据库还会接收迁移脚本、运维手工修复和其他服务的写入。

用系统表核对 schema

迁移执行完不要只看「没有报错」，还要核对数据库里实际生成了什么。PostgreSQL 的 pg_constraint 和 pg_indexes 可以直接读出约束与索引：

select conname, pg_get_constraintdef(oid)
from pg_constraint
where conrelid = 'loans'::regclass
order by conname;

本机输出（节选）：

         conname         |                      pg_get_constraintdef
-------------------------+---------------------------------------------------------------
 loans_check             | CHECK ((due_on >= borrowed_on))
 loans_check1            | CHECK (((returned_on IS NULL) OR (returned_on >= borrowed_on)))
 loans_fine_cents_check  | CHECK ((fine_cents >= 0))
 loans_isbn_fkey         | FOREIGN KEY (isbn) REFERENCES books(isbn)
 loans_member_id_fkey    | FOREIGN KEY (member_id) REFERENCES members(id)
 loans_pkey              | PRIMARY KEY (id)
 loans_renew_count_check | CHECK (((renew_count >= 0) AND (renew_count <= 1)))

索引则用：

select indexname, indexdef
from pg_indexes
where schemaname = 'public' and tablename = 'loans'
order by indexname;

本机输出：

       indexname        |                                                    indexdef
------------------------+-----------------------------------------------------------------------------------------------------------------
 ix_loans_due_active    | CREATE INDEX ix_loans_due_active ON public.loans USING btree (due_on) WHERE (returned_on IS NULL)
 ix_loans_member_active | CREATE INDEX ix_loans_member_active ON public.loans USING btree (member_id, due_on) WHERE (returned_on IS NULL)
 loans_pkey             | CREATE UNIQUE INDEX loans_pkey ON public.loans USING btree (id)
 ux_loans_active_isbn   | CREATE UNIQUE INDEX ux_loans_active_isbn ON public.loans USING btree (isbn) WHERE (returned_on IS NULL)

这两组查询适合放进迁移后的自检脚本。它们验证的不是「SQL 文件写了什么」，而是「数据库实际接受成了什么」；两者之间可能隔着权限、已存在的对象或迁移顺序。

常见错误与边界

「用 timestamp 存审计时间。」 不带时区的 timestamp 在不同会话里表示不同瞬间，跨时区排查时无法还原真实顺序。审计列用 timestamptz；只有「本地日历上的某个日期」才用 date。

「给 isbn 建普通唯一约束。」 普通唯一约束会让一本书归还后无法再次借出，因为历史行仍然占着 ISBN。活动借阅的唯一性必须用部分唯一索引表达，谓词就是 returned_on is null。

「靠先查后插维持唯一。」 两个并发请求可以同时通过检查，然后都插入。数据库约束是唯一可靠的仲裁者；应用层要做的是捕获 23505 并翻译成业务错误，而不是自己造锁。

「给外键加 on delete cascade。」 删除一本书会连带删掉所有借阅历史，财务和审计数据一起消失。默认的 restrict 更安全；需要下架时用业务状态或软删除，不要用物理删除加级联。

「修改已经执行过的 V1__init.sql。」 Flyway 校验失败会阻止启动。正确做法是新增迁移，用 alter table 表达变更；这也是为什么第 4 章把索引放在 V2，而不是回头改 V1。

小结

• books、members、loans 三张表用业务标识做主键，借阅表用 bigserial 做代理主键。
• 日历日期用 date，审计时间用 timestamptz，金额用 bigint 分。
• 检查约束覆盖年份、借期、归还日、续借次数和罚金；外键约束要求借阅指向存在的图书和会员。
• 活动借阅的唯一性由部分唯一索引 ux_loans_active_isbn 约束，它允许历史行重复，只约束未归还的行。
• 不存派生状态，不复制书名，不建无收益的 lookup 表；罚金作为归还结果单独存储。
• 已执行的迁移不可修改，schema 变更用新的 Vn__ 文件表达。

schema 定稿后，下一个问题是这些约束和索引在真实数据量下是否被用上。4.2 会用十万行数据和 EXPLAIN (ANALYZE, BUFFERS) 检查 Seq Scan、Index Scan 与 GIN 索引的选择。

阅读导航：上一节：3.3 数据访问层：把 SQL 装进 Repository · 下一节：4.2 索引与执行计划：读懂 EXPLAIN 。