I have emp_staff_info table in my database with following structure
CREATE TABLE emp_staff_info ( emp_code NUMBER
, emp_name VARCHAR2(100)
, emp_age NUMBER(2)
, emp_dob DATE
, CONSTRAINT emp_code_pk PRIMARY KEY (emp_code)
)
and following is the delete procedure I used to delete emp_code
PROCEDURE proc_del_emp_code ( p_emp_code emp_staff_info.emp_code%TYPE)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DELETE FROM emp_staff_info
WHERE emp_code = p_emp_code;
COMMIT;
END proc_del_emp_code;
I used emp_code as foreign key in 3 tables which are
1. emp_corr_address
2. emp_perm_address
3. emp_education_info
Now whenever I tried to delete emp_code from emp_staff_info it gives error
ORA-02292 :- child record found. So if I want to delete emp_code from emp_staff_info
should I change above delete procedure to this
PROCEDURE proc_del_emp_code ( p_emp_code emp_staff_info.emp_code%TYPE)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DELETE FROM emp_corr_address
WHERE emp_code = p_emp_code;
DELETE FROM emp_perm_address
WHERE emp_code = p_emp_code;
DELETE FROM emp_education_info
WHERE emp_code = p_emp_code;
DELETE FROM emp_staff_info
WHERE emp_code = p_emp_code;
COMMIT;
END proc_del_emp_code;
or is there any other way...can anyone please tell me