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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
