Oracle - ignoring constraint failures

Question

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?

Answer 1

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.