sql - 对于具有多个主键的引用表，没有匹配给定键的唯一约束

Question

我正在尝试链接这两个表，但收到错误消息:

There is no unique constraint matching given keys for referenced table "accomplices".

注意 Robberies 是另一个表。
我用它来创建 Accomplices 表(这是发生错误的时候):

CREATE TABLE info.Accomplices (
RobberID    INTEGER,
BankName    VARCHAR,
City        VARCHAR,
RobberyDate DATE,
Share       DECIMAL NOT NULL,
PRIMARY KEY(RobberID, BankName, City, RobberyDate),
FOREIGN KEY(BankName, City, RobberyDate)
   REFERENCES info.Robberies(BankName, City, RobberyDate)
);

创建 Robbers 表:

CREATE TABLE info.Robbers (
RobberID    INTEGER,
Nickname    VARCHAR,
Age     INTEGER,
NoYears INTEGER,
PRIMARY KEY(RobberID),
FOREIGN KEY(RobberID) REFERENCES info.Accomplices(RobberID),
CONSTRAINT AgeCheck CHECK (Age > NoYears)
);

Robbers 表中的外键是否需要匹配构成Accomplices 表中主键的所有组件？

Answer 1

Does the foreign key in the Robbers table need to match all components that make up the primary key in the Accomplices table?

不完全是。不一定是PK。 FK 约束需要对引用表中的(一组)列进行任何 UNIQUE 或PRIMARY KEY 约束。如果它不是唯一的，则 FK 无法引用它。理论上，您可以向 accomplices 添加一个 UNIQUE 约束:

CREATE TABLE info.Accomplices (
   robberid    integer,
   bankname    varchar,
   city        varchar,
   robberydate date,
   share       decimal not null,
   PRIMARY KEY(robberid, bankname, city, robberydate),
   <b>UNIQUE(robberid)</b>,
   FOREIGN KEY ...
);

.. 从设计的角度来看，这显然没有什么意义，但可以显示 robbers 中给定 FK 约束的要求。

我怀疑您的数据库设计存在逻辑问题。

相关:

• How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
• Foreign key matching in PostgreSQL