SQL delete orphan
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)
.
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 constraints:
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 here.
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:
- Directly reference the child tables (following Tony's solution), or,
- Loop through the table in PL/SQL and catch any exceptions (following Confusion's and Bob's solutions).
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.
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);
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.
精彩评论