PostgreSQL foreign key constraint violation with inheritance

Question

I have a PostgreSQL database, 3 tables and my schema as follows

--- Parent Table

CREATE TABLE IF NOT EXISTS abc.parent(
    record_id         SERIAL PRIMARY KEY,
    description       text NOT NULL
);

--- Child Table


CREATE TABLE IF NOT EXISTS abc.child ( 
    total_count INT NOT NULL)  INHERITS (abc.parent);

-- Detail

CREATE TABLE abc.detail(
    detail_id      int NOT NULL,
    detail_description       text NOT NULL
    record_id         int NOT NULL,
    FOREIGN KEY (record_id) REFERENCES abc.parent(record_id)
);

Then i insert records in to both Parent and Child tables .

Parent

|record_id|description|
|1        |abcd       |
|2        |efgh       |

child

|record_id|description|total_count|
|3        |xygh       |5          |
|4        |mnop       |7          |

When I try to insert record into detail table following two entires was success

Detail
|detail_id|detail_description|record_id|
|100      |detail_desc1      |  1      |
|200      |detail_desc2      |  2      |

but I cant insert entry with record_id 3 it gave me a foreign key violation error

Can someone explain this error ??

Can we create foreign key relationship like this in Postgresql with inheritance

Howard Elton · Accepted Answer · 2018-10-02 11:33:24Z

3

Sorry for the late answer. As the other answer states, Constraints are not inherited, so in your case parent has a primary key on record_id, but the child table does not have the same constraint. Without a primary key, a foreign key cannot point to it.

This is easily be fixed though by adding a primary key constraint to the child table also. The structure below would work fine:

CREATE TABLE IF NOT EXISTS abc.parent
(
    record_id         SERIAL,
    description       text NOT NULL,
    CONSTRAINT parent_pkey PRIMARY KEY (record_id)
);

--- Child Table

CREATE TABLE IF NOT EXISTS abc.child 
( 
    total_count INT NOT NULL,
    CONSTRAINT child_pkey PRIMARY KEY (record_id)
)  INHERITS (abc.parent);

-- Detail

CREATE TABLE abc.detail
(
  detail_id integer NOT NULL,
  detail_description text NOT NULL,
  record_id integer NOT NULL,
  CONSTRAINT detail_pkey PRIMARY KEY (detail_id),
  CONSTRAINT detail_record_id_fkey FOREIGN KEY (record_id)
      REFERENCES abc.parent (record_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

This will work perfectly and allow child records to be referenced in the detail table. Note that I also added a primary to to the detail table, as good practice (although it did not contribute to answering your question).

The interesting thing about this, is that you are creating a constraint on column that is not mentioned anywhere in the CREATE TABLE statement. This is the nature of inheriting columns, but might not be obvious at first.

As of version 10 of PostgresSQL, constraint inheritance is not supported, so constraints (Unique, primary keys, foreign keys etc) must be recreated on child tables. I hope they fix this issue soon.

edited Oct 2, 2018 at 11:33

answered Oct 2, 2018 at 11:25

Howard Elton

1651 silver badge7 bronze badges

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

1 Comment

thiagogcm Over a year ago

I've run your code and then executed the following:

INSERT INTO abc.child     (description, total_count)     VALUES('xpto', 0);     INSERT INTO abc.detail     (detail_id, detail_description, record_id)     VALUES(0, '', 1);

but got this error message:

> SQL Error [23503]: ERROR: insert or update on table "detail" violates > foreign key constraint "detail_record_id_fkey"   Detalhe: Key > (record_id)=(1) is not present in table "parent"

Any idea of what I did wrong? I'm using Postgres 12.

Laurenz Albe · Accepted Answer · 2017-03-11 05:03:33Z

1

No, that won't work.

Like the documentation says:

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.

“Global indexes” are one of the important missing features of table inheritance.

answered Mar 11, 2017 at 5:03

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Collectives™ on Stack Overflow

PostgreSQL foreign key constraint violation with inheritance

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related