1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-02292 Error or ORA-02449 Error

Discussion in 'Other Development Tools' started by tyro, Dec 4, 2008.

  1. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    ORA-02292 Error or ORA-02449 Error

    Now, I see many people facing the ORA-02292 error and frantically look for help, when it's really such a simple issue. Basically getting the ORA-02292 or ORA-02449 error means that you are trying to delete/drop a "parent/master" table while its "child/detail" table exists. So, if you want to delete/drop the master tables then first drop child/details tables.

    Let us look at a very simple example. There are two tables, employees and departments, one references the other, which means that every employee works in one of departments, so there's referential integrity constraint present.
    Code (Text):

    SQL> create table dept (dept_no number primary key,
    2 d_name varchar2(20));

    Table created.

    SQL> create table emp (emp_no number primary key,
    2 e_name varchar2(20),
    3 dept_no number constraint fk_ed
    4 references dept (dept_no));

    Table created.

    SQL> insert all
    2 into dept (dept_no, d_name) values (100, 'Fire')
    3 into emp (emp_no, e_name, dept_no) values (1, 'TYRO', 100)
    4 select * from dual;

    2 rows created.

    SQL> commit;

    Commit complete.
     
    Next try to delete data from these tables. Then let's see what happens when deleting parent table first
    Code (Text):

    SQL> delete from dept;
    delete from dept
    *
    ERROR at line 1:
    ORA-02292: integrity constraint (MIKE.FK_ED) violated - child record found
     
    However you can delete from the child table:
    Code (Text):

    SQL> delete from emp;

    1 row deleted.
     
    Now try to delete the parent table and ORA-02292 won't appear:
    Code (Text):

    SQL> delete from dept;

    1 row deleted.
     
    Using DROP statements will produce the ORA-02449 error in place of ORA-02292

    Another point is that referential integrity constraint as shown above may be defined as deferrable. It means that checking will be done on COMMIT. Let's look at an example:
    Code (Text):

    SQL> CREATE TABLE dept (dept_no NUMBER PRIMARY KEY,
    2 d_name VARCHAR2(20));

    Table created.

    SQL> CREATE TABLE emp (emp_no NUMBER PRIMARY KEY,
    2 e_name VARCHAR2(20),
    3 dept_no NUMBER CONSTRAINT fk_ed
    4 REFERENCES dept (dept_no)
    5 INITIALLY DEFERRED DEFERRABLE);

    Table created.

    SQL> insert into emp (empno, ename, deptno) values (1, 'TYRO', 100);

    1 row created.
     
    Here even though there are no records in the 'dept' table, we did not get an error, but on Commit
    Code (Text):

    SQL> commit;
    commit
    *
    ERROR at line 1:
    ORA-02091: transaction rolled back
    ORA-02291: integrity constraint (MIKE.FK_ED) violated - parent key not found
     
    Now the simple solution to avoid all of these is to use CASCADE CONSTRAINTS
    Code (Text):

    SQL> drop table dept cascade constraints;
    SQL> drop table emp;
     
    See No Errors!

    Cheers! :D
     
  2. ShaheerBadar

    ShaheerBadar Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    thanks your