Truncate command on a table which is referenced by FOREIGN KEY?

Question

Can we use Truncate command on a table which is referenced by FOREIGN KEY?

Aarti · Answer

Hi Akash, yes you can use truncate Command on a table which is referenced by Foreign Key by applying DELETE. Cascade If the FOREIGN KEY constraint specifies DELETE CASCADE , rows from the child (referenced) table are deleted, and the truncated table becomes empty. If the FOREIGN KEY constraint does not specify CASCADE , the Truncate table statement deletes rows one by one and stops if it encounters a parent row that is referenced by the child, returning this error. Thanks.

Pravin More · Answer

i think yes OR The other option would be like delete the child records first and afterwards the content of the parent entitiy.

Akash Ahlawat · Answer

Means I can truncate the table only by removing the foreign key constraint..

Pravin More · Answer

Hi Akash, Yes but TRUNCATE won´t work while being referenced by Foreign key constraints. So first drop the constraints, truncate the table and recreate the constraints. The other option would be like delete the child records first and afterwards the content of the parent entitiy. Thanks, Pravin.

Tulasi · Answer

Hi, you cannot truncate a table which has an FK constraint on it. one way is: Drop the constraints Trunc the table Recreate the constraints. there is no other choise to truncate table which has FK Contraint on it .