CODEWITHSUNDEEP
sqlpostgresql
Lyd
Lyd

Reputation: 2096

Postgresql ERROR: there is no unique constraint matching given keys for referenced table

I have two tables "Persona" (Person) and "Dona" (Female) and I want to alter a third one named "Baptisme" (Baptism), but I'm having some problems.

These are my tables:

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 has more attributes but they doesn't matter here.

What I'm trying to do is:

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

but this gives me the following error:

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

In english: "there's no unique constraint matching given keys for referenced table <>".

I can't understand because Dona gets PK from Persona (id), so it should be UNIQUE.

Can somebody help me, please? Thanks!

Upvotes: 3

Views: 5277

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Alas, this is a documented deficiency:

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.

So, your problem is that dona.id doesn't have a unique constraint. I think you can declare one in the create table statement -- or by altering the table afterwards.

Upvotes: 8

Related Questions