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" <andrew(at)modulus(dot)org> 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(at)fmpro(dot)com
> Fax: (312) 850-3930 Web: http://www.fmpro.com
> =======================================================
>