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