Problem with REFERENCES on INHERITS

[William Gordon Rutherdale <will.rutherdale@xxxxxxxxxxx>]

Hi.

I have encountered a problem with references when using INHERITS (on
Postgres 9.1/9.2).  Could someone please explain why this occurs.

Consider this example.

CREATE TABLE primate
(
   id SERIAL PRIMARY KEY,
   name TEXT,
   tale TEXT
);


CREATE TABLE chimp
(
    human_friend TEXT
) INHERITS(primate);

INSERT INTO chimp(name, tale, human_friend) VALUES
    ('Cheetah', 'Curly', 'Tarzan');

INSERT INTO primate(name, tale) VALUES
    ('King Julien', 'Move it');

SELECT * FROM primate;

==>
 id |    name     |  tale
----+-------------+---------
  2 | King Julien | Move it
  1 | Cheetah     | Curly
(2 rows)

CREATE TABLE banana_stash
(
    id SERIAL,
    primate_id INTEGER REFERENCES primate(id),
    qty INTEGER
);

INSERT INTO banana_stash(primate_id, qty) VALUES
    (1, 17);

==>
ERROR:  insert or update on table "banana_stash" violates foreign key
constraint "banana_stash_primate_id_fkey"
DETAIL:  Key (primate_id)=(1) is not present in table "primate".

INSERT INTO banana_stash(primate_id, qty) VALUES
    (2, 22);

==>
INSERT 0 1

SELECT * FROM banana_stash;

==>
 id | primate_id | qty
----+------------+-----
  2 |          2 |  22
(1 row)

My problem:  could someone please explain the semantics and why this
behaviour makes sense -- or is it a design error or bug?

To sum up the issue:
    - I insert into the derived table (chimp) and get id 1
    - I insert into the base table (primate) and get id 2
    - I have a foreign key constraint in banana_stash to the
      base table p.k. primate(id)
    - inserting to banana_stash with reference to id 2 is okay
    - inserting to banana_stash with reference 1 gives error
    - both ids 1 and 2 in table primate are supposed to be valid

So why does the one case give an error when the other does not?

Also, is there a way to solve this problem (i.e. remove the error)
without simply chopping out the REFERENCES clause from banana_stash?

-Will


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general