Is truncation of parent table restricted even when child table is empty?

Question

I can use delete from table_name which works.
Why not truncate?

Foreign-key reference... second-half of your answer... – Pop Stack Mar 02 '12 at 11:21 — Pop Stack, Mar 02 '12 at 11:21
Related question: http://stackoverflow.com/q/9519432 – Alex Poole Mar 02 '12 at 12:13 — Alex Poole, Mar 02 '12 at 12:13

Ollie · Accepted Answer · 2012-03-02T11:10:08.433

5

Try:

TRUNCATE TABLE <tablename>;

after disabling the FK constraint.

Truncating cannot be rolled back as it is DDL rather than DML so be careful. It also resets the highwater mark of the table being truncated.

see here for the Oracle docs explanation. and here for a comparison.

N.B.: 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.

From the Oracle documantation.

Hope it helps...

edited Mar 02 '12 at 11:10

answered Mar 02 '12 at 11:03

Ollie

17,058
7
48
59

`TRUNCATE` also resets identity columns – ntziolis Mar 02 '12 at 11:05
@ntziolis: Oracle doesn't have "identity" columns. – Jeffrey Kemp Mar 12 '12 at 08:54

Is truncation of parent table restricted even when child table is empty?

1 Answers1