Bug #566: Primary key not working correctly with inheritance

Sorry, this is a known bug. See the TODO list.

---------------------------------------------------------------------------

pgsql-bugs@postgresql.org wrote:

Adrian Bool (aid@logic.org.uk) reports a bug with a severity of
3 The lower the number the more severe it is.

Short Description Primary key not working correctly with
inheritance

Long Description Hi,

Just started out with postgres (2 days so far!). I am intrested
in it's inheritance abilities.

I have come across something that seems to be a bug as far as
I can make out.

See the simple extract in the example code section.

The second insert should not be allowed as it breaks the primary
key's unqiueness. However postgres allows it's insertion.

If one tries to insert directly into 'parent' the problem does
not come about - the second insertion is rejected correctly.

The same applies with the 'unique' constraint - which I think
should also apply to child tables.

This is in Postgres 7.1.3 - standard RedHat 7.1 build.

Hope this is indeed a bug and I've not been wasting your time
with my newness to postgres.

Regards,

aid

Sample Code create table parent
( id integer primary key ) ;

create table child
( name varchar (30) ) inherits (parent) ;

insert into child values ( 1, 'Adrian' ) ; insert into child
values ( 1, 'John' ) ;

No file was uploaded with this report

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Wed, 23 Jan 2002 pgsql-bugs@postgresql.org wrote:

Short Description
Primary key not working correctly with inheritance

This is a well known deficiency with the inheritance system. Certain
things (primary and foreign keys as an example) don't inherit right now.
For primary key, AFAIK we don't have a real mechanism in place to do
a single primary key over multiple tables, but if you just want child
to be unique across itself (ie, you could insert a 1 in parent and in
child, but not two 1's in child) you can make a unique index across
child(id).