Thanks for the question, Michael.

Asked: December 20, 2000 - 9:50 pm UTC

Last updated: March 21, 2019 - 3:41 pm UTC

Version: 8.1.6

Viewed 50K+ times! This question is

You Asked

Tom ,
why truncating empty table generates the error ORA-02266 ?

SQL> select count(*) from t_data;

COUNT(*)
----------
0

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

SQL> delete t_data;

0 rows deleted.

Thanks,
Michael.

and Tom said...

You cannot truncate a table with an enabled foreign key that points to it. Truncate does not fire any triggers, does not validate any constraints. It does not care of the child table is empty or not -- in this case the child table might actually not be empty.

You would have to disable or drop the foreign keys that reference this table.

Here is an example:

ops$tkyte@DEV816> create table p ( x int primary key );
Table created.

ops$tkyte@DEV816> create table c ( y int primary key,
2 x int,
3 constraint c_fk foreign key(x) references p);
Table created.

ops$tkyte@DEV816> insert into c ( y ) values ( 1 );
1 row created.

ops$tkyte@DEV816> truncate table p;
truncate table p
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

ops$tkyte@DEV816> alter table c disable constraint c_fk;
Table altered.

ops$tkyte@DEV816> truncate table p;
Table truncated.

Now we can truncate since the foreign key is no longer enabled, all we need to do now is re-enable it which will also check it and make sure none of the rows in C point to a row in P

ops$tkyte@DEV816> alter table c enable constraint c_fk;
Table altered.

Alternatively, you can run a query to find all of the foriegn key constraints and generate an "alter table" command that would rebuild them. In this fashion, you can drop them, run the truncate and the rebuild them. This is handy if you do not know the constraints before hand -- it'll show you what they are:

ops$tkyte@DEV816> column fkey format a80 word_wrapped
ops$tkyte@DEV816> select
2 'alter table "' || child_tname || '"' || chr(10) ||
3 'add constraint "' || child_cons_name || '"' || chr(10) ||
4 'foreign key ( ' || child_columns || ' ) ' || chr(10) ||
5 'references "' || parent_tname || '" ( ' ||
parent_columns || ');' fkey
6 from
7 ( select a.table_name child_tname, a.constraint_name
child_cons_name,
8 b.r_constraint_name parent_cons_name,
9 max(decode(position, 1, '"'||
10 substr(column_name,1,30)||'"',NULL)) ||
11 max(decode(position, 2,', '||'"'||
12 substr(column_name,1,30)||'"',NULL)) ||
13 max(decode(position, 3,', '||'"'||
14 substr(column_name,1,30)||'"',NULL)) ||
15 max(decode(position, 4,', '||'"'||
16 substr(column_name,1,30)||'"',NULL)) ||
17 max(decode(position, 5,', '||'"'||
18 substr(column_name,1,30)||'"',NULL)) ||
19 max(decode(position, 6,', '||'"'||
20 substr(column_name,1,30)||'"',NULL)) ||
21 max(decode(position, 7,', '||'"'||
22 substr(column_name,1,30)||'"',NULL)) ||
23 max(decode(position, 8,', '||'"'||
24 substr(column_name,1,30)||'"',NULL)) ||
25 max(decode(position, 9,', '||'"'||
26 substr(column_name,1,30)||'"',NULL)) ||
27 max(decode(position,10,', '||'"'||
28 substr(column_name,1,30)||'"',NULL)) ||
29 max(decode(position,11,', '||'"'||
30 substr(column_name,1,30)||'"',NULL)) ||
31 max(decode(position,12,', '||'"'||
32 substr(column_name,1,30)||'"',NULL)) ||
33 max(decode(position,13,', '||'"'||
34 substr(column_name,1,30)||'"',NULL)) ||
35 max(decode(position,14,', '||'"'||
36 substr(column_name,1,30)||'"',NULL)) ||
37 max(decode(position,15,', '||'"'||
38 substr(column_name,1,30)||'"',NULL)) ||
39 max(decode(position,16,', '||'"'||
40 substr(column_name,1,30)||'"',NULL))
41 child_columns
42 from user_cons_columns a, user_constraints b
43 where a.constraint_name = b.constraint_name
44 and b.constraint_type = 'R'
45 group by a.table_name, a.constraint_name,
b.r_constraint_name ) child,
46 ( select a.constraint_name parent_cons_name, a.table_name
parent_tname,
47 max(decode(position, 1, '"'||
48 substr(column_name,1,30)||'"',NULL)) ||
49 max(decode(position, 2,', '||'"'||
50 substr(column_name,1,30)||'"',NULL)) ||
51 max(decode(position, 3,', '||'"'||
52 substr(column_name,1,30)||'"',NULL)) ||
53 max(decode(position, 4,', '||'"'||
54 substr(column_name,1,30)||'"',NULL)) ||
55 max(decode(position, 5,', '||'"'||
56 substr(column_name,1,30)||'"',NULL)) ||
57 max(decode(position, 6,', '||'"'||
58 substr(column_name,1,30)||'"',NULL)) ||
59 max(decode(position, 7,', '||'"'||
60 substr(column_name,1,30)||'"',NULL)) ||
61 max(decode(position, 8,', '||'"'||
62 substr(column_name,1,30)||'"',NULL)) ||
63 max(decode(position, 9,', '||'"'||
64 substr(column_name,1,30)||'"',NULL)) ||
65 max(decode(position,10,', '||'"'||
66 substr(column_name,1,30)||'"',NULL)) ||
67 max(decode(position,11,', '||'"'||
68 substr(column_name,1,30)||'"',NULL)) ||
69 max(decode(position,12,', '||'"'||
70 substr(column_name,1,30)||'"',NULL)) ||
71 max(decode(position,13,', '||'"'||
72 substr(column_name,1,30)||'"',NULL)) ||
73 max(decode(position,14,', '||'"'||
74 substr(column_name,1,30)||'"',NULL)) ||
75 max(decode(position,15,', '||'"'||
76 substr(column_name,1,30)||'"',NULL)) ||
77 max(decode(position,16,', '||'"'||
78 substr(column_name,1,30)||'"',NULL))
79 parent_columns
80 from user_cons_columns a, user_constraints b
81 where a.constraint_name = b.constraint_name
82 and b.constraint_type in ( 'P', 'U' )
83 group by a.table_name, a.constraint_name ) parent
84 where child.parent_cons_name = parent.parent_cons_name
85 and parent.parent_tname = upper('&1')
86 /
old 85: and parent.parent_tname = upper('&1')
new 85: and parent.parent_tname = upper('p')

FKEY
---------------------------------------------------------
alter table "C"
add constraint "C_FK"
foreign key ( "X" )
references "P" ( "X");

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sagar, October 01, 2007 - 2:21 am UTC

Why we are unable to truncate the parent table even when there are no records in child table?.What is the problem if  Oracle allowed to truncate the table when there are any enabled foreign keys where child table does not have data?.

Thanks in advance.

SQL> create table emp2 as select * from emp;

Table created.

SQL> create table dept2 as select * from dept ;

Table created.

SQL> alter table dept2 add primary key(deptno);

Table altered.

SQL> ed
Wrote file afiedt.buf

  1* alter table emp2 add constraint emp2fk  foreign key(deptno)  references dept2(deptno)
SQL> /

Table altered.

SQL> truncate table emp2 ;

Table truncated.

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

SQL> delete  from  dept2 ;

4 rows deleted.

SQL> commit ;

Commit complete.

October 03, 2007 - 2:36 pm UTC

because Oracle does not permit that DDL to take place, with data integrity constraints, you have to delete - or

a) disable foreign key
b) truncate
c) enable foreign key (pretty darn fast, nothing to check obviously)

the table might appear empty to you (the child table) but that doesn't mean it is empty - other transactions could have stuff being inserted in there that you just cannot see yet.

Stupid

Raj, March 25, 2011 - 6:39 am UTC

Stupid Statements

March 25, 2011 - 11:18 am UTC

I agree, you just posted a fine example of one!! bravo

no references

A reader, August 19, 2013 - 6:23 am UTC

and what could be a reason of the error if there is no indexes and no constraints on a table. I have partitioned by range table with one constraint (partitioning column cannot be null). The only not usual things are clob column and my own type column. the rest are varchar, timestamp and so on... any idea?

August 28, 2013 - 5:19 pm UTC

please give example.

create table
insert into table
commit
truncate table

showing the error.

Issuses about 11.2.0.1.0/11.2.0.3.0

garfield, March 21, 2019 - 7:22 am UTC

Hi Tom,

An ORA-02266 error occurs when the following SQL on 11.2.0.1.0, however, it runs successfully on 11.2.0.3.0 (even 12.1.0.2.0). Is this error raised on the specific oracle version?

Notes: There is NO row inserted after the table creation.

Thanks
-garfield

SQL> create table p ( x int primary key );
 
Table created
 
SQL> create table c ( y int primary key,
  2  x int,
  3  constraint c_fk foreign key(x) references p);
 
Table created
 
SQL> truncate table p;
 
truncate table p
 
ORA-02266: 表中的唯一/主键被启用的外键引用

March 21, 2019 - 3:41 pm UTC

This is likely due to deferred segment creation. By default creating a table doesn't create a segment. So truncate has nothing to do!

Create a segment when you create the table and you'll see the ORA-02266:

select banner from v$version;

BANNER                                                                   
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

create table p ( x int primary key )
  segment creation immediate;
 
create table c ( 
  y int primary key, x int,
  constraint c_fk foreign key(x) references p
);

truncate table p;

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

I thought this change came in 11.2.0.1, so it's likely the parameter DEFERRED_SEGMENT_CREATION is FALSE in your database of this version.

Cascade functionality for Truncate command.

Rajeshwaran Jeyabal, October 25, 2023 - 2:16 am UTC

Starting with 12.1 we got a feature called Cascade functionality for Truncate command that allow us to truncate parent table provided the child table got its FK defined as "on delete cascade"

demo@PDB1> alter table emp
  2  add constraint emp_fk
  3  foreign key(deptno)
  4  references dept;

Table altered.

demo@PDB1> truncate table dept CASCADE;
truncate table dept CASCADE
               *
ERROR at line 1:
ORA-14705: unique or primary keys referenced by enabled foreign keys in table "DEMO"."EMP"


demo@PDB1> alter table emp drop constraint emp_fk;

Table altered.

demo@PDB1> alter table emp
  2  add constraint emp_fk
  3  foreign key(deptno)
  4  references dept
  5  on delete cascade;

Table altered.

demo@PDB1> truncate table dept CASCADE;

Table truncated.

demo@PDB1> select * from emp;

no rows selected

demo@PDB1>

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Stupid

no references

Issuses about 11.2.0.1.0/11.2.0.3.0

Cascade functionality for Truncate command.

More to Explore

PL/SQL demos

PL/SQL docs

truncating empty table generate ORA-02266:

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Stupid

no references

Issuses about 11.2.0.1.0/11.2.0.3.0

Cascade functionality for Truncate command.

More to Explore

PL/SQL demos

PL/SQL docs