外键引用PostgreSQL中的其他外键

Question

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

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)
);

一切正常，除了在创建最后一个表（更正）时出现以下错误：

错误：没有唯一的约束匹配给定键的引用表“提交”

我对更正表的打算是对每个提交都有唯一的更正，但是一个提交可以（也可以不）具有更正。

我该如何解决这个错误？是设计问题还是表声明错误？

Answer 1

外键约束不关心所引用的列是否在引用另一列本身。但是引用的列 必须 是唯一的。这就是错误消息告诉您的内容（非常清楚）。

您缺少的是外键约束可以基于
多个列
。这应该工作：

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 submission）的简短形式是可能的，因为您要引用主键，这是默认键。

另外，您可以简化：制作submission.numsinlge-
column主键，从中删除冗余列user_id，并将fk约束减小为just-如@Tim的答案中所述。
assignment_id``correction``(num)

只要您具有multicolumn fk约束，请考虑NOT NULL每个引用列上的约束（如@joop所述）。另外，引用列中的一个或多个NULL值允许使用默认MATCH SIMPLE行为来逃避fk约束。这可能是预期的，也可能不是预期的，通常 不是 。
或者，考虑MATCH FULL多列fk约束，以仅在 所有 引用列均为NULL的情况下才允许这样做。细节：

• 完全匹配与简单匹配