Foreign keys referring other foreign keys in PostgreSQL

Question

In PostgreSQL I have a database, which I intend to make the following table declaration:

CREATE TABLE canvas_user (
    id INTEGER,
    login_id VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(355) UNIQUE NOT NULL,
    name_given VARCHAR(30),
    name_family VARCHAR(30),
    name_full VARCHAR(50),
    role canvas_role,
    last_login TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE TABLE problem (
    id SERIAL,
    title VARCHAR(50),
    author VARCHAR(50),
    path TEXT,
    compiler VARCHAR(20),
    PRIMARY KEY (id)
);

CREATE TABLE assignment (
    id INTEGER,
    title TEXT NOT NULL,
    points_possible INTEGER NOT NULL,
    problem_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY (problem_id) REFERENCES problem(id)
);

CREATE TABLE submission (
    num SERIAL,
    user_id INTEGER,
    assignment_id INTEGER,
    timestamp TIMESTAMP,
    path TEXT,
    lti_info TEXT[],
    PRIMARY KEY(num, user_id, assignment_id),
    FOREIGN KEY (user_id) REFERENCES canvas_user(id),
    FOREIGN KEY (assignment_id) REFERENCES assignment(id)
);

CREATE TABLE correction (
    num INTEGER,
    user_id INTEGER,
    assignment_id INTEGER,
    timestamp TIMESTAMP,
    path TEXT,
    execution_time interval,
    PRIMARY KEY(num, user_id, assignment_id),
    FOREIGN KEY (num) REFERENCES submission(num),
    FOREIGN KEY (user_id) REFERENCES submission(user_id),
    FOREIGN KEY (assignment_id) REFERENCES submission(assignment_id)
);

Everything works fine, except for the following error at the creation of the last table (correction):

ERROR: there is no unique constraint matching given keys for referenced table "submission"

What I intend with the correction table is to have an unique correction for each submission but a submission can have (or not) a correction.

How can I solve this error? Is it a problem of design or just a table declaration mistake?

Answer 1

Foreign key constraint do not care whether referenced columns reference another column themselves. But the referenced column(s) must be unique. That's what the error message tells you.

FK constraints can be based on multiple columns. This should work:

FOREIGN KEY (num, user_id, assignment_id) REFERENCES submission

Replacing:

FOREIGN KEY (num) REFERENCES submission(num),
FOREIGN KEY (user_id) REFERENCES submission(user_id),
FOREIGN KEY (assignment_id) REFERENCES submission(assignment_id)

The short form of the syntax (REFERENCES submission) is possible because referencing the primary key is the default.

Plus, you can simplify: make submission.num the single-column primary key, drop the redundant columns user_id and assignment_id from correction and reduce the FK constraint to just (num) - as discussed in Tim's answer.

With a multicolumn FK constraint, consider NOT NULL constraints on each of the referencing columns (as joop commented). Else, one or more null values in the referencing columns allow to escape the FK constraint with the default MATCH SIMPLE behavior. This may or may not be intended, typically it is not.
Alternatively consider MATCH FULL for multicolumn fk constraints to only allow that if all referencing columns are null. See:

• MATCH FULL vs MATCH SIMPLE