Re: Can a field be a primary key as well as a foreign key?

Basically I am doing this because there are a few different tables acting as
"child" tables of "parent", each with different fields.

I would have used inheritance, but postgres has problems with referential
integrity: foreign keys can only refer to the parent table and not the
children. 

That buggered up my plans, so I came up with the solution below...

In hindsight, I should have mentioned this in the original post, to make it
clearer.


- Andrew


On Tue, Mar 12, 2002 at 12:10:36AM -0600, Steve Lane wrote:
> On 3/11/02 10:44 PM, "Andrew Snow"  wrote:
> 
> > 
> > 
> > Can anyone see any problem with the following, practice?
> > 
> > 
> > CREATE TABLE parent (
> > foo SERIAL PRIMARY KEY
> > );
> > 
> > CREATE TABLE child (
> > foo INTEGER PRIMARY KEY REFERENCES parent ON DELETE CASCADE,
> > bar TEXT
> > );
> > 
> > CREATE TABLE subchild (
> > foo INTEGER PRIMARY KEY REFERENCES child ON DELETE CASCADE,
> > bar2 TEXT
> > );
> 
> Not a problem per se, but aren't you setting up a one-to-one-to-one
> relationship here? That is, for a given value of foo, you'll have at most
> one record in each of the three tables. That being the case, there must be
> some reason for splitting what is in effect one table into three -- for
> example, if the child and subchild records contain large column sets that
> are frequently not needed.
> 
> So, as far as I know it should *work*, but curious why you want to lay it
> out this way ...
> 
> -- sgl
> 
> 
> =======================================================
> Steve Lane
> 
> Vice President
> Chris Moyer Consulting, Inc.
> 833 West Chicago Ave Suite 203
> 
> Voice: (312) 433-2421       Email: slane@fmpro.com
> Fax:   (312) 850-3930       Web:   http://www.fmpro.com
> =======================================================
>