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. |