Delete SQL Orphan

Question

Assuming all foreign keys have a corresponding restriction, is there a simple SQL statement to delete rows that are not connected anywhere in the database?

Something simple, like delete from the_table, that just skips any lines with a child entry?

I am trying to avoid manually looping around a table or adding something like where the_SK not in (a,b,c,d).

Answer 1

You may be able to use the advanced operator DELETEin 10g, which includes error logging.

DBMS_ERRLOG ( : ORA_ERR_MESG$, ..., ORA_ERR_TAG$)

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

LOG ERRORS delete , :

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

"hold-breath" ORA_ERR_TAG$.

.

, , parent_errlog, parent. , :

• ( Tony)
• PL/SQL ( Confusion's Bob)..

Answer 2

- , - . , , . / .

Answer 3

. , ( , ):

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 4

- - :

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, DELETE will fail, and no rows will be deleted, and you can continue to the next key.

Please note that if your table is large, this may take some time.