Segment Creation Deferred and ORA-02266

If you try to truncate a table with a primary key which is referenced by an enabled foreign key, you usually get an ORA-02266 error. This happens straight away if the table is set up with segment creation immediate. However, if the table is set up with segment creation deferred, the error is not reported until the segment has been created. You can see what I mean in the example below, which I tested on Oracle 11.2:

 

SQL> create table dept1

  2  (dept_code varchar2(4),

  3   dept_desc varchar2(20),

  4   constraint dept1_constraint

  5   primary key(dept_code))

  6   segment creation deferred

  7  /

 

Table created.

 

SQL> create table emp1

  2  (empno varchar2(6),

  3   dept_code varchar2(4),

  4   constraint emp1_constraint

  5   foreign key (dept_code)

  6   references dept1(dept_code))

  7  /

 

Table created.

 

SQL> select count(*) from dba_segments

  2  where segment_name = 'DEPT1'

  3  /

 

  COUNT(*)

----------

         0

 

SQL> truncate table dept1

  2  /

 

Table truncated.

 

SQL> insert into dept1 values('0001','SALES')

  2  /

 

1 row created.

 

SQL> select count(*) from dba_segments

  2  where segment_name = 'DEPT1'

  3  /

 

  COUNT(*)

----------

         1

 

SQL> truncate table dept1

  2  /

truncate table dept1

               *

ERROR at line 1:

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

enabled foreign keys

 

SQL> create table dept2

  2  (dept_code varchar2(4),

  3   dept_desc varchar2(20),

  4   constraint dept2_constraint

  5   primary key(dept_code))

  6   segment creation immediate

  7  /

 

Table created.

 

SQL> create table emp2

  2  (empno varchar2(6),

  3   dept_code varchar2(4),

  4   constraint emp2_constraint

  5   foreign key (dept_code)

  6   references dept2(dept_code))

  7  /

 

Table created.

 

SQL> truncate table dept2

  2  /

truncate table dept2

               *

ERROR at line 1:

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

enabled foreign keys

 

SQL>