SQL surprise

Secret Hackers Rule #11: Hackers read manuals.

Recently I've discovered that PostgreSQL supports setting NULL on column referenced to another table (i.e. foreign constraint) before deleting a row:

CREATE TABLE foo (
 id integer PRIMARY KEY
);

CREATE TABLE bar (
 id integer PRIMARY KEY,
 foo_id integer REFERENCES foo ON DELETE SET NULL
);

INSERT INTO foo (id) VALUES (1);
INSERT INTO bar (id, foo_id) VALUES (1, 1);
DELETE FROM foo;

Without ON DELETE SET NULL server reports error that key foo.id = 1 is still referenced in table bar.