Search Postgresql Archives

Re: Foreign key against a partitioned table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux