Home » SQL & PL/SQL » SQL & PL/SQL » Delete primary key in case of parent-child relation (Windows XP, Oracle 9i)
Delete primary key in case of parent-child relation [message #604089] Mon, 23 December 2013 23:40 Go to next message
dark_prince
Messages: 121
Registered: June 2013
Location: India
Senior Member
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
Re: Delete primary key in case of parent-child relation [message #604092 is a reply to message #604089] Tue, 24 December 2013 00:09 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Create foreign keys with ON DELETE CASCADE option.
Re: Delete primary key in case of parent-child relation [message #604096 is a reply to message #604092] Tue, 24 December 2013 00:21 Go to previous message
dark_prince
Messages: 121
Registered: June 2013
Location: India
Senior Member
thanks...
Previous Topic: please sole it
Next Topic: ORA-00932: inconsistent datatypes: expected NUMBER got -
Goto Forum:
  


Current Time: Fri Mar 29 09:53:33 CDT 2024