On Tue, Aug 23, 2016 at 1:07 PM, Igor Neyman <iney...@perceptron.com> wrote:
> > > *From:* pgsql-general-ow...@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): > > > > 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. > OK thanks. Triggers it is. Craig > > > Regards, > > Igor > -- --------------------------------- Craig A. James Chief Technology Officer eMolecules, Inc. ---------------------------------
