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