SQL delete query ignoring errors and continue

user_10280696 0
Viewed 2299

Doing this: 

delete from product 
where descricao ilike '%ACC%'

I get this error:

update or delete on table "product" violates foreign key constraint "fk3b140f7bc8c91aae" on table "productmoviment"

I need to query continue and execute all the lines, deleting the ones which don't have, because if one single line has a foreign key it doesn't execute any other at all

Thanks

sql-delete postgresql foreign-keys sql
2 Answers

I assume this is something like a one-time cleanup operation: otherwise you have to make sure your foreign keys are correctly defined.

You can do it using a anonymous stored procedure.

Assumption:

  • the product table has a primary key called id
DO $$
DECLARE
    pr product%rowtype;
BEGIN
      FOR pr IN 
      select * from product
      where descricao ilike '%ACC%'
      LOOP
        RAISE NOTICE 'trying to delete product id  %',pr.id;
          BEGIN
              DELETE FROM product WHERE id=pr.id;
              RAISE NOTICE 
              'Deleted product id: %',pr.id;
          EXCEPTION
              WHEN others THEN 
                  -- we ignore the error
              END;
      END LOOP;
END $$;

Basically it iterates on all the products matching the WHERE clause (descricao ilike '%ACC%') and tries to delete them one-by-one, ignoring errors for products that cannot be deleted due to foreign keys.

user_12592300

To delete this, you have to define the foreign key constraint with ON DELETE CASCADE.

CASCADE defines that when a referenced row is deleted, it automatically deleted referencing rows as well

read documentation

But there is another way You have to delete a foreign key 1st then delete parent table

 delete from childtable
 where id_parent = 1;-- child table

 DELETE FROM parent
  WHERE id_parent = 1; --parent table
user_68085830
Related