PostgreSQL: deleting rows referenced from another table

sql postgresql foreign-keys referential-integrity
laurt picture laurt · Mar 27, 2015 · Viewed 9.8k times · Source

I have two tables, object and object_data, with object referencing object_data by foreign key (the relation is 1:1). For a set of objects, I need to null their object_data references and delete the corresponding object_data rows, like this:

DELETE FROM object_data WHERE id IN
( SELECT object_data_id FROM object WHERE ... );

UPDATE object SET object_data_id = NULL WHERE ...;

Problem is, the foreign key constraint doesn't allow deleting object_data rows that are still referenced from object.

My current solution is reading the results of the SELECT into a list, then nulling the foreign keys and then deleting the object_data rows in reasonable-sized batches using IN operator. Is there a better solution? Adding a column that refers back from object_data to object is not an option.

Answer

Eelke picture Eelke · Mar 27, 2015

Yes use CTEs (Common Table Expression)

WITH tmp AS (SELECT object_data_id FROM object WHERE ...),
    upd AS (UPDATE object SET object_data_id = NULL WHERE ...)
DELETE FROM object_data 
  WHERE id IN (SELECT object_data_id FROM tmp);

The first CTE called tmp is executed first and remembers the data you need later The second CTE called upd does sets the fields to NULL Finally the DELETE uses the data from tmp to perform the DELETE

Related questions

SET CONSTRAINTS ALL DEFERRED not working as expected

In a PostgreSQL 9.3 database, if I define tables a and b as follows: CREATE TABLE a(i integer); ALTER TABLE a ADD CONSTRAINT pkey_a PRIMARY KEY (i); CREATE TABLE b(j integer); ALTER TABLE b add CONSTRAINT fkey_ij …

Read More
postgresql and Delete statement violates foreign key constraint

I have a problem with my delete statement. I have two tables: table vehicule_loan( vehicule TEXT NOT NULL UNIQUE, ); table vehicule_uid ( id UUID NOT NULL DEFAULT uuid_generate_v4(), vehicule TEXT NOT NULL REFERENCES vehicule_loan(vehicule) ON …

Read More
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 …

Read More