Thread: Foreign keys to inherited tables
Hello, I was wondering, I'm reading that there is no support for foreign keys to inherited (child) tables -- are there any plans on supporting these in the (near) future, and/or are there any practical workarounds for this ? Regards, Leon Mergen
Leon Mergen wrote:
> Hello,
>
> I was wondering, I'm reading that there is no support for foreign keys
> to inherited (child) tables -- are there any plans on supporting these
> in the (near) future, and/or are there any practical workarounds for
> this ?
>
This has worked well for me:
CREATE TABLE child_table (
...
) INHERITS (parent_table);
ALTER TABLE child_table ALTER COLUMN id SET DEFAULT
nextval('parent_table_id_seq');
CREATE UNIQUE INDEX child_table_pk ON child_table (id);
Note that it's not necessary to declare an id column for the child.
b
On Mar 19, 2008, at 10:42 PM, brian wrote:
> Leon Mergen wrote:
>> Hello,
>> I was wondering, I'm reading that there is no support for foreign
>> keys
>> to inherited (child) tables -- are there any plans on supporting
>> these
>> in the (near) future, and/or are there any practical workarounds for
>> this ?
>
> This has worked well for me:
>
> CREATE TABLE child_table (
> ...
> ) INHERITS (parent_table);
>
> ALTER TABLE child_table ALTER COLUMN id SET DEFAULT
> nextval('parent_table_id_seq');
>
> CREATE UNIQUE INDEX child_table_pk ON child_table (id);
>
> Note that it's not necessary to declare an id column for the child.
I think he's talking about foreign keys from a "partitioned table",
i.e. a parent and all of its child tables, to another table. That
would, at first, sound simple, but scenarios like this make it tricky
as something to be handled automatically in a simple way:
Say you have table A that references table B. You then partition
table A. Say this carries down the references to table B to each
child of table A. You then partition table B. How do you know, or
rather how does Postgres know, how to change those foreign keys?
It's entirely possible that the partitioning scheme on table B doesn't
match that of table C.
One solution (and, probably the most sane that I can think of) is to
NOT explicitly carry the foreign keys down to the child tables and,
instead, to have the actual foreign key checks follow inheritance
chain. However, with just that most people probably wouldn't want
that as that could seriously kill performance of even simple write
queries. Following that up with making foreign key checks "constraint
exclusion aware" could help there but, at this point, you can probably
see why a sane implementation of this probably wouldn't be considered
low hanging fruit.
For practical workarounds, you can use triggers on your child tables
to implement referential integrity checks customized to your
particular setup.
Erik Jones
DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
On 3/20/08, Erik Jones <erik@myemma.com> wrote: > I think he's talking about foreign keys from a "partitioned table", > i.e. a parent and all of its child tables, to another table. That > would, at first, sound simple, but scenarios like this make it tricky > as something to be handled automatically in a simple way: Well, I was actually talking about foreign keys /to/ a partitioned table -- table A points to partitioned table B, which has childs C and D. The foreign key will only be checked in table B, and not tables C and D. > Say you have table A that references table B. You then partition > table A. Say this carries down the references to table B to each > child of table A. You then partition table B. How do you know, or > rather how does Postgres know, how to change those foreign keys? > It's entirely possible that the partitioning scheme on table B doesn't > match that of table C. > > One solution (and, probably the most sane that I can think of) is to > NOT explicitly carry the foreign keys down to the child tables and, > instead, to have the actual foreign key checks follow inheritance > chain. However, with just that most people probably wouldn't want > that as that could seriously kill performance of even simple write > queries. Following that up with making foreign key checks "constraint > exclusion aware" could help there but, at this point, you can probably > see why a sane implementation of this probably wouldn't be considered > low hanging fruit. > > For practical workarounds, you can use triggers on your child tables > to implement referential integrity checks customized to your > particular setup. Yeah I was thinking about a bunch of triggers too, but was wondering whether there were any other "elegant" solutions for this. The foreign keys are actually already guaranteed by my application logic, so I'm starting to wonder whether this is becoming more trouble to implement than it's worth. Too bad this isn't supported by PostgreSQL (yet). -- Leon Mergen http://www.solatis.com