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

问题描述

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

 创建表canvas_user（
 id整数，
 login_id VARCHAR（50）唯一不为空，
电子邮件VARCHAR（355）唯一不为空，
 name_given VARCHAR（30），
 name_family VARCHAR（30） ，
 name_full VARCHAR（50），
角色canvas_role，
 last_login TIMESTAMP，
主键（id）
）； 
 
创建表问题（
 id SERIAL，
标题VARCHAR（50），
作者VARCHAR（50），
 path TEXT，
编译器VARCHAR（20），
主键（id）
）; 
 
 CREATE TABLE赋值（
 id整数，
标题TEXT NOT NULL，
 points_possible INTEGER NOT NULL，
 problem_id INTEGER，
主键（id），
外键（problem_id）参考问题（id）
）； 
 
 CREATE TABLE提交（
 num SERIAL，
 user_id INTEGER，
 assignment_id INTEGER，
 timestamp TIMESTAMP，
 path TEXT，
 lti_info TEXT []，
主键（num，user_id，assignment_id），
外键（user_id）参考画布用户（id），
外键（assignment_id）参考赋值（id）
）; 
 
 CREATE TABLE更正（
 num INTEGER，
 user_id INTEGER，
 assignment_id INTEGER，
 timestamp TIMESTAMP，
 path TEXT，
执行时间间隔，
主键（num，user_id，assignment_id），
外键（num）参考提交（num），
外键（user_id）参考提交（user_id），
 FOREIGN KEY（assignment_id）参考提交（assignment_id）
）; 
  

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

错误：对于给定
引用表 submission的键，没有唯一的约束匹配

我对更正表的意图是对每个提交都进行唯一的更正，但是一个提交可以（也可以不）进行更正。

如何解决此错误？是设计上的问题还是只是表声明错误？

解决方案

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

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

 外键（num，user_id，assignment_id）参考提交
  

替换：

   FOREIGN KEY（ num）参考提交（num），
 FOREIGN KEY（user_id）参考提交（user_id），
 FOREIGN KEY（assignment_id）参考提交（assignment_id）   pre> 
 
 
语法的简短形式（参考提交）是可能的，因为您引用的是主键， 
 
 
 
此外，您还可以简化以下操作：将 submission.num 设为sinlge-column主键，从更正中删除​​冗余列 user_id 和 assignment_id 并减少将fk约束限制为（num）-如 @Tim的答案中所述。 
 
 
 
只要您具有多列fk约束，请在每个引用列上考虑 NOT NULL 约束（如@joop评论）。另外，引用列中的一个或多个NULL值允许使用默认 MATCH SIMPLE 行为来逃避fk约束。这可能会也可能不会，通常是 不是 。
 
或者考虑 MATCH FULL 对于多列fk约束，仅在 all 引用列为NULL时才允许。详细信息：
 
 
 
 
 
 完全匹配与简单匹配 
 
 
 
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
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


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