ORA-02292: integrity constraint violated - child record found while creating procedures in ORACLE SQL Developer?
There are two ways to accomplish this :
You may use cascade on delete ( drop&create again ) :
drop constraint order_details_fk_orders;
alter table order_details add constraint order_details_fk_orders
foreign key(order_id) references orders(order_id)
on delete cascade;
delete orders where customer_id = &i_cust_id;
or
You may delete your detail records first :
with following procedure :
CREATE OR REPLACE PROCEDURE PR_DELETE_CUSTOMER(
I_CUSTOMER_ID orders.customer_id%type,
O_TOT_CUSTOMERS out number
) IS
BEGIN
delete order_details where order_id in ( select order_id from orders where customer_id = I_CUSTOMER_ID );
delete orders where customer_id = I_CUSTOMER_ID;
delete customers where customer_id = I_CUSTOMER_ID;
select count(1) into O_TOT_CUSTOMERS from orders;
END;
and then
var v_tot_customers number;
execute pr_delete_customer(1,:v_tot_customers);
print v_tot_customers;
pyuntae
Updated on June 27, 2022Comments
-
pyuntae almost 2 years
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; /