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

Re: Alter table x add constraing

From: "Webb Sprague" <webb(dot)sprague(at)gmail(dot)com>
To: "Thomas Keller" <kellert(at)ohsu(dot)edu>
Cc: "Postgresql PDX_Users" <pdxpug(at)postgresql(dot)org>
Subject: Re: Alter table x add constraing
Date: 2008-09-26 23:11:12
Message-ID: b11ea23c0809261611u7201006aqc222d053130203ce@mail.gmail.com (view raw or flat)
Thread:
Lists: pdxpug
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

In response to

pdxpug by date

Next:From: Joshua DrakeDate: 2008-09-29 17:15:40
Subject: West: Second call for lightning talks
Previous:From: Thomas KellerDate: 2008-09-26 22:04:32
Subject: Alter table x add constraing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group