外键引用 PostgreSQL 中的其他外键 [英] Foreign keys referring other foreign keys in PostgreSQL

问题描述

在 PostgreSQL 中，我有一个数据库，我打算对它进行以下表声明:

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?

推荐答案

外键约束不关心被引用的列是否引用了另一个列本身.但引用的列必须是唯一的.这就是错误消息告诉您的内容(非常清楚).

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

替换:

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

语法的缩写形式(REFERENCES 提交)是可能的，因为您引用的是默认主键.

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

另外，您可以简化:使 submission.num 成为单列主键，从 user_id 和 assignment_id 中删除冗余列code>correction 并将 fk 约束减少到 (num) - 如 @Tim's 中所述回答.

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.

只要您有多列 fk 约束，请考虑每个引用列上的 NOT NULL 约束(由 @joop 注释).否则，引用列中的一个或多个 NULL 值允许使用默认的 MATCH SIMPLE 行为转义 fk 约束.这可能是有意的，也可能不是有意的，通常是.
或者，考虑多列 fk 约束的 MATCH FULL 以仅在 all 引用列为 NULL 时允许.详情:

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:

• 完整匹配与简单匹配

这篇关于外键引用 PostgreSQL 中的其他外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！