The Oracle (tm) Users' Co-Operative FAQ

How do I truncate a table which is the parent for a foreign key relation ?


Author's name: Connor McDonald

Author's Email: connor_mcdonald@yahoo.com

Date written: August 22, 2001

Oracle version(s): 7.3+

How do I truncate a table which is the parent for a foreign key relation ?

Back to index of questions


The only way to truncate a table that is the parent in a foreign-key relationship is to disable the foreign key (even if both tables contain no data), as the following example shows:

SQL> create table a_parent ( x number primary key );

Table created.

SQL> create table a_child ( y number, x number );

Table created.

SQL> alter table a_child add constraint fk_cons
  2  foreign key (x ) references a_parent ( x );

Table altered.

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


SQL> alter table a_child disable constraint fk_cons;

Table altered.

SQL> truncate table a_parent;

Table truncated.

Of course, the next step should be to re-enable the FK_CONS constraint. If you can "guarantee" that the truncate was done in a "quiet" time (for example, the database is in restricted mode), then the constraint can be re-enabled in NOVALIDATE mode. If there is any chance that some illegal data could have made its way into the A_CHILD table, it makes sense to follow this with a VALIDATE.

Note that locking the parent table does not lower the risk of illegal data to be entered, since every DDL you issue (the alter, the truncate, etc) will immediately commit and thus release any lock that you had.


Further reading: N/A


Back to top

Back to index of questions