On Fri, Sep 26, 2008 at 3:04 PM, Thomas Keller <kellert(at)ohsu(dot)edu> wrote:
> 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
In response to
pdxpug by date
|Next:||From: Joshua Drake||Date: 2008-09-29 17:15:40|
|Subject: West: Second call for lightning talks|
|Previous:||From: Thomas Keller||Date: 2008-09-26 22:04:32|
|Subject: Alter table x add constraing|