Two composite foreign keys with one column in common

I am trying to map the following ER Model into PostgreSQL and I am having trouble with the Issue_Label table. It requires the key from the Issue table, which is id and the key from the Label table, which is the pair (id, project_id).

However, I wanna make sure the project_id from both the Issue table (which is not part of the primary key) and the Label table are the same. I tried the following:

CREATE TABLE issue_label (
    issue_id UUID,
    label_name TEXT,
    project_id UUID,
    FOREIGN KEY (issue_id, project_id) REFERENCES issue(id, project_id),
    FOREIGN KEY (label_name, project_id) REFERENCES label(name, project_id),
    PRIMARY KEY (issue_id, label_name, project_id)
);

but I keep getting this error:

ERROR:  there is no unique constraint matching given keys for referenced table "issue"
SQL state: 42830

How can I fix this, preferably without having to change the Issue table key?

Answer

If:

The key from the Issue table is id, and
The key from the Label table is the pair (id, project_id)

Then the foreign keys should take the form:

CREATE TABLE issue_label (
    issue_id UUID,
    label_name TEXT,
    project_id UUID,
    FOREIGN KEY (issue_id) REFERENCES issue(id),
    FOREIGN KEY (issue_id, project_id) REFERENCES label(id, project_id),
    PRIMARY KEY (issue_id, label_name, project_id)
);

Advertisement

Answer