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

Tweet

Few days ago I received an email from a user who had two empty table, one referenced by the other. He was not able to execute a truncate command on the parent table and asked me to solve his error: ORA-02266: unique/primary keys in table referenced by enabled foreign keys.

Truncate command is very useful when you want to completely remove all data from a table: for very large tables it is usually the best way to remove your data.
When you use the TRUNCATE command Oracle sets back to zero the HWM (high-water mark) of the table and, compared with the DELETE command, less UNDO and REDO information is generated.
You can query the dba_extents to see how many extents are allocated for your table.

However using TRUNCATE command has some "disadvantages": first you have to remember it's a DDL command so it will commit automatically any pending transactions. Another one is that you can receive the following error when the table you are truncating is referenced by an "enabled" foreign key: ORA-02266: unique/primary keys in table referenced by enabled foreign keys.
To successfully complete your TRUNCATE command you have to temporarily disable the foreign key.

Let's see it with an example.
Create two tables: one table (hr.parent) has one primary key referenced by a second table (hr.child) using a constraint.

SQL> create table hr.parent (a number);

Table created.

SQL> create table hr.child (b number, a_ref number);

Table created.

SQL> create index parent_pk on hr.parent(a);

Index created.

SQL> alter table hr.parent add constraint parent_pk primary key (a);

Table altered.

SQL> alter table hr.child add constraint child_fk foreign key (a_ref) references hr.parent (a);

Table altered.

No extent is allocated for PARENT table. I'm working with Oracle Database 11G R2, so have a look at deferred_segment_creation initialization parameter to justify why you have zero extent allocated (or read this link)

SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

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

And no rows still are created.

SQL> select * from hr.parent;

no rows selected

SQL> select count(*) from hr.child;

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

In this situation we can truncate our PARENT table with any problem.

SQL> truncate table hr.parent;

Table truncated.

But what does it happen when records are inserted into it ? Let's populate PARENT table with some data:

SQL> begin
  2  for  indx IN 1 .. 10000
  3  loop
  4      execute immediate 'insert into hr.parent values (:1)' using indx;
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

PARENT table is now formed by 3 extents.

SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         3

As you can see we are no more allowed to truncate PARENT table even if any data on CHILD table are referencing to it.

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

SQL> select count(*) from hr.child;

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

To successfully complete the truncate command we have to disable the foreign key...

SQL> alter table hr.child modify constraint child_fk disable;

Table altered.

... execute the truncate command...

SQL>  truncate table hr.parent;

Table truncated.

.. and enable again the foreign key.

SQL> alter table hr.child modify constraint child_fk enable;

Table altered.

PARENT table is now formed by one extent.

SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         1

Let's populate again PARENT table with 10000 records:

SQL> begin
  2  for  indx IN 1 .. 10000
  3  loop
  4      execute immediate 'insert into hr.parent values (:1)' using indx;
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

PARENT table is formed by 3 extents.

SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         3

Let's see what it happens to the HWM using delete command.

SQL> delete from hr.parent;

10000 rows deleted.

SQL> commit;

Commit complete.

PARENT table is always formed by 3 extents.

SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         3

It's still not possible to truncate PARENT table...

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

... even when no rows are available and referenced.

SQL> select count(*) from hr.parent;

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

SQL> select count(*) from hr.child;

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

You can truncate PARENT table again only if foreign key is disabled

SQL> alter table hr.child modify constraint child_fk disable;

Table altered.

SQL> truncate table hr.parent;

Table truncated.

PARENT table is now formed by 1 extents.

SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         1

If you want to see the extent allocated as soon as you create PARENT table, you have to use the "segment creation immediate" option on your create table statement.

SQL> drop table hr.child; 

Table dropped.

SQL> drop table hr.parent;

Table dropped.

SQL> create table hr.parent (a number) segment creation immediate;

Table created.

SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         1

That's all.