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.