Re: Alter table x add constraing

["Webb Sprague" <webb(dot)sprague(at)gmail(dot)com>]

On Fri, Sep 26, 2008 at 3:04 PM, Thomas Keller <kellert(at)ohsu(dot)edu> wrote:
> Hi,
> I have a table that inherits most of it's columns.

I have found that inheritance in PG is not worth the fact that indexes
are not inherited, and (thus) unique constraints are not inherited.
This means if you have a Researcher table with an ID field and inherit
it, you can a duplicate ID in the child tables.... usually bad.

I do it the old fashioned way:  A Researcher table that contains all
the commonalities (id (or some PK), firstname, lastname, etc), and
then create non-inherited subtables that use a foreign key to this
Researcher table for attributes specific to certain types of
researchers.  Use views if you need to see all researchers all at
once.  Put a primary key on the Researcher table.  (In the table
definition you can do it, you don't need an alter table -- read the
docs for the details.)  You might have to type another 100 characters
or so, but in the longer term, 100 characters is almost nothing.

I have been bitten by this before, and I would recommend getting rid
of inheritance unless you need it for some other reason like
partitioning.  It really isn't a great feature, though a lot of people
might disagree.  If indexes were inherited, that would be a different
story.

-W