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