CODEWITHSUNDEEP
oracle-databasetruncate
zono
zono

Reputation: 8584

Oracle's specification or SQL's ? : Truncate table with foreign key constraints

I want to see a specification about "Truncate table with foreign key constraints" like below. Does anyone know where it is defined ?

On the minus side, if you have a foreign key constraint referring to the table you are trying to truncate, this won't work - even if the referring table has no data in it! This is because the foreign key checking is done with DDL rather than DML. This can be got around by temporarily disabling the foreign key constraint(s) to the table. http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

Upvotes: 0

Views: 1719

Answers (1)

Alex Poole
Alex Poole

Reputation: 191415

You mean the fourth bullet in the restrictions on truncating tables section of the Oracle's truncate documentation?

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.

Upvotes: 2

Related Questions