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

User · Answer

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/

User · Answer

It means that in table kontakty you have a row referencing the row in osoby you want to delete  You have do delete that row first or set a cascade delete on the relation between tables   Powodzenia

User · Answer

You can t delete a foreign key if it still references another table  First delete the reference   delete from kontakty where id osoby   1   DELETE FROM osoby  WHERE id osoby   1

User · Answer

It s been a while since this question was asked  hope can help   Because you can not change or alter the db structure  you can do this  according the postgresql docs   TRUNCATE -- empty a table or set of tables   TRUNCATE   TABLE     ONLY   name                      RESTART IDENTITY   CONTINUE IDENTITY     CASCADE   RESTRICT     Description  TRUNCATE quickly removes all rows from a set of tables  It has the same effect as an unqualified DELETE on each table  but since it does not actually scan the tables it is faster  Furthermore  it reclaims disk space immediately  rather than requiring a subsequent VACUUM operation  This is most useful on large tables     Truncate the table othertable  and cascade to any tables that reference othertable via foreign-key constraints   TRUNCATE othertable CASCADE    The same  and also reset any associated sequence generators   TRUNCATE bigtable  fattable RESTART IDENTITY    Truncate and reset any associated sequence generators   TRUNCATE revinfo RESTART IDENTITY CASCADE

User · Answer

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 def FROM   pg constraint WHERE  conrelid    public kontakty   regclass  -- assuming public 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   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

[sql] Delete rows with foreign key in PostgreSQL

Examples related to sql

Examples related to postgresql

Examples related to foreign-keys

Examples related to cascading-deletes