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

##########################
## Error
##########################

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

### Full Error

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

##########################
#  Error Occurred
##########################

Error occured while truncating a table in 11.2.0.4.0 version

##########################
## Command Executed
##########################

truncate table ABC_OWNER.TAB1;

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check the table relationship
#########################################

set lines 200
col owner for a20
col status for a10
col deferrable for a15
col validated for a18
col generated for a15
col deferred for a15

select owner,constraint_name,constraint_type,table_name,status,DEFERRABLE,DEFERRED,VALIDATED,GENERATED from dba_constraints where table_name='TAB1' and constraint_type in ('R','P');

OWNER                CONSTRAINT_NAME                C TABLE_NAME                     STATUS     DEFERRABLE      DEFERRED        VALIDATED          GENERATED
-------------------- ------------------------------ - ------------------------------ ---------- --------------- --------------- ------------------ ---------------
ABC_OWNER            PK_TAB1                        P TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME
ABC_OWNER            FK_TAB1_30                     R TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME
ABC_OWNER            FK_ERROR_3A                    R TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

"TAB1" table has relationship with other tables. From the above output we can see it has a primary constraint and 2 referential constraints(Child).

##########################
## Solution
##########################

Solution would be to disable the constraints and then proceed with the truncate. This is a test environment and i'm doing some testing with the database export/import.

So i'm not worried about the data in it. But in case if you are worried about the data integrity then, best way is to find out what are the child tables and make sure this data is not required anymore and then perform your activity.

select 'alter table '||owner||'.'||table_name ||' disable constraint '||constraint_name||' cascade;' from dba_constraints where constraint_type in ('R','P') and table_name in ('TAB1') order by table_name;

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||'CASCADE;'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter table ABC_OWNER.TAB1 disable constraint PK_TAB1 cascade;
alter table ABC_OWNER.TAB1 disable constraint FK_TAB1_30 cascade;
alter table ABC_OWNER.TAB1 disable constraint FK_ERROR_3A cascade;

=====================================================================================================================
After Disabling the Constraints, truncate command worked.
=====================================================================================================================

SQL>  truncate table ABC_OWNER.TAB1;

Table truncated.

SQL>

OWNER                CONSTRAINT_NAME                C TABLE_NAME                     STATUS     DEFERRABLE      DEFERRED        VALIDATED          GENERATED
-------------------- ------------------------------ - ------------------------------ ---------- --------------- --------------- ------------------ ---------------
ABC_OWNER            PK_TAB1                        P TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME
ABC_OWNER            FK_TAB1_30                     R TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME
ABC_OWNER            FK_ERROR_3A                    R TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================