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