Thread: [BUG?] table inhiritance violates primary key

hi,

i have two tables:
CREATE TABLE public.test1 (
   id1 int4 NOT NULL,
   CONSTRAINT test1_pkey PRIMARY KEY (id1)
) WITHOUT OIDS;
CREATE TABLE public.test3 (
) INHERITS(test1) WITH OIDS;


i can insert the values
1
1
1

into the table test2. when i do a select * from test1 it returns:
1
1
1

after that i can once insert the value 1 again directly into table
test1. a second try failes for table test1.

it would make more sense, if the table test2 inhirits the primary key of
table test1 because each value inserted into test2 creates an implicit
entry in table test1 (at least from the users point of view) and these
entries simply violate the primary key of test1 which is unique.

so what's the deal with table inhiritance? i guess other constraints
aren't inhirited too - what about foreign keys, unique indexes etc.?

had some typos in the statements:

> CREATE TABLE public.test1 (
>   id1 int4 NOT NULL,
>   CONSTRAINT test1_pkey PRIMARY KEY (id1)
> ) WITHOUT OIDS;

CREATE TABLE public.test2 (
) INHERITS(test1) WITHOUT OIDS;

On Wed, 25 Jun 2003, [ISO-8859-15] Sven K�hler wrote:

> i have two tables:
> CREATE TABLE public.test1 (
>    id1 int4 NOT NULL,
>    CONSTRAINT test1_pkey PRIMARY KEY (id1)
> ) WITHOUT OIDS;
> CREATE TABLE public.test3 (
> ) INHERITS(test1) WITH OIDS;
>
>
> i can insert the values
> 1
> 1
> 1
>
> into the table test2. when i do a select * from test1 it returns:
> 1
> 1
> 1
>
> after that i can once insert the value 1 again directly into table
> test1. a second try failes for table test1.
>
> it would make more sense, if the table test2 inhirits the primary key of
> table test1 because each value inserted into test2 creates an implicit
> entry in table test1 (at least from the users point of view) and these
> entries simply violate the primary key of test1 which is unique.
>
> so what's the deal with table inhiritance? i guess other constraints
> aren't inhirited too - what about foreign keys, unique indexes etc.?

Table inheritance is fairly broken right now. Foreign keys and unique
constraints (including primary key) don't inherit to children, you can't
even get the effect that is generally desired by putting a separate
constraint on the child table since then the parent and the child could
each insert a 1 value.  Workarounds have been discussed in the past, so
you can probably find details in the archives. There was also talk
recently about multi-table indexes on either general or hackers, so you
can find that discussion as well for more info.

> Table inheritance is fairly broken right now. Foreign keys and unique
> constraints (including primary key) don't inherit to children, you can't
> even get the effect that is generally desired by putting a separate
> constraint on the child table since then the parent and the child could
> each insert a 1 value.  Workarounds have been discussed in the past, so
> you can probably find details in the archives. There was also talk
> recently about multi-table indexes on either general or hackers, so you
> can find that discussion as well for more info.

I'm very disappointed now. Table inheritance was the feature that
attracted me the most. Is it currently an experimental feature? Is it a
goal to achieve stability for future versions of postgresql? Which
version might be first with stable table inheritance?