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

ORA-02266

ORA-02266 means that you can't truncate the table because reference keys constraint still depend on the data of the table.

Truncate a Single Table

Let's see a case that throws ORA-02266.

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

Solution to ORA-02266

To solve ORA-02266, we need to know what reference keys are preventing us from truncating the table and furthermore, we should disable them.

Connect as a privileged user, this is because a privileged user can find all reference keys to the table in the database globally.

SQL> conn / as sysdba
Connected.

We compose disabling reference key statements for this table.

SQL> set hea off;
SQL> select 'alter table "' || owner || '"."' || table_name || '" disable constraint "' || constraint_name ||'";' stmt from all_constraints where r_owner= 'HR' and r_constraint_name in (select constraint_name from all_constraints where owner = 'HR' and table_name = 'EMPLOYEES') order by 1;

alter table "HR"."DEPARTMENTS" disable constraint "DEPT_MGR_FK";
alter table "HR"."EMPLOYEES" disable constraint "EMP_MANAGER_FK";
alter table "HR"."JOB_HISTORY" disable constraint "JHIST_EMP_FK";
alter table "OE"."CUSTOMERS" disable constraint "CUSTOMERS_ACCOUNT_MANAGER_FK";
alter table "OE"."ORDERS" disable constraint "ORDERS_SALES_REP_FK";

Disable each reference key constraint one by one.

SQL> alter table "HR"."DEPARTMENTS" disable constraint "DEPT_MGR_FK";

Table altered.

SQL> alter table "HR"."EMPLOYEES" disable constraint "EMP_MANAGER_FK";

Table altered.

SQL> alter table "HR"."JOB_HISTORY" disable constraint "JHIST_EMP_FK";

Table altered.

SQL> alter table "OE"."CUSTOMERS" disable constraint "CUSTOMERS_ACCOUNT_MANAGER_FK";

Table altered.

SQL> alter table "OE"."ORDERS" disable constraint "ORDERS_SALES_REP_FK";

Table altered.

We can truncate the table now.

SQL> truncate table hr.employees;

Table truncated.

ORA-02266 is solved.

Later on, you may like to enable those constraints back.

Truncate a Bunch of Tables

Got ORA-02266 when import data in schema mode with TABLE_EXISTS_ACTION=TRUNCATE.

[oracle@test ~]$ impdp system/password schemas=hr,oe,sh content=data_only table_exists_action=truncate network_link=system_link_orcl
...
ORA-39120: Table "OE"."PRODUCT_INFORMATION" can't be truncated, data will be skipped. Failing error is:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
...

You might consider to add DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS for this import, but this option cannot suppress referential constraints with TABLE_EXISTS_ACTION=TRUNCATE. Therefore, you have to disable referential constraints manually.

Solution to ORA-02266

Here is my sample script for disabling foreign key constraints.

[oracle@test ~]$ vi disable_constraints.sh
sqlplus -s / as sysdba << EOF
set heading off feedback off pagesize 0 linesize 150 echo off
spool /home/oracle/disable_constraint_test.sql
select 'alter table "' || owner || '"."' || table_name || '" disable constraint ' || constraint_name || ';' stmt from dba_constraints where r_owner in ('HR', 'OE', 'SH') and constraint_type = 'R' and status = 'ENABLED' order by owner, table_name;
spool off
set echo on feedback on
@/home/oracle/disable_constraint_test.sql
exit
EOF

In the above, you can change the schema name for your condition.

Don't forget to make it executable.

[oracle@test ~]$ chmod u+x disable_constraints.sh

Then execute the script.

[oracle@test ~]$ ./disable_constraints.sh
alter table "PM"."ONLINE_MEDIA" disable constraint LOC_C_ID_FK;
alter table "PM"."PRINT_MEDIA" disable constraint PRINTMEDIA_FK;

Table altered.


Table altered.

Now, you can import the data again. And don't forget to enable them back once import is completed.

. . imported "OE"."PRODUCT_INFORMATION"                     288 rows

Additionally, you may need to disable triggers before import as well.

If you don't want to disable triggers or constraints, you should use table_exists_action=replace to dump the data instead. The import job will conduct the order of objects to be import.