integrity and inherit - Mailing list pgsql-novice

Hi ...

I hope someone will be so kind to take the time to explain to me what I
have been doing wrong in this example.

I have used PosgreSQL now for a while, and I really like to begin to use
some more of its integrity checks. The database I have been creating
uses the inherit functionality of PG, and this works out beautiful :-)

But I can't make the combination work ! To point out the problem i have
made this small example :

CREATE TABLE base (
 id INTEGER PRIMARY KEY,
 name TEXT
);

CREATE TABLE derived (
 value INTEGER
) INHERITS( base );

CREATE TABLE test_ref (
 ref_id INTEGER REFERENCES base,
 name TEXT
);

This is the setup, and all I get is a few "NOTICE", about indirect check
and indexes, really nice. Time to use it :-)

INSERT INTO base (id, name) VALUES( 1, 'mother' );
INSERT INTO derived (id, name, value) VALUES( 2, 'child', 42 );

INSERT INTO test_ref (ref_id, name) VALUES( 1, 'mother' );

All this works, but this one dont :

INSERT INTO test_ref (ref_id, name) VALUES( 2, 'child' );

Here it says :

ERROR:  <unnamed> referential integrity violation - key referenced from
test_ref not found in base

But a quick :

    SELECT * FROM base;

shows :

 id |  name
----+--------
  1 | mother
  2 | child

So as fare as I can see, this isn't correct, the id value number 2 is in
"base" table.

Could someone please tell me what I have done wrong ?

/BL