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