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?

Varico

Check on this as well on delete cascade ;) It's good to have these settings in your table though.. When creating foreign keys we add parent then child. So when deleting we delete child and then parent ;) – Higgler

Answer 1

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.

Vineyard

We do not have created tables with ON DELETE CASCADE. I can not change structure of tables. Is there a way that foreign keys are deleted automatically? or we have to follow the way suggested by @juergen d – Wetmore

@Varun: If you can't ALTER the table to add an FK with ON DELETE CASCADE, then deleting by hand (once) or by trigger BEFORE DELETE (every time) are the remaining options. – Vineyard

Cool, so the answer is to add ON DELETE CASCADE to the column with the foreign key in your create statement. – Volatile

@425nesp: I added explicit instructions. – Vineyard

In my case I needed DDL for seeing the exact name constraint, in this case it would be \d kontakty – Hindustan

This is still the best answer i would say. Only problem is that when working with IDS, its a bit tricky. It might delete all the wanted and wanted rows with the same ID. – Shuler

Answer 2

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/

Hippogriff

Archive version of dead link: web.archive.org/web/20160922175428/http://www.openscope.net/…. (suggested edit queue is full, so I don't have enough rep to edit it myself) – Jugoslavia

When in need to cut a portion of a huge table for tests (like 1 year) that helps a lot! – Voidable

Answer 3

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;

Siesta

Answer 4

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 ;

Wrongful

Nice one, very useful. – Gyrose

the best answer ever :) – Whittaker

Answer 5

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!

Laurustinus

Answer 6

One can achieve this by issueing an extra SQL script that deletes the records related via the FK.

For this, using subselect in WHERE clause of a DELETE command can simply do what is needed.

Something similar to:

DELETE FROM kontakty 
    WHERE fk_column_from_kontakty_matching_id_osoby IN (
        SELECT id_osoby FROM osoby WHERE id_osoby = '1'
    );


DELETE FROM osoby WHERE id_osoby = '1';

• the example assumes the FK column from kontakty matching osoby.id_osoby is called fk_column_from_kontakty_matching_id_osoby as it cannot be interpolated from the error message provided
• For the case of OP's the query could by simplified a lot, but I have decided to leave it like this for it demonstrates accomplishing more complex scenarios
• This approach is quite useful for SQL migrations, where one cannot depend on an assigned id and where ids cannot be easily fetched first and acted upon later.

Churinga

This is the cleanest solution to the asked question. Having CASCADE conditions and all is good, but OP may not have the rights to actually modify the design. Given the status quo, this is the way to solve the problem. – Pirzada

Answer 7

You can still delete rows that have no foreign key constraint with the third table:

DELETE FROM tab1
where id IN
(
  SELECT id
  FROM tab1 One
  INNER JOIN tab2 Two 
    ON   One.id = Two.id
  WHERE  One.name = Two.name
)
AND NOT EXISTS (SELECT 1 FROM tab3 WHERE tab3.id = tab1.id)

Unthinkable