sql - PostgreSQL 错误 : there is no unique constraint matching given keys for referenced table

Question

我有两个表“Persona”(人)和“Dona”(女性)，我想更改第三个名为“Baptisme”(洗礼)的表，但我遇到了一些问题。

这些是我的表格:

CREATE TABLE baptismes.Persona (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(255),
    nom_complementari1 VARCHAR(255),
    nom_complementari2 VARCHAR(255),
    nom_complementari3 VARCHAR(255),
    nom_complementari4 VARCHAR(255),
    cognom1 VARCHAR(255),
    cognom2 VARCHAR(255),
    lloc_naixement VARCHAR(255) REFERENCES baptismes.Poblacio(nom),
    data_naixement baptismes.Data,
    alies VARCHAR(255),
    sexe CHAR(1),
    difunt BOOLEAN
);

CREATE TABLE baptismes.Dona (
    cognom_marit_actual VARCHAR(255),
    cognom_marit_anterior VARCHAR(255)
) INHERITS (baptismes.Persona);

CREATE TABLE baptismes.Baptisme (
    id SERIAL PRIMARY KEY,
    ...
);

Baptisme 有更多属性，但它们在这里并不重要。

我想做的是:

ALTER TABLE baptismes.baptisme
ADD COLUMN mare INTEGER REFERENCES baptismes.Dona(id);

但这给了我以下错误:

ERROR:  no hay restricción unique que coincida con las columnas dadas en la tabla referida «dona»
********** Error **********

ERROR: no hay restricción unique que coincida con las columnas dadas en la tabla referida «dona»
SQL state: 42830

英文:“没有唯一约束与引用表 <> 的给定键匹配”。

我无法理解，因为 Dona 从 Persona (id) 获取 PK，所以它应该是 UNIQUE。

有人可以帮帮我吗？谢谢!

Answer 1

唉，这是一个documented不足:

5.8.1. Caveats

A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint.

• If we declared cities.name to be UNIQUE or a PRIMARY KEY, this would not stop the capitals table from having rows with names duplicating rows in cities. And those duplicate rows would by default show up in queries from cities. In fact, by default capitals would have no unique constraint at all, and so could contain multiple rows with the same name. You could add a unique constraint to capitals, but this would not prevent duplication compared to cities.

所以，您的问题是 dona.id 没有唯一约束。我认为您可以在 create table 语句中声明一个——或者通过之后更改表来声明。