Re: Primary Key on Inherited Table

Tom Lane wrote:
> 
> Wenjin Zheng <wenjin.zheng@lsbc.com> writes:
> > Is there a way to specify primary key in the child table using columns
> > from parent table?  I am using 6.5.0.
> 
> It sounds like the PRIMARY KEY spec is getting processed before the
> parent table reference is expanded.  That's probably a bug, but it's
> not going to get fixed in 6.5 ;-).  What I'd suggest is not relying
> on the PRIMARY KEY syntax, but just writing out the equivalent CREATE
> UNIQUE INDEX command separately after you create the child table.
> 
> (Strictly speaking, PRIMARY KEY also implies NOT NULL on each column,
> which might be hard to duplicate if you don't want the same columns
> to be NOT NULL in the parent, but as long as you can set them that
> way in the parent you don't need PRIMARY KEY.)

IMHO primary key itself should be one of inherited "attributes", and 
it should be unique over all parents and children.

This is hard to enforce under current "inheritance".

I think that use of inheritance as it is in current PostgreSQL should 
be discouraged.

There has been some discussion about making PostgreSQL a real ORDBMS
but as I understood the work was pushed back to wait for 7.0.

Doing small incremental "fixes" like the above would make it harder 
to get true OO features in the main code later, as people start to 
rely on "inheritance" for convenience features (like creating some 
tables with similar fields) some of which will have to be altered 
when fixing some more fundamental flaws in current PostgreSQL OO 
architecture (inheriting primary and foreign keys and other 
constraints, making alter table add column work, making it 
possible to dump/reload inherited tables that have added columns, 
etc.).

------------
Hannu