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;
/

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