如何为不同外键关联的记录实现独立自增序列（Account与Transaction表场景）

要实现每个Account对应的Transaction拥有独立从1开始的自增序列，核心是要构建按外键（account_id）分组的专属序列，而不是全局自增主键。下面针对主流数据库给出具体实现方案：

1. 核心表结构逻辑 #

不管用哪种数据库，都需要两张表：

• Account表：存储账户基础信息，唯一id作为主键
• Transaction表：存储交易记录，需要包含：
  • 全局唯一的主键（比如transaction_id）
  • 关联Account的外键account_id
  • 每个账户独立的自增序号（比如per_account_seq）
  • 交易相关字段（金额、时间等）
  • 必须添加UNIQUE(account_id, per_account_seq)约束，确保每个账户的序号不会重复

2. PostgreSQL 实现方案 #

第一步：创建Account表 #

CREATE TABLE Account (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    balance NUMERIC(12,2) DEFAULT 0.00
);

第二步：创建Transaction表 #

CREATE TABLE Transaction (
    transaction_id SERIAL PRIMARY KEY, -- 全局唯一主键
    account_id INT NOT NULL REFERENCES Account(id),
    per_account_seq INT NOT NULL, -- 账户专属自增序号
    amount NUMERIC(12,2) NOT NULL,
    transaction_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(account_id, per_account_seq) -- 防止序号重复
);

第三步：创建触发器实现自动生成序号 #

为了避免并发插入时的序号冲突，推荐用UPDATE原子操作来维护序号（比直接查最大值更可靠）：
首先给Account表添加一个字段维护当前最大交易序号：

ALTER TABLE Account ADD COLUMN last_transaction_seq INT DEFAULT 0;

然后创建触发器函数：

CREATE OR REPLACE FUNCTION generate_per_account_seq()
RETURNS TRIGGER AS $$
BEGIN
    -- 原子更新Account表的序号并返回新值
    UPDATE Account
    SET last_transaction_seq = last_transaction_seq + 1
    WHERE id = NEW.account_id
    RETURNING last_transaction_seq INTO NEW.per_account_seq;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

最后绑定触发器到Transaction表：

CREATE TRIGGER trigger_transaction_seq
BEFORE INSERT ON Transaction
FOR EACH ROW
EXECUTE FUNCTION generate_per_account_seq();

3. MySQL 实现方案 #

第一步：创建Account表 #

CREATE TABLE Account (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    balance DECIMAL(12,2) DEFAULT 0.00
);

第二步：创建Transaction表 #

CREATE TABLE Transaction (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY,
    account_id INT NOT NULL,
    per_account_seq INT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    transaction_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES Account(id),
    UNIQUE KEY (account_id, per_account_seq) -- 约束唯一
);

第三步：创建触发器自动生成序号 #

DELIMITER //
CREATE TRIGGER trigger_transaction_seq BEFORE INSERT ON Transaction
FOR EACH ROW
BEGIN
    -- 获取当前账户的最大序号，加1作为新序号
    SELECT COALESCE(MAX(per_account_seq), 0) + 1 INTO NEW.per_account_seq
    FROM Transaction WHERE account_id = NEW.account_id;
END //
DELIMITER ;

注意：MySQL的这个方案在高并发场景下可能出现序号冲突，建议配合应用层重试机制，或者改用基于Account表维护序号的方式（类似PostgreSQL的方案）。

4. SQL Server 实现方案 #

第一步：创建Account表 #

CREATE TABLE Account (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    balance DECIMAL(12,2) DEFAULT 0.00
);

第二步：创建Transaction表 #

CREATE TABLE Transaction (
    transaction_id INT IDENTITY(1,1) PRIMARY KEY,
    account_id INT NOT NULL FOREIGN KEY REFERENCES Account(id),
    per_account_seq INT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    transaction_time DATETIME DEFAULT GETDATE(),
    UNIQUE(account_id, per_account_seq)
);

第三步：创建INSTEAD OF触发器生成序号 #

CREATE TRIGGER trigger_transaction_seq
ON Transaction
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO Transaction (account_id, per_account_seq, amount, transaction_time)
    SELECT 
        i.account_id,
        -- 计算当前账户的新序号
        COALESCE((SELECT MAX(per_account_seq) FROM Transaction WHERE account_id = i.account_id), 0) + 1,
        i.amount,
        ISNULL(i.transaction_time, GETDATE())
    FROM inserted i;
END;

并发场景注意事项 #

• 不管用哪种方案，UNIQUE(account_id, per_account_seq)约束是必须的，它能作为最后一道防线防止重复序号
• 基于Account表维护序号的方式（PostgreSQL方案）比直接查询Transaction表最大值的并发安全性更高，因为UPDATE操作是原子的
• 高并发场景下，建议应用层捕获唯一约束冲突的异常，进行重试操作

内容的提问来源于stack exchange，提问作者Renaldinho69