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

Foreign key constaint can be broken

From: Mark Kazemier <mark(dot)kazemier(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Foreign key constaint can be broken
Date: 2010-01-26 13:21:05
Message-ID: ace5c7fc1001260521t6df2534n8435e77f897695a9@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Dear,

I found a way to break a foreign key constraint in PostgreSQL. I discussed
this bug on a dutch discussion board and multiple people tried this on
multiple versions.

When I create the following tables:


CREATE TABLE a
(
id SERIAL NOT NULL,
foo CHAR(100) NOT NULL,
PRIMARY KEY(id)
);

CREATE TABLE b
(
id INT NOT NULL,
bar CHAR(20) NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(id) REFERENCES a(id) ON DELETE CASCADE
);

and add the following rule to table b:

CREATE OR REPLACE RULE delete_b AS ON DELETE
TO b
DO INSTEAD
DELETE FROM a WHERE OLD.id = id;

When you try to delete a row on table b:
DELETE FROM b WHERE id = 1;
The record from table a disappears, but the record in table b is still
there. Of course this is a very stupid construction, but I would expect some
kind of error / warning message instead. Now it is possible to corrupt your
data.

Best regards,
Mark Kazemier

Responses

pgsql-bugs by date

Next:From: Kevin GrittnerDate: 2010-01-26 14:55:10
Subject: Re: BUG #5284: Postgres CPU 100% and worker took too long to start; cancelled... Systemdown
Previous:From: yua ゅぁDate: 2010-01-26 00:58:19
Subject: Re: BUG #5284: Postgres CPU 100% and worker took too long to start; cancelled... Systemdown

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