On Oct 12, 2009, at 1:21 PM, Erik Jones wrote:
On Oct 9, 2009, at 9:57 AM, Takeichi Kanzaki Cabrera wrote:
Hi everybody, I need your help. I have a hierarchy of tables, and
other table that has a foreign key with the top table of the
hierarchy, can I insert a value into the "other" table where the
value
it reference is not on the parent table? (it's in one of its child)
No, foreign key checks do not (yet) follow inheritance
hierarchies. Here's the specific clause in the manual (http://
www.postgresql.org/docs/8.4/interactive/ddl-inherit.html) that
covers this:
"All check constraints and not-null constraints on a parent table
are automatically inherited by its children. Other types of
constraints (unique, primary key, and foreign key constraints) are
not inherited."
That said, there are ways around this. We're using inheritance to
deal with things like customer "accounts" such as bank accounts,
debit cards, etc. There's stuff that all of these have in common, and
stuff that's specific, so the bank_account and debit_card tables each
inherit from a customer_account table.
customer_account.customer_account_type_id specifies what type of
account a record is. Using that, we have a trigger that you can put
on some other table that's referencing
customer_account.customer_account_id; that trigger implements part of
the functionality of a true foreign key. It only handles certain
cases because that's all we need, but I believe you should be able to
provide full foreign key support if you wanted to create all the
right trigger functions. The key is to have the trigger function look
at the parent table to determine what type of account / record it is,
and then use that information to go to the appropriate child table
and acquire a FOR UPDATE lock.
I can probably provide a more concrete example of this if anyone's
interested.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@xxxxxxxxxxx
Give your computer some brain candy! www.distributed.net Team #1828
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
