Skip site navigation (1) Skip section navigation (2)

Re: inheritance is madness

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: T W <list(at)traviswellman(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: inheritance is madness
Date: 2007-09-21 08:45:37
Message-ID: 1190364337.7954.178.camel@linda.lfix.co.uk (view raw or flat)
Thread:
Lists: pgsql-novice
On Thu, 2007-09-20 at 22:11 -0700, T W wrote:
> Hello Gurus
> 
> I'm very surprised at the fact that primary keys are not inherited.
> When trying to make foreign keys from a third table to a parent table,
> the child table's data is not counted [I assume] due to this fact,
> even if the child table has the same primary key. This is really
> weird, considering a select from the parent table will return the
> child table's data. Does anyone know of a hack to get this behavior to
> change, and allow the third table to use a foreign key that references
> child table data?

This is a very long-standing deficiency in inheritance.  Primary keys
and foreign keys are valid only for a single table, not for an
inheritance tree.

One solution is to maintain an index table which is updated by triggers
fired after an update to any of the tables in the inheritance tree.  Do
your foreign key relationships to that index table.  The trigger could
reject any duplicated keys in the tree itself.

-- 
Oliver Elphick                                          olly(at)lfix(dot)co(dot)uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


In response to

pgsql-novice by date

Next:From: Derrick BettsDate: 2007-09-21 16:03:24
Subject: numericOnly trigger
Previous:From: T WDate: 2007-09-21 05:11:11
Subject: inheritance is madness

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group