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

Re: Using a serial primary key as a foreign key in a second table

From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>, pgsql-novice(at)postgresql(dot)org
Cc: Nathaniel <naptrel(at)yahoo(dot)co(dot)uk>
Subject: Re: Using a serial primary key as a foreign key in a second table
Date: 2006-12-21 15:23:58
Message-ID: 610820.38154.qm@web31813.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
> Actually, your explanation of the problem is very succinct.  Postgresql offers 
> a direct solution to situations like these.  See here:
> 
> http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html
> 
> Isn't postgresql an AWESOME product!

Not arguments here, but there is one point of caution with postgresql's table inheritance that
should be considered in during schema design.  You cannot use the parent's tables primary key as a
foreign key in other parts of the schema, since the parent table does not >actually< have records
inserted into the child tables.

I have worked out an inheritance solution that does allow this just by using regular tables linked
with foreign keys.  To handle simultaneous insertion and updates across an entire branch of the
hierarchy I used a view made updatable using the rule system.  For deletes I just create the child
tables using cascade deletes.

The advantage using postgresql's tables inheritance is that it is quite easy to set up and it
works very well.  The solution I am using requires alot more planning and setup. :o(

If any are interested I can provide more details on how this is accomplished.

Regards,

Richard Broersma Jr.

In response to

pgsql-novice by date

Next:From: Bruno Wolff IIIDate: 2006-12-21 17:46:13
Subject: Re: Using a serial primary key as a foreign key in a second table
Previous:From: Sean DavisDate: 2006-12-21 14:52:36
Subject: Re: Using a serial primary key as a foreign key in a second table

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