Thread: Foreign key against a partitioned table

Foreign key against a partitioned table

From

Craig James

Date:

23 August 2016, 23:00:30

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

Re: Foreign key against a partitioned table

From

Igor Neyman

Date:

23 August 2016, 23:07:45

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):

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".

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

Re: Foreign key against a partitioned table

From

Adam Brusselback

Date:

24 August 2016, 00:13:48

I have wondered if there were any plans to enhance fkey support for partitioned tables now that more work is being done on partitioning (I know there has been a large thread on declarative partitioning on hackers, though I haven't followed it too closely).

Foreign keys are all done through triggers on the backend anyways, it does seem totally possible to have it work for partitioned tables if the code is aware that a table is partitioned and it needs to look in all inherited tables as well as the one specified.

Re: Foreign key against a partitioned table

From

Adrian Klaver

Date:

24 August 2016, 01:29:46

On 08/23/2016 01:00 PM, Craig James wrote:
> 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.

I would say it is because of this:

https://www.postgresql.org/docs/9.5/static/sql-createtable.html

" Notes
...

Unique constraints and primary keys are not inherited in the current
implementation. This makes the combination of inheritance and unique
constraints rather dysfunctional.
...
"

>
> 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


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Foreign key against a partitioned table

From

Craig James

Date:

24 August 2016, 05:43:31

On Tue, Aug 23, 2016 at 1:07 PM, Igor Neyman <ineyman@perceptron.com> wrote:

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):

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.

---------------------------------