3

Suppose I have a parent table parent referenced by a child table child. The table parent is populated but child is not. Attempting to truncate parent results in

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

Is there a way to hint to the DBMS that child is empty, so that the foreign key constraint doesn't need to be disabled?

11

No, there is no such hint. What you can do is

alter table {child_table} disable constraint {fk_constraint_name};
truncate table {parent_table};
alter table {child_table} enable constraint {fk_constraint_name};
7

The restrictions on truncating tables include:

You cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table. An exception is that you can truncate the table if the integrity constraint is self-referential.

This is presumably because truncate is DDL and doesn't do any checks on data in the target table, even to see if it has any rows. It would have to do that DML, and look for any matching rows in all child tables (or at least check there are no rows), etc, which would change the nature of the command and potentially impact performance. And it would have to consider uncommitted transactions against the child tables, which would have expected an error on insert rather than commit if the constraint wasn't defferred.

The only way to do this is to disable or drop the constraint, as @ik_zelf shows.

-2

There is no way round the restriction with truncate as other answers have indicated, but you can of course use delete without disabling the constraints:

delete from parent;

Note that 'delete' and 'truncate' have different side-effects

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.