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?

Add NOT NULL to user_id and assignment_id in table submission.(all elements of (composite) primary keys must be NOT NULLable) — joop, Aug 21 '14 at 15:45
@otorillas: Why don't you define a multi-column foreign key constraint on your `num`, `user_id`, and `assignment_id` attributes in the [DDL](http://en.wikipedia.org/wiki/Data_definition_language) statement for you `correction` table like so: `FOREIGN KEY(num, user_id, assignment_id) REFERENCES submission(num, user_id, assignment_id)`? EDIT: Pardon, it seems that @ErwinBrandstetter already beat me to it with a more through answer. :) — Priidu Neemre, Aug 21 '14 at 15:49
Oops, failed to scroll and didn't see the correction table. NOT NULL is still needed, though. — joop, Aug 21 '14 at 15:53
@joop: I tend to agree, but there is more to it. I added a chapter to my answer. — Erwin Brandstetter, Aug 21 '14 at 19:56

score 4 · Accepted Answer · edited May 23 '17 at 10:30

4

A foreign key constraint does not care whether the referenced column(s) is referencing another column itself. But the referenced column(s) must be unique. That's what the error message tells you (quite clearly).

What you are missing is that a foreign key constraint 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 you are referencing the primary key, which is the default.

Plus, you can simplify: make submission.num the sinlge-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.

As long as you have the multicolumn fk constraint, consider NOT NULL constraints on each of the referencing columns (as commented by @joop). Else, one or more NULL values in the referencing columns allow to escape the fk constraint with the default MATCH SIMPLE behaviour. 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. Details:

MATCH FULL vs MATCH SIMPLE

edited May 23 '17 at 10:30

Community

1
1

answered Aug 21 '14 at 15:46

Erwin Brandstetter

605,456
145
1,078
1,228

Thanks, it worked! I tried first to do: FOREIGN KEY (num, user_id, assignment_id) REFERENCES submission(num, user_id, assignment_id) but it didn't work. – otorrillas Aug 21 '14 at 15:53
1

@otorrillas: Hmm... the constraint definition should be valid either way. Check your statement for any unanticipated syntax errors (bare in mind that Erwin Branstetter's shorter form looks more elegant, though). – Priidu Neemre Aug 21 '14 at 15:57
@otorrillas: This time, Priidu beat me to it, I was about to comment the same. The explicit form may even be preferable for clarity. – Erwin Brandstetter Aug 21 '14 at 15:59
Thanks to both of you for providing such great help. – otorrillas Aug 21 '14 at 16:01
I get your point @ErwinBrandstetter but in my particular case, there are 2 points I want to remark: - Assignment_id and user_id will never be null, as it is a user who makes a submission for an specific problem, so they must exist before uploading a submission. - I don't want to make just submission.num the PK because what I want to store is the number of submissions that a user have for a specific problem. – otorrillas Aug 22 '14 at 09:48
@otorrillas: OK, in this case, make `num` an `integer`, not a `serial`. – Erwin Brandstetter Aug 22 '14 at 15:27
@ErwinBrandstetter: and then, how do I auto-increment it? – otorrillas Aug 22 '14 at 16:37
@otorrillas: It depends on what `number of submissions that a user have for a specific problem` is supposed to mean *exactly*. Please ask a **new question**, comments are not the place. Either way, a `serial` is no good for counting items. For starters: http://stackoverflow.com/questions/24918552/serial-numbers-per-group-of-rows-for-compound-key/24918964#24918964 – Erwin Brandstetter Aug 22 '14 at 17:00

score 3 · Answer 2 · answered Aug 21 '14 at 16:00

Make the foreign key from the correction table to the submission table a compound key that is unique. Also, review the design of the submission table; num is a serial type and should be the unique primary key. You can add a unique constraint to the columns num, user_id, and assignment_id

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, user_id,assignment_id ) REFERENCES submission(num, user_id, assignment_id)

);

Good point concerning the primary key. But once `submission.num` is defined unique, `user_id` and `assignment_id` in `correction` are just redundant and should be removed altogether, reducing the fk to `(num)` alone. — Erwin Brandstetter, Aug 21 '14 at 16:11

Foreign keys referring other foreign keys in PostgreSQL

2 Answers2

Linked