Is Primary Key valid with Inheritance Table

Hi Guys,

I am new to this database. I want a small clarification here, does inheritance can violate the primary key of any table with its insert. Please look at the example..

postgres=# create table ptest(id int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ptest_pkey" for
CREATE TABLE
postgres=# insert into ptest values(1);
INSERT 0 1
postgres=# insert into ptest values(1);
ERROR: duplicate key value violates unique constraint "ptest_pkey"

This is quite clear
postgres=# select * from ptest;
id
----
1
(1 row)
Creating a table with inheritance concept

postgres=# create table itest(name char(4)) inherits (ptest);
CREATE TABLE
postgres=# insert into itest values(1,'aaa');
INSERT 0 1

Here am inserting with same value which is already in the table.

postgres=# select * from ptest;
id
----
1
1
(2 rows) // Is this not the voilation of the Primary key. Please Explain
postgres=# select * from itest;
id | name
----+------
1 | aaa
(1 row)

Suppose, if i drop the table "itest", then i cannot see the values in the "ptest", but still if a view is generated on a "ptest" then the values will have a duplicates.

Can you please explain.

Regards
Ragavendra

Suppose, if i drop the table "itest", then i cannot see the values in the "ptest", but still if a view is generated on a "ptest" then the values will have a duplicates

This is what mention in Caveat Section of PostgreSQL Inheritance Document.
i.e. parent table's PRIMARY KEY would not stop the child table from having rows with names duplicating rows in parents. And those duplicate rows would by default show up in queries from parents.
Also, by default child would have no unique constraint at all, and so could contain multiple rows with the same name. You could add a unique constraint to child, but this would not prevent duplication compared to parent.

Command which will work in Inheritance hierarchies are SELECT, UPDATE and DELETE.

For more information, please follow the link given below:
http://www.postgresql.org/docs/8.4/static/ddl-inherit.html

Hi Vibhor,

Thank you for the update. I got the result from the google search on inheritance. There are few more reasons which will act quite opposite to the inheritance.

There are two problems with the current implementation of inheritance in PostgreSQL.

First, indexes are not shared between parent and child tables. On one hand, that's good because it gives you good performance. On the other hand, that's bad because PostgreSQL uses an index to guarantee uniqueness. Of course, there can be work around to this problem by encoding the type. But PostgreSQL won't give you any help in fixing this problem.

Secondly, The other potential problem with inheritance is that triggers are not shared between parent and child tables. If you define a trigger for the topmost table in your inheritance hierarchy, you will have to remember to define the same trigger for each descendant.

Regards
Raghavendra