DAY 30 W5 · 公司立规矩：建模、约束与事务 高频

支付模块上线：把正确性交给数据库

支付模块今天上线，payments 表由你来建。后端说：「别靠应用代码保证正确，数据库要能挡住脏数据。」

学 40 min
练 65 min
盘 15 min
共 120 分钟

学 · 40 min

  1. 01五种约束：PK / FK / UNIQUE / CHECK / NOT NULL

    场景后端说「数据库要能挡脏数据」--挡脏数据的就是这五个门卫。

    各管一件事：PK = 唯一 + 非空（行的身份证）；FK = 引用必须存在（明细指向的订单必须是真订单）；UNIQUE = 列组合不重复；CHECK = 自定义规则（金额 > 0）；NOT NULL = 必填。约束在写入时检查，违反即整条语句失败--脏数据根本进不了库。

    create table payments (
  id      bigint generated always as identity primary key,
  order_id uuid not null references orders(id),
  method  text not null,
  amount  numeric(10,2) not null check (amount > 0),
  status  text not null,
  paid_at timestamp,
  unique (order_id, method)          -- 同一订单同渠道只有一笔
);

    易错约束挡的是「结构性错误」；「业务逻辑错」（把 100 元记成 1000 元）永远要靠流程和评审。

  2. 02外键的级联动作：CASCADE / SET NULL / RESTRICT

    场景删一个用户，他的订单、明细怎么办？建表时就要回答。

    on delete cascade：连带删光子行；set null：子行保留、外键置空（列必须可空）；restrict（默认）：有子行就拒绝删父行。选哪个是业务决策：审计要求高的库默认 restrict，别让一次 delete 引发雪崩。

    -- 实验：删用户，订单连带消失
alter table orders
  drop constraint orders_user_id_fkey,
  add constraint orders_user_id_fkey
    foreign key (user_id) references users(id) on delete cascade;

delete from users where id = '...';   -- orders 里他的订单同时没了

    易错cascade 链会一路传导（订单没了 -> 明细也没了）且静默发生；生产上删数据前先数一下子行有多少。

  3. 03唯一约束 vs 唯一索引的关系

    场景面试题：唯一约束和唯一索引是不是一回事？

    在 PG 里功能等价：建唯一约束时 PG 自动建一个唯一索引来实现它。区别在意图层：约束是声明（表达业务规则），索引是实现（顺带加速）。写 DDL 用约束语义更清晰；已存在的唯一索引也被视作可用的冲突目标（on conflict 能用）。另外 PG 默认多个 NULL 不算重复。

    -- 两种写法效果等价
alter table users add constraint uq_email unique (email);
create unique index idx_email on users (email);

select count(*) from (values (null),(null)) t(x);   -- 两个 null 并存
-- unique 列同理：多个 NULL 行可以共存（PG 15+ 可用 nulls not distinct 改变）

    易错「唯一列可以有多个 NULL」常被当成 bug 上报--其实是 SQL 标准语义。

  4. 04「生产环境该不该用外键」的正反理由

    场景技术评审会上最热闹的一题：后端嫌外键碍事，DBA 嫌没有外键心慌。

    支持：数据库兜底正确性（应用有 bug 也挡得住）、自带文档作用、防止孤儿数据。反对：每次写入多一次引用检查（高频写场景有感）、锁竞争、分库分表后外键根本没法跨库。成熟观点不是站队，是分场景：单库中小规模用；超大规模 / 分库分表靠应用保证 + 定期跑一致性校验脚本。

    易错面试回答这题要给出「在什么规模、什么前提下我会改变结论」--只喊口号不得分。

练 · 65 min

  1. 跑 seed.sql「W5」段灌入支付流水；按页面底部结构建 payments，PK / FK / NOT NULL 一次到位
    参考答案

    约束一次到位：PK、FK、NOT NULL、unique。check 留到第 3 题单独加--seed 埋的负金额订单会留下负流水，先过不了 check，得先把表建起来灌数。

    create table payments (
  id        bigint generated always as identity primary key,
  order_id  uuid not null references orders(id),
  method    int not null,               -- 1~4：支付渠道
  amount    numeric(10,2) not null,
  status    int not null default 1,
  paid_at   timestamp,
  unique (order_id, method)             -- 同一订单同渠道只有一笔：幂等的地基
);

-- 然后跑 seed.sql 的 §E 段（一条 insert ... select）
select count(*) from payments;   -- ≈4 万：每个已支付订单一条
  2. 给前几周建的 7 张表补齐全部 PK 和 FK（还债日）
    参考答案

    加外键会扫一遍子表验证存量（order_items 12 万行，秒级）。加完之后每次写子表都多一次父表引用检查--这就是第 6 题「反方理由」的现场。

    -- PK 建表时基本都有（\d 检查），缺的补：alter table 表名 add primary key (id);
-- 这题的主菜是补外键：
alter table orders      add constraint orders_user_id_fkey
  foreign key (user_id) references users(id);
alter table products    add constraint products_category_id_fkey
  foreign key (category_id) references categories(id);
alter table categories  add constraint categories_parent_id_fkey
  foreign key (parent_id) references categories(id);
alter table order_items add constraint order_items_order_id_fkey
  foreign key (order_id) references orders(id);
alter table order_items add constraint order_items_product_id_fkey
  foreign key (product_id) references products(id);
alter table user_logins add constraint user_logins_user_id_fkey
  foreign key (user_id) references users(id);

-- 验收：列出全库外键
select conrelid::regclass as 子表, pg_get_constraintdef(oid) as 定义
from pg_constraint
where contype = 'f'
order by 1;
  3. 加 CHECK (amount > 0)，插一条负数验证报错
    参考答案

    check 在写入时拦截、整条语句失败；「先有脏数据后加约束」必须先清存量--约束是入库的最后一道门，不是洗数据工具。

    -- 约束对存量数据同样校验：负金额订单留下的负流水先清掉
delete from payments where amount < 0;

alter table payments
  add constraint chk_payments_amount_positive check (amount > 0);

-- 验证：插一条负数
insert into payments(order_id, method, amount, status, paid_at)
values ((select id from orders where status = 2 limit 1), 1, -9.90, 1, now());
-- ERROR: new row for relation "payments" violates check constraint
--        "chk_payments_amount_positive"
  4. 用唯一约束防止「同一用户同一秒重复下单」
    参考答案

    这是 learn「唯一约束 vs 唯一索引」的落地：约束语法不支持表达式，唯一索引可以；反过来 PG 的唯一约束本来就是靠自动建唯一索引实现的。另外 PG 默认多个 NULL 不算重复。

    -- 「同一用户同一秒」是表达式，约束语法写不了，只能用表达式唯一索引
create unique index uq_orders_user_second
  on orders (user_id, date_trunc('second', created_at));

-- 验证：原样复制一笔订单（同用户同时间戳）
insert into orders(user_id, status, total_amount, created_at)
select user_id, 1, 99.00, created_at
from orders
limit 1;
-- ERROR: duplicate key value violates unique constraint "uq_orders_user_second"
  5. 做 ON DELETE CASCADE 实验：删一个用户，看订单是否连带消失
    参考答案

    级联会静默传导：用户没了、订单没了、明细也没了。整个实验包在事务里 rollback--PG 连 DDL 都是事务性的（D33 正式讲）。生产删父行前先数一下子行有多少。

    begin;

create temp table victim as
select user_id from orders group by 1 order by count(*) limit 1;

-- 外键改成级联（order_items 不改的话 delete 会被它挡住--那就是 RESTRICT 的现场）
alter table orders
  drop constraint orders_user_id_fkey,
  add constraint orders_user_id_fkey
    foreign key (user_id) references users(id) on delete cascade;
alter table order_items
  drop constraint order_items_order_id_fkey,
  add constraint order_items_order_id_fkey
    foreign key (order_id) references orders(id) on delete cascade;

delete from users where id = (select user_id from victim);

select count(*) as 该用户剩余订单数
from orders
where user_id = (select user_id from victim);   -- 0：订单连带没了

rollback;   -- 实验整体撤销，库不留痕
  6. 写下「生产用外键」的支持理由 3 条、反对理由 3 条
    参考答案

    支持：① 数据库兜底正确性，应用有 bug 也挡得住孤儿数据；② 自带文档作用，\d 就能看到表间关系；③ 优化器可借外键信息做 join 消除等优化。反对：① 写子表多一次引用检查，高频写场景有感；② 引入额外的锁竞争；③ 分库分表后外键无法跨库，形同虚设。结论模板：单库中小规模我默认用；到分库分表或极高写入量时，改为应用层保证 + 定期一致性校验脚本。

过关标准 能给出你自己的外键取舍结论，并说明在什么规模下会改变结论。