PostgreSQL: removing rows that reference another table

Question

I have two tables, object and object_data, with an object referencing object_data by foreign key (the ratio is 1: 1). For a set of objects, I need to nullify their object_data links and delete the corresponding object_data lines, for example:

DELETE FROM object_data WHERE id IN ( SELECT object_data_id FROM object WHERE ... ); UPDATE object SET object_data_id = NULL WHERE ...; 

The problem is that the foreign key constraint prevents the removal of object_data strings that still reference the object.

My current solution reads the SELECT results into a list, then nullifies the foreign keys, and then deletes the object_data strings in batches of a reasonable size using the IN statement. Is there a better solution? Adding a column referencing an object_data object to an object is not an option.

Answer 1

Yes use CTE (general 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, runs first and stores the data you need later. The second CTE, called upd, sets the fields to NULL. Finally, DELETE uses the data from tmp to execute DELETE.

Answer 2

Sonds as the perfect job for the ON UPDATE SET NULL modifier for your FK constraint. In the documentation:

SET NULL

Set the column (s) of the link 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 . Then you only need:

 DELETE FROM object_data WHERE id ... 

Reverses in object automatically set to NULL.

Also, that sounds weird:

I have two tables, object and object_data, with a reference to the object_data object using a foreign key (the ratio is 1: 1)

Typically, I would expect the link to be in the opposite direction from object_data to object in such a scenario, but this is just a guess from the table names.

Answer 3

You can create a temporary table:

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

Then use the temporary table both in your update and when deleting:

 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);