foreign key referencing inheritance parent

From: J Lumby <johnlumby(at)hotmail(dot)com>
To: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: foreign key referencing inheritance parent
Date: 2020-07-20 14:44:25
Message-ID: DM6PR06MB5562927953A8FAA81FE792B1A37B0@DM6PR06MB5562.namprd06.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

This has come up before elsewhere e.g.

https://stackoverflow.com/questions/26034752/postgresql-inheritance-and-foreign-key-referencing-parent-table

but I don't see any mention in any pg mailing list.

postgresql permits creation of a foreign key referencing a table which
is the parent of child tables via inheritance

but enforces that every foreign key value must exist as pkey ONLY in the
parent  -  it throws an ERROR otherwise, even if the pkey exists in a
child table.

This is problematic (to me) for three slightly different reasons :

1)   it is surprising.    The expectation is that a reference to
accessing row(s) in a parent table will also search all children,   as
with SELECT,  unless the ONLY keyword is specified.

which then leads to ...

2)    it is inconsistent with,  and less useful than,    the somewhat
similar CHECK clause in a CREATE/ALTER TABLE :

_____________________________________________________

         CHECK ( expression ) [ NO INHERIT ]

A constraint marked with NO INHERIT will not propagate to child tables.

_____________________________________________________

For CHECK,   the default is propagation to child tables unless
explicitly prevented,  which is consistent with SELECT and the ONLY
qualifier.

For FOREIGN KEY there is no optional qualifier to express "propagation" 
(i.e. propagation of the search for primary key) or "ONLY" and the
behaviour is always "ONLY".

3)   I am probably wrong but as far as I can tell this behaviour is not
documented anywhere.

Would there be any interest in providing a choice,   e.g. an optional  [
INHERIT ] on the REFERENCES clause,  in a future release?

Cheers,  John

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message karsten 2020-07-25 21:26:45 plphyton function - return each list value as a row ?
Previous Message Stephen Frost 2020-07-08 17:54:02 Re: Role information table name