Re: [GENERAL] Foreign key against a partitioned table

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Craig James
Sent: Tuesday, August 23, 2016 4:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Foreign key against a partitioned table

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.

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


You can’t.
Only through triggers as you suggested.

Regards,
Igor