如何调整 SQL 表列序号并将两列设为 composite PK

2026-10-24支付老李

结论

可以在数据库层实现，不过要分清两件事：

(order_id, payment_id) 可以定义为复合主键。
普通的 AUTO_INCREMENT、IDENTITY 或 SEQUENCE 通常生成全表递增值，无法自动按 order_id 分组，并在每组中从 1 开始计数。

建议把订单内的支付序号命名为 payment_no，主键定义为：

PRIMARY KEY (order_id, payment_no)

payment_no 可以通过事务、行锁和存储过程分配。不要简单使用 COUNT(*) + 1，否则删除记录或出现并发写入时，编号很容易重复。

基本表结构

下面以 PostgreSQL 为例。其他关系型数据库的约束设计基本相同，主要区别在于自动编号的实现语法。

CREATE TABLE orders (
    order_id bigint GENERATED ALWAYS AS IDENTITY,
    PRIMARY KEY (order_id)
);

CREATE TABLE payments (
    order_id bigint NOT NULL,
    payment_no integer NOT NULL,
    amount numeric(12, 2) NOT NULL,

    PRIMARY KEY (order_id, payment_no),

    CONSTRAINT fk_payments_order
        FOREIGN KEY (order_id)
        REFERENCES orders (order_id),

    CONSTRAINT chk_payment_no_positive
        CHECK (payment_no > 0)
);

这套结构可以保证：

同一订单内不会出现重复的 payment_no；
不同订单可以各自拥有 payment_no = 1；
payment 必须属于一个已经存在的 order；
复合主键的索引以 order_id 开头，适合查询某个订单的全部支付记录。

例如：

SELECT *
FROM payments
WHERE order_id = 1424
ORDER BY payment_no;

为什么不能直接使用 AUTO_INCREMENT

如果把 payment_no 定义为普通自增列：

payment_no bigint GENERATED ALWAYS AS IDENTITY

数据库通常会生成全表唯一的连续序号：

order_id  payment_no
1424      1
1424      2
1523      3
1513      4

当 order_id 改变时，编号不会重新从 1 开始。标准自增机制并不了解”每个订单单独编号”这条业务规则。

也不要根据当前记录数计算下一个编号：

SELECT COUNT(*) + 1
FROM payments
WHERE order_id = ?;

假设某个订单已有编号 1、2、3，删除 2 后，COUNT(*) + 1 的结果仍是 3，会和现有记录冲突。即使换成 MAX(payment_no) + 1，两个并发事务也可能算出相同的结果。

方案一：锁定订单后计算 MAX + 1

如果每个订单的 payment 数量不多，可以在同一个事务中锁定对应的订单行，然后计算下一个编号。

BEGIN;

SELECT order_id
FROM orders
WHERE order_id = 1424
FOR UPDATE;

SELECT COALESCE(MAX(payment_no), 0) + 1 AS next_payment_no
FROM payments
WHERE order_id = 1424;

INSERT INTO payments (order_id, payment_no, amount)
VALUES (1424, 4, 99.90);

COMMIT;

这里的重点是 SELECT ... FOR UPDATE。为同一个订单创建 payment 的事务会串行执行，不会同时算出相同的编号。不同订单锁定不同的行，通常仍然可以并发处理。

这些语句必须放在同一个事务中。只执行 MAX(payment_no) + 1 而不加锁，无法避免并发竞争。

还可以把这段逻辑封装成 PostgreSQL 函数：

CREATE OR REPLACE FUNCTION add_payment(
    p_order_id bigint,
    p_amount numeric(12, 2)
)
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
    v_payment_no integer;
BEGIN
    PERFORM 1
    FROM orders
    WHERE order_id = p_order_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order % does not exist', p_order_id;
    END IF;

    SELECT COALESCE(MAX(p.payment_no), 0) + 1
    INTO v_payment_no
    FROM payments AS p
    WHERE p.order_id = p_order_id;

    INSERT INTO payments (order_id, payment_no, amount)
    VALUES (p_order_id, v_payment_no, p_amount);

    RETURN v_payment_no;
END;
$$;

调用方式如下：

SELECT add_payment(1424, 99.90);

数据库会返回这笔 payment 在订单内的编号。

方案二：为每个订单维护计数器

如果 payment 写入频繁，或者不想每次都计算 MAX()，可以单独记录每个订单最后使用的编号：

CREATE TABLE order_payment_counters (
    order_id bigint PRIMARY KEY,
    last_payment_no integer NOT NULL,

    CONSTRAINT fk_counter_order
        FOREIGN KEY (order_id)
        REFERENCES orders (order_id)
);

在 PostgreSQL 中，可以通过原子 UPSERT 取得下一个编号：

INSERT INTO order_payment_counters (order_id, last_payment_no)
VALUES (1424, 1)
ON CONFLICT (order_id)
DO UPDATE
SET last_payment_no =
    order_payment_counters.last_payment_no + 1
RETURNING last_payment_no;

取得编号后，在同一个事务中写入 payment：

BEGIN;

WITH next_number AS (
    INSERT INTO order_payment_counters (order_id, last_payment_no)
    VALUES (1424, 1)
    ON CONFLICT (order_id)
    DO UPDATE
    SET last_payment_no =
        order_payment_counters.last_payment_no + 1
    RETURNING last_payment_no
)
INSERT INTO payments (order_id, payment_no, amount)
SELECT 1424, last_payment_no, 99.90
FROM next_number;

COMMIT;

这种方案有以下特点：

编号分配是原子操作；
不需要扫描已有的 payment；
删除 payment 后不会意外重复使用旧编号；
如果后续插入失败，整个事务会回滚，计数器也会一并回滚。

如果业务要求编号永久不重复，即使事务回滚也不能重用，就需要另外设计不可回滚的编号服务或审计机制，不能只依赖普通事务。

是否一定要使用复合主键

如果 payment 只能通过订单号和订单内序号共同识别，复合主键很合适。不过在实际电商系统中，payment 往往还会被退款、对账、支付网关回调等流程单独引用。此时更常见的做法是使用全局主键，同时保留订单内序号：

CREATE TABLE payments (
    payment_id bigint GENERATED ALWAYS AS IDENTITY,
    order_id bigint NOT NULL,
    payment_no integer NOT NULL,
    amount numeric(12, 2) NOT NULL,

    PRIMARY KEY (payment_id),

    CONSTRAINT uq_payment_number_per_order
        UNIQUE (order_id, payment_no),

    CONSTRAINT fk_payments_order
        FOREIGN KEY (order_id)
        REFERENCES orders (order_id)
);

在这个结构中：

payment_id 是 payment 的全局标识；
payment_no 用于订单内部展示或排序；
UNIQUE (order_id, payment_no) 继续保证同一订单内的编号不重复。

如果其他表需要引用 payment，这种设计通常更方便。外部表只需保存一个 payment_id，不必同时保存两列。

关于列序号

列在表中的物理排列顺序不会影响复合主键的正确性。如果查询结果需要特定的列顺序，应明确写出列名：

SELECT order_id, payment_no, amount
FROM payments;

真正需要关注的是复合索引中的列顺序：

PRIMARY KEY (order_id, payment_no)

这种顺序适合按 order_id 查询，也适合同时按 order_id 和 payment_no 查询，但通常无法高效支持只按 payment_no 查询。如果确实有后一种查询需求，应另外建立索引。

注意事项

使用 MAX(payment_no) + 1 时，必须通过事务和锁处理并发。
不要使用 COUNT(*) + 1 分配编号。
不建议用简单触发器执行 MAX() + 1。触发器如果没有正确加锁，同样会发生并发冲突。
复合主键只能保证唯一性，不能自动生成编号。
金额应使用 numeric 或 decimal，不要使用浮点类型。
payment 通常不宜物理删除。涉及支付审计时，更适合增加 status、voided_at 等字段来保留历史。
如果支付会被退款、对账或网关回调单独引用，优先考虑”全局 payment_id 主键 + (order_id, payment_no) 唯一约束”的结构。