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

The Oracle (tm) Users' Co-Operative FAQ

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

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