From: | Craig James <cjames(at)emolecules(dot)com> |
---|---|
To: | Igor Neyman <ineyman(at)perceptron(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Foreign key against a partitioned table |
Date: | 2016-08-24 02:43:24 |
Message-ID: | CAFwQ8rftwWpJ7i4Y9nBMbHbX6FE_g5tR-2XdCEMGYQhBLAwTFg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Aug 23, 2016 at 1:07 PM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:
>
>
> *From:* pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner@
> postgresql.org] *On Behalf Of *Craig James
> *Sent:* Tuesday, August 23, 2016 4:00 PM
> *To:* pgsql-general(at)postgresql(dot)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.
---------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2016-08-24 04:58:43 | Re: Determining table change in an event trigger |
Previous Message | Jonathan Rogers | 2016-08-24 01:38:16 | Determining table change in an event trigger |