Truncate Table with Foreign Key constraint

Truncating the Tables i always a better option when we want to delete all rows in the table as Dropping and re-creating a table invalidates dependent objects of the table, requires you to re grant object privileges on the table, and requires you to re-create the indexes, integrity constraints, and triggers on the table and re specify its storage parameters. Truncating has none of these effects.

The "Truncate Table" also performs of following tasks

• Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter
• Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process 

But there is one challenge in truncating a table when there is referential integrity constraint involved. I have written a small script to first disable the foreign key constraint on the child table. After then perform the truncate operation and enable the foreign key constraints again. Lets perform a small demo.

Parent table:

SQL> CREATE TABLE TEST1 (

  2  id    NUMBER        PRIMARY KEY,

  3  name  VARCHAR2(20)  NOT NULL

  4  );

Table created.

Child table:

SQL> CREATE TABLE TEST2 (

  2  ctry_id         NUMBER        NOT NULL,

  3  cont_id         NUMBER        NOT NULL REFERENCES TEST1

  4  );

Table created.

Now While trying to truncate the Parent table. The following errors pops up

SQL> truncate table TEST1;

truncate table TEST1

               *

ERROR at line 1:

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

The very useful script disable all the foreign keys referencing the parent table (or list of tables to be truncated)

SQL> select 'alter table '||owner||'.'||TABLE_NAME||' disable constraint '||constraint_name ||';'

  2  r_constraint_name

  3  from dba_constraints

  4  where constraint_type='R'

  5  and r_constraint_name in

  6  (select constraint_name from dba_constraints

  7  where constraint_type in ('P','U') and table_name='TEST1');

R_CONSTRAINT_NAME

----------------------------------------------------------------------------------------------------

alter table SYSTEM.TEST2 disable constraint SYS_C00821334;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Disable the Foreign Keys identified by the Script:

SQL> alter table SYSTEM.TEST2 disable constraint SYS_C00821334;

Table altered.

The table truncate is successful now

SQL> truncate table TEST1;

Table truncated.

The script Enable all the foreign keys referencing the parent table (or list of tables)

SQL> select 'alter table '||owner||'.'||TABLE_NAME||' ENABLE NOVALIDATE constraint '||constraint_name ||';'

  2  r_constraint_name

  3  from dba_constraints

  4  where constraint_type='R'

  5  and r_constraint_name in

  6  (select constraint_name from dba_constraints

  7  where constraint_type in ('P','U') and table_name='TEST1');

R_CONSTRAINT_NAME

----------------------------------------------------------------------------------------------------

alter table SYSTEM.TEST2 ENABLE NOVALIDATE constraint SYS_C00821334;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Finally Enable the Foreign Keys identified by the Script:

SQL> alter table SYSTEM.TEST2 ENABLE NOVALIDATE constraint SYS_C00821334;

Table altered.