How to let DELETE to remove the rows with no constraint problem anyways?

When there is a constraint and one tries to DELETE data on a relation, but some rows can’t be deleted because some constraint, how can one Select those rows that had no problem with the constraint to delete them anyways?

Example:

Table h1 has 2 elements: h1_1 and h1_2
Table h2 has 1 element: h2_1
h2_1 refers by Foreign Key constraint to h1_1.

So, a simple DELETE FROM h1 will fail by that restriction.

How to DELETE h1_2?

Instead of simply DELETE from h1 WHERE f(value) == 'h1_2'…

… I was hoping for a kind of error management solution that simply ignore what it can’t remove, but proceed when no problem arise.

WHY? The reason is explained below:

THE VALUE OF THIS

The value of this is in cases where there is a really deep hierarchy by Foreign Keys with DELETE ON CASCADE.

If this was something, I could delete the top element and all it’s descendant but still protect data in a really low position in the hierarchy by removing the CASCADE on DELETE in it’s FOREIGN KEY Constraint.

Example:

h1
- CASCADE- h2.1
|           -CASCADE-h3.1
|           |         -CASCADE- h4.1
|           |         -NO-CASCADE- h4.2 <=== PROTECTED BY NO-CASCADE
|           -CASCADE-h3.2
- CASCADE- h2.2

Then One could remove all the h1 records and it’s descendants which has no element in h4.2 referencing transitively to them some how, by simple calling the DELETE sentence with the “IGNORE” mechanism:

DELETE WHAT-YOU-CAN FROM h1;

There would be no code specific to this Hierarchy, no knowledge of what refers to what.

Is there something like that in Postgres… or SQL anyways? Something similar to INSERT ... ON CONFLICT DO NOTHING?

Answer

I am not sure if you can solve it with basic SQL but in PL/pgSQL it is possible to catch the exceptions.

do $$
declare
    x mytable%rowtype;
begin
    for x in select * from mytable
    loop
      begin
        delete from mytable where id = x.id;
      exception when others then
        null;
      end;
    end loop;
end;
$$;

Advertisement

Answer