Oracle in World: Troubleshoot ORA-02292, ORA-02449 and ORA-02266

SQL> delete from task;
delete from task
*
ERROR at line 1:
ORA-02292: integrity constraint (OMS.TASKUSER_TASK) violated - child record
found

SQL> drop table task;
drop table task
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> truncate table task;
truncate table task
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

To get list of child table with the constraint name that is referencing the TASK table issue,

SQL> set lines 140
SQL> SELECT p.table_name "Parent Table", c.table_name "Child Table",
p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
FROM user_constraints p
JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.table_name = UPPER('&table_name');
Enter value for table_name: task
old   7: AND p.table_name = UPPER('&table_name')
new   7: AND p.table_name = UPPER('task')

Parent Table                   Child Table                    Parent Constraint              Child Constraint
------------------------------ ------------------------------ ------------------------------ ------------------------------
TASK                           TASK_REF                       TASK_PK                        TASKREF_TASK
TASK                           TASK                           TASK_PK                        TASK_TASK_FK
TASK                           TASK_SCHEDULE                  TASK_PK                        TASKSCHEDULE_TASK
TASK                           TASK_USER                      TASK_PK                        TASKUSER_TASK
TASK                           TASK_ATTACHED_FILEINFO         TASK_PK                        TASK_ATTACH_FILE_FK

Let's start by disabling constraint from the child table one by one and try to delete rows from the table TASK.

SQL> ALTER TABLE TASK_USER DISABLE CONSTRAINT TASKUSER_TASK;

Table altered.

SQL> delete from task;
delete from task
*
ERROR at line 1:
ORA-02292: integrity constraint (OMS.TASKSCHEDULE_TASK) violated - child record found

SQL> ALTER TABLE task_schedule DISABLE CONSTRAINT TASKSCHEDULE_TASK;

Table altered.

SQL> delete from task;
delete from task
*
ERROR at line 1:
ORA-02292: integrity constraint (OMS.TASK_ATTACH_FILE_FK) violated - child record found

SQL> ALTER TABLE TASK_ATTACHED_FILEINFO DISABLE CONSTRAINT TASK_ATTACH_FILE_FK;

Table altered.

SQL> delete from task;

1289 rows deleted.

And now delete succeed. In order to get a list of parent tables that is referencing a child table issue,

SQL> SELECT c.table_name "Child Table", p.table_name "Parent Table",
p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
FROM user_constraints p JOIN user_constraints c
ON (p.constraint_name = c.r_constraint_name)
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND c.table_name = UPPER('&table_name');
Enter value for table_name: task
old   7: AND c.table_name = UPPER('&table_name')
new   7: AND c.table_name = UPPER('task')

Child Table                    Parent Table                   Parent Constraint              Child Constraint
------------------------------ ------------------------------ ------------------------------ ------------------------------
TASK                           TASK                           TASK_PK                        TASK_TASK_FK

Oracle in World

Wednesday, December 17, 2008

Troubleshoot ORA-02292, ORA-02449 and ORA-02266

1 comment: