Delete rows with foreign key in PostgreSQL

Question

I would like to delete rows which contain a foreign key, but when I try something like this:

DELETE FROM osoby WHERE id_osoby='1'

I get this statement:

ERROR: update or delete on table "osoby" violates foreign key constraint "kontakty_ibfk_1" on table "kontakty" DETAIL: Key (id_osoby)=(1) is still referenced from table "kontakty".

How can I delete these rows?

Answer 1

To automate this, you could define the foreign key constraint with ON DELETE CASCADE.
I quote the the manual of foreign key constraints:

CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well.

Look up the current FK definition like this:

SELECT pg_get_constraintdef(oid) AS constraint_def
FROM   pg_constraint
WHERE  conrelid = 'public.kontakty'::regclass  -- assuming pubic schema
AND    conname = 'kontakty_ibfk_1';

Then add or modify the ON DELETE ... part to ON DELETE CASCADE (preserving everything else as is) in a statement like:

ALTER TABLE kontakty
   DROP CONSTRAINT kontakty_ibfk_1
 , ADD  CONSTRAINT kontakty_ibfk_1
   FOREIGN KEY (id_osoby) REFERENCES osoby (id_osoby) ON DELETE CASCADE;

Since there is no ALTER CONSTRAINT syntax, drop and recreate the constraint in a single ALTER TABLE statement. That avoids possible race conditions with concurrent write access.

You need the privileges to do so, obviously. The operation takes an ACCESS EXCLUSIVE lock on table kontakty and a SHARE ROW EXCLUSIVE lock on table osoby.

If you can't ALTER the table, then deleting by hand (once) or by trigger BEFORE DELETE (every time) are the remaining options.