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

  • by

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_constraint_name in (select constraint_name from all_constraints where table_name = 'EMPLOYEES');

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

Disable each reference key constraint one by one.

SQL> alter table HR.EMPLOYEES disable constraint EMP_MANAGER_FK;

Table altered.

SQL> alter table HR.DEPARTMENTS disable constraint DEPT_MGR_FK;

Table altered.

SQL> alter table HR.JOB_HISTORY disable constraint JHIST_EMP_FK;

Table altered.

SQL> alter table OE.ORDERS disable constraint ORDERS_SALES_REP_FK;

Table altered.

SQL> alter table OE.CUSTOMERS disable constraint CUSTOMERS_ACCOUNT_MANAGER_FK;

Table altered.

We can truncate the table now.

SQL> truncate table hr.employees;

Table truncated.

ORA-02266 is solved.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *