Delete rows with foreign key in PostgreSQL

To automate this, you could define the foreign key constraint with ON DELETE CASCADE.
I quote the the manual for 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_defFROM   pg_constraintWHERE  conrelid = 'public.kontakty'::regclass  -- assuming public schemaAND    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;

There is no ALTER CONSTRAINT command. Drop and recreate the constraint in a single ALTER TABLE statement to avoid 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.

One should not recommend this as a general solution, but for one-off deletion of rows in a database that is not in production or in active use, you may be able to temporarily disable triggers on the tables in question.

In my case, I'm in development mode and have a couple of tables that reference one another via foreign keys. Thus, deleting their contents isn't quite as simple as removing all of the rows from one table before the other. So, for me, it worked fine to delete their contents as follows:

ALTER TABLE table1 DISABLE TRIGGER ALL;ALTER TABLE table2 DISABLE TRIGGER ALL;DELETE FROM table1;DELETE FROM table2;ALTER TABLE table1 ENABLE TRIGGER ALL;ALTER TABLE table2 ENABLE TRIGGER ALL;

You should be able to add WHERE clauses as desired, of course with care to avoid undermining the integrity of the database.

There's some good, related discussion at http://www.openscope.net/2012/08/23/subverting-foreign-key-constraints-in-postgres-or-mysql/

You can't delete a foreign key if it still references another table.First delete the reference

delete from kontaktywhere id_osoby = 1;DELETE FROM osoby WHERE id_osoby = 1;