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

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
);

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

Andrew Snow wrote:
>
>
> Can anyone see any problem with the following, practice?

    No  problem  at  all.  With  that  setup there can never be a
    subchild without the  matching  child,  even  if  the  parent
    exists.  If  a  child is deleted, it's subchildren follow, no
    need to touch the parent.  If that's the relationship of  the
    data, go ahead.


Jan

>
>
> 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
> );
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com