Foreign key against a partitioned table

From: Craig James <cjames(at)emolecules(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Foreign key against a partitioned table
Date: 2016-08-23 20:00:22
Message-ID: CAFwQ8re__=xcYRzmD+CofHWoUiLZcGU0cWuRejt6Tg3knsGjdw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Igor Neyman 2016-08-23 20:07:30 Re: Foreign key against a partitioned table
Previous Message Igor Neyman 2016-08-23 19:52:00 Re: Forward declaration of table