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

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

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: 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 14:52:36
Message-ID: 200612210952.36825.sdavis2@mail.nih.gov (view raw or flat)
Thread:
Lists: pgsql-novice
On Thursday 21 December 2006 09:47, Nathaniel wrote:
> Thanks for the help Sean. You're right about the problem you
> identified in my earlier reply concerning uniqueness, but fortunately
> the problem is that I picked a bad example---in an attempt to distill
> the essence of the problem I was was trying to solve from my own
> messy database I oversimplified. What I'm trying to do is model
> object inheritance in a relational database by defining 4 tables/
> classes: 3 different types of children, each of which has its own
> table, and a parent class that's represented by one table which
> contains attributes common to all 3 child types. When I want to add
> an "object", I add a row to one of the three child tables (depending
> on the type of object I'm adding) and then add a record to the parent
> class table...so a single object is represented by a row in two
> different tables---I think it's called polymorphic association. I've
> been struggling to concisely find a way to add the child entry
> followed by the parent entry (which has to contain the ID of the child).

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!

> So thanks again, especially for the link to the "INSERT"
> documentation. The line "If the INSERT command contains a RETURNING
> clause, the result will be similar to that of a SELECT statement
> containing the columns and values defined in the RETURNING list,
> computed over the row(s) inserted by the command" seems to be new to
> the 8.2 docs---and I've spent hours pouring over the 8.1.4 manual!

It is a new feature in 8.2.  I never asked what version of postgres you were 
using.  However, my answer above is a better way to go about your specific 
problem, anyway.

Sean

In response to

Responses

pgsql-novice by date

Next:From: Richard Broersma JrDate: 2006-12-21 15:23:58
Subject: Re: Using a serial primary key as a foreign key in a second table
Previous:From: NathanielDate: 2006-12-21 14:47:38
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