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

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