From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@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):
(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.