Re: [GENERAL] Foreign key against a partitioned table

[Craig James]

On Tue, Aug 23, 2016 at 1:07 PM, Igor Neyman  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_idinteger 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.
-

Re: [GENERAL] Foreign key against a partitioned table

[Adrian Klaver]

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_idinteger 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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Foreign key against a partitioned table

[Adam Brusselback]

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: [GENERAL] Foreign key against a partitioned table

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@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_idinteger 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.

Regards,
Igor