Skip site navigation (1) Skip section navigation (2)

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

From: "Adam Terrey" <adam(dot)terrey(at)acu(dot)edu(dot)au>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4520: Cases where a forign key constraint is not inforced and when it is incorrectly inforced.
Date: 2008-11-11 03:49:42
Message-ID: 200811110349.mAB3ngLr041355@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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

Responses

pgsql-bugs by date

Next:From: Dey, SutapaDate: 2008-11-11 12:14:34
Subject: SHARED LIBRARY IS NOT CREATED:Postgres could not create support for plperl
Previous:From: Shawn ChasseDate: 2008-11-10 18:55:17
Subject: BUG #4519: deadlock on commit

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group