Re: Inheritance on foreign key

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(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828