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

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

Issue :

Error “ORA-02266: unique/primary keys in table referenced by enabled foreign keys” when trying to truncate a table.

Error Message:

SQL> truncate table inventory_item;

truncate table inventory_item

               *

ERROR at line 1:

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

    

Oracle documentation says:

    

> oerr ora 02266

02266, 00000, "unique/primary keys in table referenced by enabled foreign keys"

// *Cause: An attempt was made to truncate a table with unique or                                                                                                                

//         primary keys referenced by foreign keys enabled in another table.

//         Other operations not allowed are dropping/truncating a partition of a

//         partitioned table or an ALTER TABLE EXCHANGE PARTITION.

// *Action: Before performing the above operations the table, disable the

//          foreign key constraints in other tables. You can see what

//          constraints are referencing a table by issuing the following

//          command:

//          SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

Solution:

-- Find the referenced foreign key constraints.

SQL> select 'alter table '||a.owner||'.'||a.table_name||' disable constraint '||a.constraint_name||';'

  2  from all_constraints a, all_constraints b

  3  where a.constraint_type = 'R'

  4  and a.r_constraint_name = b.constraint_name

  5  and a.r_owner  = b.owner

  6  and b.table_name = 'INVENTORY_ITEM';

'ALTERTABLE'||A.OWNER||'.'||A.TABLE_NAME||'DISABLECONSTRAINT'||A.CONSTRAINT_NAME||';'

---------------------------------------------------------------------------------------------------------

alter table CTRLCTR.INVENTORY_ITEM_ATTACHMENT disable constraint FK_INV_ITM_ATTCHMNT_INV_ITM_ID;

alter table CTRLCTR.INVENTORY_ITEM_LOCATION disable constraint FK_INV_ITEM_LOC_INV_ITM_ID;

alter table CTRLCTR.SERVICE_ORDER disable constraint FK_SERVICE_ORDER_INV_ITEM_ID;

-- Disable them.

SQL> alter table CTRLCTR.INVENTORY_ITEM_ATTACHMENT disable constraint FK_INV_ITM_ATTCHMNT_INV_ITM_ID;

Table altered.

SQL> alter table CTRLCTR.INVENTORY_ITEM_LOCATION disable constraint FK_INV_ITEM_LOC_INV_ITM_ID;

Table altered.

SQL> alter table CTRLCTR.SERVICE_ORDER disable constraint FK_SERVICE_ORDER_INV_ITEM_ID;

Table altered.

-- Run the truncate

SQL> truncate table inventory_item;

Table truncated.

-- Enable the foreign keys back

SQL> select 'alter table '||a.owner||'.'||a.table_name||' enable constraint '||a.constraint_name||';'

  2  from all_constraints a, all_constraints b

  3  where a.constraint_type = 'R'

  4  and a.r_constraint_name = b.constraint_name

  5  and a.r_owner  = b.owner

  6  and b.table_name = 'INVENTORY_ITEM';

'ALTERTABLE'||A.OWNER||'.'||A.TABLE_NAME||'ENABLECONSTRAINT'||A.CONSTRAINT_NAME||';'

----------------------------------------------------------------------------------------------------

alter table CTRLCTR.INVENTORY_ITEM_ATTACHMENT enable constraint FK_INV_ITM_ATTCHMNT_INV_ITM_ID;

alter table CTRLCTR.INVENTORY_ITEM_LOCATION enable constraint FK_INV_ITEM_LOC_INV_ITM_ID;

alter table CTRLCTR.SERVICE_ORDER enable constraint FK_SERVICE_ORDER_INV_ITEM_ID;

-- Enable them

SQL> alter table CTRLCTR.INVENTORY_ITEM_ATTACHMENT enable constraint FK_INV_ITM_ATTCHMNT_INV_ITM_ID;

Table altered.

SQL> alter table CTRLCTR.INVENTORY_ITEM_LOCATION enable constraint FK_INV_ITEM_LOC_INV_ITM_ID;

Table altered.

SQL> alter table CTRLCTR.SERVICE_ORDER enable constraint FK_SERVICE_ORDER_INV_ITEM_ID;

Table altered.