Documentation Bug related to Inheritance - Mailing list pgsql-bugs

Inheritance is one of the most useful and 'marketed' features in PostgreSQL.
However its behavior is not what one expected it to be. Inherited tables do
not inherit some constraints, including primary and foreing keys.
 This information is very important, specially for newbies, who can get very
frustated by referential integrity violation happening when they would not
be expected to happen, and vice versa.
 The mailing lists have many questions related to it, including:

 http://fts.postgresql.org/db/mw/msg.html?mid=118834
        where Peter Eisentraut remommends one NOT TO mix inheritance
        and foreing keys;
 http://fts.postgresql.org/db/mw/msg.html?mid=1022481
        where Stephan Szabo advices the reader to see past discussion in the
        GENERAL list for more information
 and http://fts.postgresql.org/db/mw/msg.html?mid=122007
        where Bruce Momjian adds this issue to the TODO list.

 Looking for past discussions in the lists require too much work, since this
is a very popular question. ( you find a lot of questions and answers, but
those only tells the reader 'Currently you can't do that' )
 I would suggest this question (referential integrity in inherited tables) to
be added to the FAQ, since in think it is a bug in the documentation.
 Perhaps it would also be useful a documentation page (probably the inheritance
one - http://www.postgresql.org/idocs/index.php?inherit.html) warning the
reader about this particular issue.
 Additionally, this page should help the user to work through it telling how
to use inheritance and primary keys, using the current postgreSQL features
(rules, triggers, inheritance itself, etc.) or the 'pure' referential model.

 A simple example of the problem follows:

CREATE TABLE student (
        id              serial,
        name            varchar(35),
        email           varchar(25),
        UNIQUE ( email ),
        PRIMARY KEY ( id ) );

CREATE TABLE graduate (
        thesisname      varchar (100)
) INHERITS ( student );

CREATE TABLE address (
        student         int4 REFERENCES student,
        add1            varchar(50),
        add2            varchar(50)
);


testeinh=> INSERT INTO student ( name, email ) VALUES ( 'Danilo', 'danilogh@comp' );
INSERT 28259 1

-- *************[ Cannot duplicate UNIQUE value, as expected ]*****************
testeinh=> INSERT INTO student ( name, email ) VALUES ( 'Another Danilo', 'danilogh@comp' );
ERROR:  Cannot insert a duplicate key into unique index student_email_key

-- *************[ Should stop duplicating UNIQUE value, defined in student ]***
-- *************[ Strange behavior: must be documented ]***********************
testeinh=> INSERT INTO graduate ( name, email ) VALUES ( 'Yet Anothe Danilo', 'danilogh@comp' );
INSERT 28261 1

-- *************[ Duplicated UNIQUE field: 'broken' referential integrity ]****
testeinh=> SELECT * FROM student*;
 id |       name        |     email
----+-------------------+---------------
  1 | Danilo            | danilogh@comp
  3 | Yet Anothe Danilo | danilogh@comp
(2 rows)

-- *************[ Referential integrity tested, and OK ]*********************
testeinh=> INSERT INTO address ( student, add1 ) VALUES ( 1, 'Some Street' );
INSERT 28262 1

-- *************[ No id=2 in table: normal behavior ]*************************
testeinh=> INSERT INTO address ( student, add1 ) VALUES ( 2, 'Some Other Street' );
ERROR:  <unnamed> referential integrity violation - key referenced from address not found in student

-- *************[ Should allow inclusion, since id=3 exists for table inherited from student ]***********
-- *************[ Strange behavior: must be documented ]***********************
testeinh=> INSERT INTO address ( student, add1 ) VALUES ( 3, 'Some Other Street' );
ERROR:  <unnamed> referential integrity violation - key referenced from address not found in student

Sorry my broken English, I'd be glad to explain better any confusing passage.
Regards,

    Danilo Hashimoto