Re: foreign key referencing inheritance parent

On 07/31/20 03:07, Samed YILDIRIM wrote:
> 20.07.2020, 17:44, "J Lumby" <johnlumby(at)hotmail(dot)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