HiveBrain v1.2.0
Get Started
← Back to all entries
patternModerate

Truncate parent table in Oracle when child table is empty

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
parentemptytruncatechildwhenoracletable

Problem

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?

Solution

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};

Code Snippets

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

Context

StackExchange Database Administrators Q#17615, answer score: 13

Revisions (0)

No revisions yet.