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

Question

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!

Gordon Linoff · Accepted Answer · 2015-04-17 16:44:43Z

8

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.

answered Apr 17, 2015 at 16:44

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lyd Over a year ago

But if I declare one in Dona table I could have a dona with id = 10, for example, and a person with same id = 10. Isn't it? And they wouldn't be same person.

Gordon Linoff Over a year ago

@Lyd . . . Yes, that is exactly one of the "Caveats" in the documentation.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related