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.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