Re: Foreign key against a partitioned table

On 08/23/2016 01:00 PM, Craig James wrote:
> How do you create a foreign key that references a partitioned table?
>
> I'm splitting a large table "molecules" into 20 partitions, which also
> has an associated "molecular_properties" table. It looks something like
> this (pseudo-code):
>
>     create table molecules(molecule_id    integer primary key,
>                            molecule_data  text,
>                            p              integer);
>
>     foreach $p (0..19) {
>         create table molecules_$p (check(p = $p)) inherits (molecules);
>     }
>
>     create table molecular_properties(molprops_id       integer primary key,
>                                       molecule_id       integer,
>                                       molecular_weight  numeric(8,3));
>     alter table molecular_properties
>       add constraint fk_molecular_properties
>       foreign key(molecule_id)
>       references molecules(molecule_id);
>
>
> (NB: There is no natural way to partition molecules, so the value for p
> is a random number. There is a good reason for partitioning that's not
> relevant to my question...)
>
> When I try to insert something into the molecular_properties table it fails:
>
>     insert or update on table "molecular_properties" violates foreign
>     key constraint "fk_molecular_properties"
>     DETAIL:  Key (molecule_id)=(83147) is not present in table "molecules".
>
>
> This surprised me. Obviously ID isn't in the "molecules" parent table,
> but I guessed that the foreign key would work anyway since the parent
> table is supposed to behave as though it includes all of the child tables.

I would say it is because of this:

https://www.postgresql.org/docs/9.5/static/sql-createtable.html

" Notes
...

Unique constraints and primary keys are not inherited in the current
implementation. This makes the combination of inheritance and unique
constraints rather dysfunctional.
...
"

>
> So how do you create a foreign key on a partitioned table?
>
> I suppose I could partition the molecular_properties table, but that
> would add unnecessary complication to the schema for no reason other
> than the "on delete cascade" feature.
>
> The only other thing I can think of is a delete trigger on each of the
> partition child tables. That would work, but it's a nuisance.
>
> Thanks,
> Craig


--
Adrian Klaver
adrian.klaver@aklaver.com