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

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
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. Commented Nov 26, 2017 at 20:09

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.