SQL delete orphan

Question

Assuming that all foreign keys have the appropriate constraint, is there a simple SQL statement to delete rows not referenced anywhere in the DB?

Something as simple as delete from the_table that simply skip any rows with child record?

I'm trying to avoid manually looping through the table or adding something like where the_SK not in (a,b,c,d).

Answer 1

No. Obviously you can do this (but I realise you would rather not):

delete parent
where  not exists (select null from child1 where child1.parent_id = parent.parent_id)
and    not exists (select null from child2 where child2.parent_id = parent.parent_id)
...
and    not exists (select null from childn where childn.parent_id = parent.parent_id);

Answer 2

You might be able to use the extended DELETE statement in 10g that includes error logging.

First use DBMS_ERRLOG to create a logging table (which is just a copy of the original table with some additional prefixing columns: ORA_ERR_MESG$, ..., ORA_ERR_TAG$)

execute dbms_errlog.create_error_log('parent', 'parent_errlog');

Now you can use the LOG ERRORS clause of the delete statement to capture all rows that have existing integrity constaints:

delete from parent
   log errors into parent_errlog ('holding-breath')
   reject limit unlimited;

In this case the "holding-breath" comment will go into the ORA_ERR_TAG$ column.

You can read the full documentation at:

http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_8005.htm#i2117787

If the parent table is huge and you're only looking to delete a few stray rows, you'll end up with a parent_errlog table that is essentially a duplicate of your parent table. If this isn't ok, you'll have to do it the long way:

1. Directly reference the child tables (following Tony's solution), or
2. Loop through the table in PL/SQL and catch any exceptions (following Confusion's and Bob's solutions)

Hope this helps.

Answer 3

The easiest way may be to write an application or stored procedure that attempts to delete the rows in the table one-by-one and simply ignores the failures due to foreign key constraints. Afterwards, all rows not under a foreign key constraint should be removed. Depending on the required/possible performance, this may be an option.

Answer 4

One way to do this is to write something like the following:

eForeign_key_violation EXCEPTION;
PRAGMA EXCEPTION_INIT(eForeign_key_violation, -2292);

FOR aRow IN (SELECT primary_key_field FROM A_TABLE) LOOP
  BEGIN
    DELETE FROM A_TABLE A
    WHERE A.PRIMARY_KEY_FIELD = aRow.PRIMARY_KEY_FIELD;
  EXCEPTION
    WHEN eForeign_key_violation THEN
      NULL;  -- ignore the error
  END;
END LOOP;

If a child row exists the DELETE will fail and no rows will be deleted, and you can proceed to your next key.

Note that if your table is large this may take quite a while.