Thread: foreign key referencing inheritance parent

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

On 07/31/20 03:07, Samed YILDIRIM wrote:

 

20.07.2020, 17:44, "J Lumby" <johnlumby@hotmail.com>:

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.

Having of primary key on child table with the same columns doesn't mean that it guarantees uniqueness of a record among parent and child tables. Each primary key provides uniqueness of records only in the table on which they are created.

Of course!   Thanks for pointing that out,  I should have known it as pkeys are not inherited.

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.

https://www.postgresql.org/docs/12/ddl-inherit.html#DDL-INHERIT-CAVEATS

https://www.postgresql.org/docs/12/ddl-partitioning.html  - Title 5.11.3.3. Caveats

Ah I see.  But it would be nice to reference those from CREATE TABLE and ALTER TABLE which is where someone is likely to be.

 

The most important part is why you need to use foreign key referencing to parent table of inheritance. Depending on your answer, there are multiple solution. For example, if the reason why you need it is that you use table partitioning implemented by using triggers and table inheritance, such as in the document below[1], you can switch declarative partitioning with PostgreSQL 12. PostgreSQL 12 supports foreign keys referencing to partitioned tables. [2]

 

[1]: https://www.postgresql.org/docs/12/ddl-partitioning.html#DDL-PARTITIONING-IMPLEMENTATION-INHERITANCE

[2]: https://www.postgresql.org/docs/12/sql-createtable.html

• When establishing a unique constraint for a multi-level partition hierarchy, all the columns in the partition key of the target partitioned table, as well as those of all its descendant partitioned tables, must be included in the constraint definition.
• PRIMARY KEY constraints share the restrictions that UNIQUE constraints have when placed on partitioned tables.

Yes,   we are using inheritance for table partitioning into time ranges,  very similar to the examples in the reference manual.
However,  unfortunately,    the primary key is not the partitioning column,   and I *think* (correct me if wrong) that would disallow defining a referential constraint pointing to a declarative partition-set.    In effect,   what we need is for the referential constraint enforcement to scan all tables of the set in the same way as a SELECT.
Having said that,  I do see that declarative partitioning is much tidier and well-defined than inheritance+trigger so there are probably advantages in changing to that  -   but we would be no better off as regards referential constraints.

Cheers,  John

 

Best regards.

Samed YILDIRIM