On Sat, Jun 03, 2000 at 05:22:56PM +0200, Louis-David Mitterrand wrote:
> When creating a child (through CREATE TABLE ... INHERIT (parent)) it
> seems the child gets all of the parent's contraints _except_ its PRIMARY
> KEY. Is this normal? Should I add a PRIMARY KEY(id) statement each time
> I create an inherited table?

Following up to my previous message, I found that one can't explicitely
add a PRIMARY KEY on child table referencing a field on the parent
table, for instance:

    CREATE TABLE auction (
    id SERIAL PRIMARY KEY,
    title text,
    ... etc...
    );

then 

    CREATE TABLE auction_dvd (
    zone int4,
    PRIMARY KEY("id")
    ) inherits("auction");

doesn't work:
    ERROR:  CREATE TABLE: column 'id' named in key does not exist

But the aution_dvd table doesn't inherit the auction table's PRIMARY
KEY, so I can insert duplicates.

Solutions:

1) don't use PRIMARY KEY, use UNIQUE NOT NULL (which will be inherited?)
but the I lose the index,

2) use the OID field, but it's deprecated by PG developers?

What would be the best solution?

TIA

-- 
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr

Veni, Vidi, VISA.

Sun, 4 Jun 2000 08:52:10 +0200
Louis-David Mitterrand <cunctator@apartia.ch>
child table doesn't inherit PRIMARY KEY?
Louis-David Mitterrand wrote:
> 
> Solutions:
> 
> 1) don't use PRIMARY KEY, use UNIQUE NOT NULL (which will be inherited?)
> but the I lose the index,

AFAIK the UNIQUE constraint is implemented in PostgreSQL by creating 
an unique index on that field 

----------
Hannu

Sun, 04 Jun 2000 10:42:57 +0300
Hannu Krosing <hannu@tm.ee>
Re: child table doesn't inherit PRIMARY KEY?