The following bug has been logged online:
Bug reference: 4520
Logged by: Adam Terrey
Email address: adam.terrey@acu.edu.au
PostgreSQL version: 8.3.4
Operating system: Linux Debian 2.6.18-6-amd64
Description: Cases where a forign key constraint is not inforced and
when it is incorrectly inforced.
Details:
(sorry I don't have 8.3.5, however, in looking at the release notes this
doesn't seem to be fixed)
There are two bugs I wish to report they are:
1) A foreign key constraint on a field in a parent table is not enforced on
child tables.
and
2) Foreign key constraints incorrectly produces an error on inserts when the
referenced row belongs to a child table of the referenced table.
Both bugs use the database below:
---- Init Listing: Start ----
BEGIN;
CREATE TABLE valid_names (
name TEXT PRIMARY KEY
);
CREATE TABLE parent_table (
id INTEGER PRIMARY KEY,
field_a TEXT REFERENCES valid_names(name),
age INTEGER CHECK (age > 10)
);
CREATE TABLE child_table (
-- id INTEGER PRIMARY KEY,
-- field_a TEXT,
field_b TEXT
) INHERITS (parent_table) ;
CREATE TABLE other_table (
id_b SERIAL PRIMARY KEY,
id INTEGER REFERENCES parent_table,
field_c TEXT
);
-- Some nice values :)
INSERT INTO valid_names (name)
VALUES
('ben'),
('fred'),
('sam'),
('alex'),
('baby ben'),
('baby fred'),
('baby sam');
INSERT INTO parent_table (id, field_a, age)
VALUES
(1, 'ben', 22),
(2, 'fred', 21);
INSERT INTO child_table (id, field_a, age, field_b)
VALUES
(3, 'baby ben', 27, 'hello'),
(4, 'baby fred', 40, 'boo boo');
INSERT INTO other_table (id, field_c)
VALUES (1, 'aaa');
COMMIT;
---- Init Listing: End ----
Bug 1: A foreign key constraint on a field in a parent table is not enforced
on child tables.
In this example database, field_a in parent_table references "name" in the
table valid_names.
SELECT name FROM valid_names;
name
-----------
ben
fred
sam
alex
baby ben
baby fred
baby sam
(7 rows)
SELECT id, field_a, name
FROM parent_table
LEFT JOIN valid_names ON field_a = name;
id | field_a | name
----+-----------+-----------
1 | ben | ben
2 | fred | fred
3 | baby ben | baby ben
4 | baby fred | baby fred
(4 rows)
An atempt to insert a invalid name into parent_table correctly fails.
INSERT INTO parent_table (id, field_a, age)
VALUES (7, 'bob', 45);
ERROR: insert or update on table "parent_table" violates foreign key
constraint "parent_table_field_a_fkey"
DETAIL: Key (field_a)=(bob) is not present in table "valid_names".
However an an insert into child_table with an invalid name incorrectly
succeeds.
INSERT INTO child_table (id, field_a, age, field_b)
VALUES (8, 'baby bob', 50, 'nooo');
INSERT 0 1
A scan from the parent table shows that the database is now broken because
'baby bob' does not exist in the table valid_names:
SELECT id, field_a, name
FROM parent_table
LEFT JOIN valid_names ON field_a = name;
id | field_a | name
----+-----------+-----------
1 | ben | ben
2 | fred | fred
3 | baby ben | baby ben
4 | baby fred | baby fred
8 | baby bob |
(5 rows)
Other checks on parent tables seem to be safe and inherit correctly:
INSERT INTO child_table (id, field_a, age, field_b)
VALUES (8, 'baby sam', 7, 'bar bar');
ERROR: new row for relation "child_table" violates check constraint
"parent_table_age_check"
Bug 2. Foreign key constraints incorrectly produces an error on inserts when
the referenced row belongs to a child table of the referenced table.
other_table references parent_table on the field id. Following are the
contents of the parent_table. (those who's field_a values prefix with "baby"
are from child_table)
SELECT id, field_a FROM parent_table;
id | field_a
----+-----------
1 | ben
2 | fred
3 | baby ben
4 | baby fred
8 | baby bob
(5 rows)
The following correct works:
INSERT INTO other_table (id, field_c)
VALUES (1, 'bbb');
INSERT 0 1
The following should work but incorrectly produces an error:
INSERT INTO other_table (id, field_c)
VALUES (4, 'ccc');
ERROR: insert or update on table "other_table" violates foreign key
constraint "other_table_id_fkey"
DETAIL: Key (id)=(4) is not present in table "parent_table".
Kind regrads,
Adam Terrey