Truncate table in Oracle getting errors

I got the problem is when I run following command in Oracle, I encounter the error.

Truncate table mytable;

Errors:

ORA-02266: unique/prim开发者_开发问答ary keys in table referenced by enabled foreign keys

I found that, this mytable has relationship with other tables. That's why Truncate command cannot proceed anymore. How to delete data from myTable with the SQL scripts using Truncate command?

You have to swap the TRUNCATE statement to DELETE statements, slower and logged but that's the way to do it when constraints are in place.

DELETE mytablename;

Either that or you can find the foreign keys that are referencing the table in question and disable them temporarily.

select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||CONSTRAINT_NAME||';'
from user_constraints
where R_CONSTRAINT_NAME='<pk-of-table>';

Where pk-of-table is the name of the primary key of the table being truncated

Run the output of the above query. When this has been done, remember to enable them again, just change DISABLE CONSTRAINT into ENABLE CONSTRAINT

this page offers a very good solution ...

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

I'm here copying from it the Solution:

• Find the referenced ENABLED foreign key constraints and disable them.
• truncate/delete from the table .

• using any text editor .. just change disable to enable in the output you get from the query , then run it.

  select 'alter table '||a.owner||'.'||a.table_name||' disable constraint '||a.constraint_name||';'
  from all_constraints a, all_constraints b
  where a.constraint_type = 'R' and a.status='ENABLED'
  and a.r_constraint_name = b.constraint_name
  and a.r_owner  = b.owner
  and b.table_name = upper('YOUR_TABLE');
  

The error message is telling you that there are other table(s) with a foreign key constraint referring to your table.

According to the Oracle docs

You cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table.

The syntax for disabling a foreign key is:

ALTER TABLE table_name disable CONSTRAINT constraint_name;

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 TABLE_NAME;  

truncate table TABLE_NAME
           *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

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 = 'TABLE_NAME';

    'ALTER TABLE'||A.OWNER||'.'||A.TABLE_NAME||'DISABLE CONSTRAINT'||A.CONSTRAINT_NAME||';'
    ---------------------------------------------------------------------------------------------------------
    alter table SCHEMA_NAME.TABLE_NAME_ATTACHMENT disable constraint CONSTRAINT_NAME;    
alter table SCHEMA_NAME.TABLE_NAME_LOCATION disable constraint CONSTRAINT_NAME;

-- Disable them

alter table SCHEMA_NAME.TABLE_NAME_ATTACHMENT disable constraint CONSTRAINT_NAME;
alter table SCHEMA_NAME.TABLE_NAME_LOCATION disable constraint CONSTRAINT_NAME;

-- Run the truncate

SQL> truncate table TABLE_NAME;

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 = 'TABLE_NAME';

'ALTER TABLE'||A.OWNER||'.'||A.TABLE_NAME||'ENABLE CONSTRAINT'||A.CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------

alter table SCHEMA_NAME.TABLE_NAME_ATTACHMENT enable constraint CONSTRAINT_NAME;
alter table SCHEMA_NAME.TABLE_NAME_LOCATION enable constraint CONSTRAINT_NAME;

-- Enable them

alter table SCHEMA_NAME.TABLE_NAME_ATTACHMENT enable constraint CONSTRAINT_NAME;
alter table SCHEMA_NAME.TABLE_NAME_LOCATION enable constraint CONSTRAINT_NAME;

Oracle 12c introduced a feature to truncate a table that is a parent of a referential integrity constraint having ON DELETE rule.

Instead of truncate table tablename; use:

TRUNCATE TABLE tablename CASCADE;

From Oracle truncate table documentation:

If you specify CASCADE, then Oracle Database truncates all child tables that reference table with an enabled ON DELETE CASCADE referential constraint. This is a recursive operation that will truncate all child tables, granchild tables, and so on, using the specified options.

A typical approach to delete many rows with many constraints is as follows:

• create mytable_new with all the columns but without constrains (or create constraints disabled);
• copy whatever data you need from mytable to mytable_new.
• enable constraints on mytable_new to see that everything is ok.
• alter any constraints that reference mytable to reference mytable_new instead and see that everything is ok.
• drop table mytable.
• alter table mytable_new rename to mytable.

It's far faster than deleting a million records with many slow constraints.

I had the similar issue and I sorted it out by the following scripts.

begin
for i in (select constraint_name, table_name from user_constraints a where a.owner='OWNER' and a.table_name not in
(select b.table_name from user_constraints b where b.table_name like '%BIN%') 
  and  a.constraint_type not in 'P')
LOOP
  execute immediate 'alter table '||i.table_name||' disable constraint '||i.constraint_name||'';
end loop;
end;
/

truncate table TABLE_1;
truncate table TABLE_2;


begin
for i in (select constraint_name, table_name from user_constraints a where a.owner='OWNER' and a.table_name not in
(select b.table_name from user_constraints b where b.table_name like '%BIN%') 
  and  a.constraint_type not in 'P')
LOOP
  execute immediate 'alter table '||i.table_name||' enable constraint '||i.constraint_name||'';
end loop;
end;
/

This script will first disable all the Constraints. Truncates the data in the tables and then enable the contraints.

Hope it helps.

cheers..

TRUNCATE TABLE TEST2 DROP ALL STORAGE;

This statement Actually works when there is an foreign key constraint applied on a .table

As mentioned by the error message, you cannot truncate a table that is referenced by enabled foreign keys. If you really want to use the truncate DDL command, disable the foreign key constraint first, run the truncate command, and enable it back.

Reference: Difference between TRUNCATE, DELETE and DROP commands