PostgreSQL外键多表引用及工单状态关联约束问题

我正在设计一个工单管理系统的数据库Schema，部分表结构如下：

CREATE TABLE workflow 
(
    workflow_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
    -- 省略其他字段
);

CREATE TABLE ticket_state 
(
    workflow_id BIGINT NOT NULL 
        REFERENCES workflow(workflow_id) ON DELETE CASCADE ON UPDATE CASCADE,
    ticket_state_ordinal INT NOT NULL,
    -- 省略其他字段
    PRIMARY KEY (workflow_id, ticket_state_ordinal)
);

CREATE TABLE project 
(
    project_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    workflow_id BIGINT NOT NULL 
        REFERENCES workflow(workflow_id) ON UPDATE CASCADE
    -- 省略其他字段
);

CREATE TABLE ticket 
(
    project_id BIGINT NOT NULL 
        REFERENCES project(project_id) ON DELETE CASCADE ON UPDATE CASCADE,
    ticket_id BIGINT NOT NULL,
    ticket_state_ordinal INT NOT NULL,
    -- 省略其他字段
    PRIMARY KEY (project_id, ticket_id)
);

我需要添加约束，限制ticket.ticket_state_ordinal的值必须属于该工单关联的project对应的workflow下的ticket_state.ticket_state_ordinal有效值。

我尝试在ticket表中新增workflow_id字段，通过两个外键来保证一致性，但第一个外键创建失败，提示project表中没有workflow_id的唯一索引：

CREATE TABLE ticket 
(
    project_id BIGINT NOT NULL,
    ticket_id BIGINT NOT NULL,
    workflow_id BIGINT NOT NULL, -- 仅用于关联一致性校验
    ticket_state_ordinal INT NOT NULL,
    -- 省略其他字段
    FOREIGN KEY (project_id, workflow_id) 
        REFERENCES project(project_id, workflow_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (workflow_id, ticket_state_ordinal) 
        REFERENCES ticket_state(workflow_id, ticket_state_ordinal) ON UPDATE CASCADE,
    PRIMARY KEY (project_id, ticket_id)
);

解决方案 #

方案一：给project表添加唯一约束/索引

PostgreSQL要求外键引用的列组合必须是唯一约束或主键，虽然project表中project_id是主键，(project_id, workflow_id)天然唯一，但需要显式创建索引或约束来支持外键：

-- 添加唯一约束（语义更清晰，推荐使用）
ALTER TABLE project ADD CONSTRAINT uq_project_projectid_workflowid UNIQUE (project_id, workflow_id);

-- 或者创建唯一索引，效果等价
-- CREATE UNIQUE INDEX idx_project_projectid_workflowid ON project(project_id, workflow_id);

添加完成后，再创建ticket表的两个外键即可生效，这样就能同时保证：

• ticket.workflow_id与关联project的workflow_id完全一致
• ticket.ticket_state_ordinal属于对应workflow下的有效状态值

方案二：使用触发器实现（无需冗余存储workflow_id）

如果不想在ticket表中冗余workflow_id字段，可以通过触发器来完成校验：

1. 创建校验函数：

CREATE OR REPLACE FUNCTION validate_ticket_state()
RETURNS TRIGGER AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM project p
        JOIN ticket_state ts ON p.workflow_id = ts.workflow_id
        WHERE p.project_id = NEW.project_id
          AND ts.ticket_state_ordinal = NEW.ticket_state_ordinal
    ) THEN
        RAISE EXCEPTION '无效的工单状态：该项目对应的工作流中不存在此状态序号';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 给ticket表绑定触发器：

CREATE TRIGGER trg_validate_ticket_state
BEFORE INSERT OR UPDATE ON ticket
FOR EACH ROW EXECUTE FUNCTION validate_ticket_state();

这种方式不需要冗余字段，每次插入/更新工单时都会自动查询校验状态有效性，但相比外键约束，触发器的性能略低，更适合数据量不大的业务场景。

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