BUG #4520: Cases where a forign key constraint is not inforced and when it is incorrectly inforced.

The following bug has been logged online:

Bug reference: 4520
Logged by: Adam Terrey
Email address: adam(dot)terrey(at)acu(dot)edu(dot)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