Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.)

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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;
/
share|improve this answer
1  
If you're going to iterate over the collection, use a FORALL, not a FOR loop. –  Justin Cave Sep 11 '13 at 7:03
    
@JustinCave,Thanks for the suggestion. I have modified the answer. –  Dba Sep 11 '13 at 7:19
1  
It may be better to collect ROWIDs in bulk collect. Then DELETE FROM A will not have to do index lookup. –  Mindaugas Riauba Sep 11 '13 at 11:59
    
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! –  jpmc26 Sep 11 '13 at 19:55
add comment

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

share|improve this answer
    
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. –  jpmc26 Sep 11 '13 at 19:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.