如何在PostgreSQL中实现类Jira的动态二级主键？

你要实现的是按项目维度自增的工单编号，类似Jira的项目缩写-序号格式，下面结合你的表结构，提供两种可靠的实现方案：

先修正基础表结构 #

首先，PostgreSQL没有AUTOINCREMENT关键字，替代方案是用SERIAL（旧版兼容）或GENERATED AS IDENTITY（SQL标准）。同时要给project.short加唯一约束，避免项目缩写重复：

-- 项目表
CREATE TABLE project (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(100) NOT NULL,
    short VARCHAR(8) NOT NULL UNIQUE -- 项目缩写唯一
);

-- 工单表（基础结构）
CREATE TABLE ticket (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, -- 全局唯一主键
    project_id INTEGER NOT NULL REFERENCES project(id),
    identifier INTEGER NOT NULL, -- 项目内自增序号
    -- 联合唯一约束：确保同一项目下序号不重复
    CONSTRAINT uq_ticket_project_identifier UNIQUE (project_id, identifier)
);

方案1：每个项目绑定专属序列（推荐，性能优） #

这种方式和Jira的核心逻辑一致，每个项目对应一个独立序列，插入工单时从对应序列取号，并发性能好，无锁竞争问题。

步骤1：自动为项目创建序列 #

可以写一个函数+触发器，在新增项目时自动生成对应序列：

-- 创建函数：新增项目时自动生成专属序列
CREATE OR REPLACE FUNCTION create_project_ticket_sequence()
RETURNS TRIGGER AS $$
BEGIN
    EXECUTE format(
        'CREATE SEQUENCE ticket_seq_%s START 1 INCREMENT 1',
        NEW.id -- 用项目ID命名序列，也可改用short（需注意特殊字符转义）
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到项目表
CREATE TRIGGER trigger_create_project_sequence
AFTER INSERT ON project
FOR EACH ROW EXECUTE FUNCTION create_project_ticket_sequence();

步骤2：插入工单时获取序号 #

插入工单时，通过项目ID调用对应序列的nextval函数：

-- 先插入测试项目
INSERT INTO project (name, short) VALUES ('Test', 'TEST');

-- 插入工单，从项目1的序列取号
INSERT INTO ticket (project_id, identifier)
VALUES (1, nextval('ticket_seq_1'));

-- 多次插入后，identifier会自动递增为1、2、3...

步骤3：生成显示用的完整编号 #

可以用生成列（PostgreSQL 12+支持）直接存储完整编号，避免每次查询拼接：

ALTER TABLE ticket ADD COLUMN ticket_code VARCHAR(16) GENERATED ALWAYS AS (
    CONCAT(
        (SELECT short FROM project WHERE id = project_id),
        '-',
        identifier
    )
) STORED;

之后查询ticket.ticket_code就能直接得到TEST-1、TEST-2这类格式的编号。

方案2：触发器+最大序号计算（适合小型场景） #

如果项目数量少、工单并发插入频率低，可以用触发器自动计算当前项目的最大identifier+1，无需维护多个序列。

步骤1：创建触发器函数 #

CREATE OR REPLACE FUNCTION generate_ticket_identifier()
RETURNS TRIGGER AS $$
BEGIN
    -- 锁定对应项目的工单行，避免并发插入时序号重复
    SELECT COALESCE(MAX(identifier), 0) + 1
    INTO NEW.identifier
    FROM ticket
    WHERE project_id = NEW.project_id
    FOR UPDATE;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤2：绑定触发器到工单表 #

CREATE TRIGGER trigger_set_ticket_identifier
BEFORE INSERT ON ticket
FOR EACH ROW EXECUTE FUNCTION generate_ticket_identifier();

使用方式 #

插入工单时无需指定identifier，触发器会自动填充：

INSERT INTO ticket (project_id) VALUES (1); -- identifier自动设为1
INSERT INTO ticket (project_id) VALUES (1); -- identifier自动设为2

注意事项 #

1. 方案1的序列如需重置（比如项目序号归零），可执行ALTER SEQUENCE ticket_seq_1 RESTART WITH 1;
2. 方案2的FOR UPDATE会锁定对应项目的所有工单行，高并发场景下有性能瓶颈，优先选方案1
3. 项目缩写short必须唯一，否则会出现不同项目生成相同编号的情况

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