0

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: enter image description here

1 Answer 1

1

No code needed for this, just define the constraint with ON DELETE CASCADE option.

alter table orders drop constraint ORDERS_FK_CUSTOMERS;
alter table orders add constraint ORDERS_FK_CUSTOMERS 
  foreign key (customer_id) references customers(customer_id) on delete cascade;

Same for order_details.

1
  • Just stumbled across this while looking for something else. Realizing the thread is a month old, but if @pyuntae is still following - I gotta say that from a business standpoint, deleting a customer -- and even more so, deleting a PO sounds very problematic. Do you really want to come up to the end of the accounting year knowing that you have been deleting PO's all year long? How would you ever be able to balance your books? How would you honor warranty claims or recalls? Most jurisdictions require, by law, that records like this be retained for a lengthy period of time.
    – EdStevens
    Nov 26, 2017 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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