PostgreSQL: deleting rows referenced from another table

Question

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 1

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