CODEWITHSUNDEEP
sqloracle-database
Malvineous
Malvineous

Reputation: 27320

Oracle - ignoring constraint failures

I have a 'staff' table with many rows, and I would like to remove unused rows from this table (all the staff who were never referenced by any other table.)

There are constraints in place such that it is not possible to delete rows from the staff table if they are being referenced elsewhere. Because of this I was hoping I could just DELETE FROM STAFF which would remove the unreferenced rows and leave the others.

Unfortunately Oracle considers this an error, so as soon as it encounters a row that is referenced externally the whole query fails with a constraint violation.

Is there some way I can tell Oracle to just ignore the constraint violation, leave the record as is and move on to the next one?

Upvotes: 3

Views: 2411

Answers (2)

Jon Heller
Jon Heller

Reputation: 36807

You can use DML error logging:

exec dbms_errlog.create_error_log(dml_table_name => 'STAFF'
    ,err_log_table_name => 'STAFF_ERRORS');

delete from STAFF
log errors into STAFF_ERRORS('Is referenced') reject limit 999999999;

Then optionally delete, truncate, or drop the table STAFF_ERRORS.

Upvotes: 3

A.B.Cade
A.B.Cade

Reputation: 16905

You can use PLSQL:

BEGIN
  FOR r IN (SELECT id FROM STAFF) LOOP
    begin
       delete STAFF where id = r.id;
    exception
       when others then
          null;
    end;
  END LOOP;
END;

But it might take some time to complete ...

Upvotes: 1

Related Questions