我正在设计一个票务管理系统的数据库模式。架构的一部分将包括以下表：

CREATE TABLE workflow 
(
    workflow_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
    -- other columns omitted for brevity
);

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,
    -- other columns omitted for brevity
    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
    -- other columns omitted for brevity
);

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,
    -- other columns omitted for brevity
    PRIMARY KEY (project_id, ticket_id)
);

字符串
是否可以添加一个约束，将ticket.ticket_state_ordinal限制为与ticket.project_id引用的项目相关联的workflow_id的ticket_state.ticket_state_ordinal值？
我尝试向ticket添加一个workflow_id列，如下所示：

CREATE TABLE ticket 
(
    project_id BIGINT NOT NULL,
    ticket_id BIGINT NOT NULL,
    workflow_id BIGINT NOT NULL, -- purely for relational consistency
    ticket_state_ordinal INT NOT NULL,
    -- other columns omitted for brevity
    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)
);

型
以便添加应保持值有效的两个外键。
然而，由于workflow_id上没有唯一索引，这在第一个外键处失败。
我预计这会失败，但无论如何都想尝试一下，以防postgres识别出外键引用整个主键，因此任何额外的列都保证只有1个值可供选择。