Thread: BUG #1307: Possible bug inheritance/referential integrity

The following bug has been logged online:

Bug reference:      1307
Logged by:          Thomas Jacob

Email address:      jacob@internet24.de

PostgreSQL version: 7.4.5

Operating system:   Gentoo Linux, Kernel 2.6.7

Description:        Possible bug inheritance/referential integrity

Details:

Rows inserted into a table "sub" inherited from a table
"super" do not seem to exist in the super-table from the point of
view of the foreign key constraint checker in references to the
"super"-table, even though a SELECT query displays the rows that
were inserted into sub also in super, as should be (See example
provided).

But they should exist, shouldn't they? Or do I simply not
understand PostgreSQL's inheritance model correctly?

CREATE TABLE super (id INT PRIMARY KEY);
CREATE TABLE super_ref (id INT REFERENCES super);
CREATE TABLE sub () INHERITS(super);

-- this works
INSERT INTO super VALUES(1); INSERT INTO super_ref VALUES(1);

-- clean up
DELETE FROM super_ref; DELETE FROM super;

-- this fails with: ERROR:  insert or update on table "super_ref" violates
foreign key constraint "$1"
INSERT INTO sub VALUES(1); INSERT INTO super_ref VALUES(1);

On Sat, Nov 06, 2004 at 12:11:49PM +0000, PostgreSQL Bugs List wrote:
>
> Rows inserted into a table "sub" inherited from a table
> "super" do not seem to exist in the super-table from the point of
> view of the foreign key constraint checker in references to the
> "super"-table, even though a SELECT query displays the rows that
> were inserted into sub also in super, as should be (See example
> provided).

This is a known problem -- see the last paragraph in the "Inheritence"
chapter of the PostgreSQL documentation:

http://www.postgresql.org/docs/7.4/static/ddl-inherit.html

The doc for 8.0 (in beta) has more detail:

http://developer.postgresql.org/docs/postgres/ddl-inherit.html

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Sat, 6 Nov 2004, PostgreSQL Bugs List wrote:

> Rows inserted into a table "sub" inherited from a table
> "super" do not seem to exist in the super-table from the point of
> view of the foreign key constraint checker in references to the
> "super"-table, even though a SELECT query displays the rows that
> were inserted into sub also in super, as should be (See example
> provided).
>
> But they should exist, shouldn't they? Or do I simply not
> understand PostgreSQL's inheritance model correctly?
>
> CREATE TABLE super (id INT PRIMARY KEY);
> CREATE TABLE super_ref (id INT REFERENCES super);
> CREATE TABLE sub () INHERITS(super);

Inheritance really just doesn't work very well right now when combined
with constraints.

For example, sub has no primary key and even if you defined one on sub(id)
it wouldn't guarantee that you didn't have duplicates between super and
sub.  Foreign keys currently reference as if you said ONLY, so the
references constraint above references only super and not any child.
It needs someone to really put effort into making it better.