Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 7 down vote accepted

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};
share|improve this answer

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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