ORA-02266: unique/primary keys in table referenced by enabled foreign keys

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

SQL>select 'alter table '||a.owner||'.'||a.table_name||' disable constraint '||a.constraint_name||';'
    from all_constraints a, all_constraints b
    where a.constraint_type = 'R'
    and a.r_constraint_name = b.constraint_name
    and a.r_owner  = b.owner
    and b.table_name = 'DEPT';

'ALTERTABLE'||A.OWNER||'.'||A.TABLE_NAME||'DISABLECONSTRAINT'||A.CONSTRAINT_NAME||';'
---------------------------------------------------------------------------------------------------------------------------------------------------
alter table SCOTT.EMP disable constraint FK_DEPTNO;

SQL> alter table SCOTT.EMP disable constraint FK_DEPTNO;

Table altered.

SQL> truncate table DEPT;

Table truncated.

SQL>select 'alter table '||a.owner||'.'||a.table_name||' enable constraint '||a.constraint_name||';'
    from all_constraints a, all_constraints b
    where a.constraint_type = 'R'
    and a.r_constraint_name = b.constraint_name
    and a.r_owner  = b.owner
    and b.table_name = 'DEPT';

'ALTERTABLE'||A.OWNER||'.'||A.TABLE_NAME||'ENABLECONSTRAINT'||A.CONSTRAINT_NAME||';'
----------------------------------------------------------------------------------------------------------------------------------
alter table SCOTT.EMP enable constraint FK_DEPTNO;

SCOTT@dun 12:07:00> alter table SCOTT.EMP enable novalidate constraint FK_DEPTNO;
alter table SCOTT.EMP enable constraint FK_DEPTNO
                                        *
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.FK_DEPTNO) - parent keys not found

-- Cause: an alter table validating constraint failed because the table has orphaned child records.
-- Solution 1: delete child record
-- Solution 2: enable constraint with novalidate

SQL>select 'alter table '||a.owner||'.'||a.table_name||' enable novalidate constraint '||a.constraint_name||';'
    from all_constraints a, all_constraints b
    where a.constraint_type = 'R'
    and a.r_constraint_name = b.constraint_name
    and a.r_owner  = b.owner
    and b.table_name = 'DEPT';

'ALTERTABLE'||A.OWNER||'.'||A.TABLE_NAME||'ENABLENOVALIDATECONSTRAINT'||A.CONSTR
--------------------------------------------------------------------------------
alter table SCOTT.EMP enable novalidate constraint FK_DEPTNO;

SQL> alter table SCOTT.EMP enable novalidate constraint FK_DEPTNO;

Table altered.
Comments