Re: [GENERAL] Foreign keys to inherited tables
On 3/20/08, Erik Jones [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- 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 keys to inherited tables
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
--
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 keys to inherited tables
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®
[EMAIL PROTECTED]
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
