Skip to Main Content
  • Questions
  • Procedure that deletes an input from table with foreign key constraints

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: October 22, 2017 - 3:17 am UTC

Last updated: October 22, 2017 - 9:37 am UTC

Version: ORACLE SQL DEVELOPER 17.2.0.188

Viewed 1000+ times

You Asked

I have written a procedure that has as input the CUSTOMER_ID. Then the procedure deletes the corresponding customer from the table CUSTOMERS.

CREATE OR REPlACE PROCEDURE DELETE_CUSTOMER 
    (CUSTOMER_ID NUMBER) AS 
    TOT_CUSTOMERS NUMBER;
    BEGIN
        DELETE FROM CUSTOMERS
        WHERE CUSTOMERS.CUSTOMER_ID = DELETE_CUSTOMER.CUSTOMER_ID;
        TOT_CUSTOMERS := TOT_CUSTOMERS - 1;
        END;
    / 

I have to execute the procedure to delete customer with id 1.

EXECUTE DELETE_CUSTOMER(01);

When I do this, I get an error

Error starting at line : 120 in command -
    BEGIN DELETE_CUSTOMER(01); END;
    Error report -
    ORA-02292: integrity constraint (TUG81959.ORDERS_FK_CUSTOMERS) violated - child record found
    ORA-06512: at "TUG81959.DELETE_CUSTOMER", line 5
    ORA-06512: at line 1
    02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
    *Cause:    attempted to delete a parent key value that had a foreign
               dependency.
    *Action:   delete dependencies first then parent or disable constraint.


I know this is because there is a foreign key for CUSTOMER_ID on the table ORDERS, which means the customer cannot be deleted because he has placed an order.
**How do I write the code so that I can first delete the corresponding ORDER_DETAILS and then delete the corresponding ORDERS so that I can finally be able to delete a record from CUSTOMERS?**

I tried rewriting the code but I am just lost now:

CREATE OR REPlACE PROCEDURE DELETE_CUSTOMER 
(CUSTOMER_ID_IN NUMBER) AS 
TOT_CUSTOMERS NUMBER;
CURSOR C1 IS
DELETE FROM ORDERS
WHERE ORDERS.ORDER_ID = CUSTOMER_ID.ORDER_ID;
CURSOR C2 IS
DELETE FROM ORDER_DETAILS
WHERE ORDER_DETAILS.ORDER_ID = CUSTOMER_ID.ORDER_ID;
CURSOR C3 IS
DELETE FROM CUSTOMERS
WHERE CUSTOMERS.CUSTOMER_ID = DELETE_CUSTOMER.CUSTOMER_ID;
BEGIN
    OPEN C1;
    OPEN C2;
    OPEN C3;
    IF C1%FOUND AND C2%FOUND AND C3%FOUND
    THEN TOT_CUSTOMERS := TOT_CUSTOMERS - 1;
    END IF;
    CLOSE C1;
    CLOSE C2;
    CLOSE C3;
END;
/


Here are the tables for reference:
https://i.stack.imgur.com/pAAmb.png

and Connor said...

Easiest way is to define your constraints as ON DELETE CASCADE. Then you don't need to worry about it. But as long as you know the foreign key columns, you just work your from children up to the parent.

begin
  delete from order_details
  where order_id in 
    ( select order_id from orders where cust_id = p_cust_id );

  delete from orders 
  where cust_id = p_cust_id;
  
  delete from customers where cust_id = p_cust_id;
end;


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.