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