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.

Jasper

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

Insertion

Answer 2

Sonds like the perfect job for the ON UPDATE SET NULL modifier to your FK constraint. Per documentation:

SET NULL

Set the referencing column(s) to null.

ALTER TABLE object DROP CONSTRAINT <fk_name_here>;

ALTER TABLE object ADD CONSTRAINT <fk_name_here>
FOREIGN KEY ON (object_data_id) REFERENCES object_data (object_data_id)
ON DELETE SET NULL;

Guessing the PK name is object_data_id, too. Then all you need is:

DELETE FROM object_data WHERE id ...

Reverences in object are set to NULL automatically.

Aside, this sounds odd:

I have two tables, object and object_data, with object referencing object_data by foreign key (the relation is 1:1)

Typically, I would expect the reference to be the other way round from object_data to object in such a scenario, but that's just guessing from the table names.

Coccidiosis

Answer 3

You could create a temp table:

CREATE TEMP TABLE object_data_ids AS
SELECT object_data_id FROM object WHERE ...;

Then use the temp table in both your update and delete:

UPDATE object SET object_data_id = NULL WHERE object_data_id IN
(SELECT object_data_id FROM object_data_ids);


DELETE FROM object_data WHERE id IN
(SELECT object_data_id FROM object_data_ids);

Defeatist