Friday, August 3, 2012

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.



4 comments:

  1. Hi,

    While truncation of table the first 2 steps works fine.

    3rd one as below returns error.

    SQL> alter table PEOPLES_PROD_TMS.PA_MAIL_ATTMT enable constraint FK_MAIL_ATTMT__MAIL;
    *
    ERROR at line 1:
    ORA-02298: cannot validate (PEOPLES_PROD_TMS.FK_MAIL_ATTMT__MAIL) - parent keys not found

    ReplyDelete
    Replies
    1. In my case, I had to truncate to reload data again and the FK was enabled after data reload. There were no orphaned child records.

      You are getting the error since you have orphaned child records and it is obvious..
      Fix depends on what you are/were trying to achieve.. The key was there to preserve application logic and I do not know the reason why you had to truncate the primary table. Were you doing a data cleanup ? Were you going to load new data ?

      You can add parent records in the primary table.
      You can delete orphaned records in the child table.
      If your logic is to enforce integrity for new records and not worry about existing records, you can use the "enable novalidate" option.

      Refer
      http://docs.oracle.com/cd/E11882_01/server.112/e25494/general005.htm#ADMIN11537
      http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_constraints.htm#i1006697

      Delete
  2. how to make constraint name same for all user?
    in my case constraint name is SYS_C00113015. and when i creating again user this value will change.
    how to solve this issue? please help

    ReplyDelete
  3. Nicely written.. will surely help .. but i came across different solution to same ORA- error

    http://www.moreajays.com/2020/06/ora-02266-uniqueprimary-keys-in-table.html

    ReplyDelete