Delete rows in parent table based on criteria in child table - Oracle

Question

I have a table B with a foreign key to table A. I want to DELETE some rows in table B, and I also want to DELETE their parent rows in table A. However, the delete criteria is based on table B. The rows in table A cannot be deleted first because the reference in table B restricts the deletion, but I also have to get the keys of A from the rows to delete in B.

Here is a SQLFiddle with a sample table structure: http://sqlfiddle.com/#!4/f156c/4/0.

My first inclination was to attempt to save the keys by SELECTing them from B into a variable, and then use that to DELETE from A.

DECLARE
 A_ID_TO_DELETE DBMS_SQL.NUMBER_TABLE;
BEGIN
 SELECT A_ID BULK COLLECT INTO A_ID_TO_DELETE
 FROM (SELECT A_ID
 FROM B
 WHERE LENGTH(B_DATA) > 4
 );
 DELETE FROM B
 WHERE LENGTH(B_DATA) > 4;
 DELETE FROM A
 WHERE A_ID IN A_ID_TO_DELETE;
END;
/

But that just gives an PLS-00382: expression is of wrong type error. The error itself is coming from the DELETE on A; I know this because if I comment it out, the block runs.

How can I either get around the expression is of wrong type error, or what's another way to approach this?

Oracle version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

(Yes, I'm well aware of how old that is. Client's choice of DB, not ours.)

Answer 1

Try like this,

DECLARE
 A_ID_TO_DELETE DBMS_SQL.NUMBER_TABLE;
BEGIN
 SELECT A_ID BULK COLLECT INTO A_ID_TO_DELETE
 FROM (SELECT A_ID
 FROM B
 WHERE LENGTH(B_DATA) > 4
 );
 DELETE FROM B
 WHERE LENGTH(B_DATA) > 4;
 FORALL i IN A_ID_TO_DELETE.first..A_ID_TO_DELETE.last
 DELETE FROM A
 WHERE A_ID = A_ID_TO_DELETE(i);
END;
/

• Justin Cave: If you're going to iterate over the collection, use a FORALL, not a FOR loop.
• Dba: @JustinCave,Thanks for the suggestion. I have modified the answer.
• Mindaugas Riauba: It may be better to collect ROWIDs in bulk collect. Then DELETE FROM A will not have to do index lookup.
• jpmc26: SQL Fiddle of this working: sqlfiddle.com/#!4/689f3/2. I'd prefer to avoid such an iteration, but iterating is better than now working at all. Thanks!

Answer 2

Foreign key constraints in Oracle are defined (by default) as NON DEFERRABLE which means the constraint is checked always at the end of statements. There are two more ways for a constraint to be defined:

• DEFERRABLE INITIALLY DEFERRED

If you have the constraint defined (or modify it now) as DEFERRABLE INITIALLY DEFERRED, then the checking of the constraint will happen at the end of the transactions. So, this would work for you (but it would also modify how the constraint is checked in all other transactions.)

• DEFERRABLE INITIALLY IMMEDIATE

If you define it as DEFERRABLE (it gets the default of INITIALLY IMMEDIATE), then it is still checked at the end of statements but it can be deferred. So, you can choose to set all constraints inside a transaction with SET CONSTRAINTS ALL DEFERRED; and this would work fine:

SET CONSTRAINTS ALL DEFERRED; 
DELETE FROM A
WHERE A_ID IN 
 ( SELECT A_ID
 FROM B
 WHERE LENGTH(B_DATA) > 4
 );
DELETE FROM B
WHERE LENGTH(B_DATA) > 4;

Test at SQL-Fiddle

More details about constraints at Oracle docs: Constraints and Managing Integrity Constraints

• jpmc26: This probably would've been a better way to organize things. Unfortunately, I can't modify the schema in conjunction with this query. (The whole reason we're doing this via query instead of through an application is because of the bureaucracy invoked in getting changes to production.) Thank you, though. Much appreciated.