[sql] Delete rows with foreign key in PostgreSQL

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?

This question is related to sql postgresql foreign-keys cascading-deletes

The answer is


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!


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/


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 ;

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;

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to postgresql

Subtracting 1 day from a timestamp date pgadmin4 : postgresql application server could not be contacted. Psql could not connect to server: No such file or directory, 5432 error? How to persist data in a dockerized postgres database using volumes input file appears to be a text format dump. Please use psql Postgres: check if array field contains value? Add timestamp column with default NOW() for new rows only Can't connect to Postgresql on port 5432 How to insert current datetime in postgresql insert query Connecting to Postgresql in a docker container from outside

Examples related to foreign-keys

Migration: Cannot add foreign key constraint The ALTER TABLE statement conflicted with the FOREIGN KEY constraint Can a foreign key refer to a primary key in the same table? Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why? MySQL Error 1215: Cannot add foreign key constraint MySQL Cannot Add Foreign Key Constraint Delete rows with foreign key in PostgreSQL How to remove constraints from my MySQL table? MySQL - Cannot add or update a child row: a foreign key constraint fails How to remove foreign key constraint in sql server?

Examples related to cascading-deletes

Entity Framework (EF) Code First Cascade Delete for One-to-Zero-or-One relationship Delete rows with foreign key in PostgreSQL How to add "on delete cascade" constraints? On delete cascade with doctrine2 How do I use cascade delete with SQL Server? How does JPA orphanRemoval=true differ from the ON DELETE CASCADE DML clause