Search Postgresql Archives

Re: Inheritance on foreign key

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux