The TRUNCATE TABLE command deletes all rows of a table without causing any triggered action. Unlike the DELETE command it contains no WHERE clause to specify individual rows.

With respect to the TRUNCATE TABLE command most DBMS show significant better performance than with DELETE command. This results from the facts the DBMS can empty the table (and its indexes) as a whole. It's not necessary to access individual rows.

  • There is - per definition - no WHERE clause.
  • No trigger action will be launched - per definition.
  • The transaction locks the complete table.
  • If there is an FK-constraint from table t1 to t2, the command 'TRUNCATE TABLE t2' will fail. This holds true independent from the question whether any row of t1 refers actually to one of the rows of t2 or not. The DBMS checks only the existence of the FK-constraint definition.

The syntax of the TRUNCATE TABLE command is very simple.

TRUNCATE TABLE <tablename>;


-- Delete ALL rows of the table 'myTable'
-- In most DBMS ROLLBACK is not possible - in opposite to situations with a DELETE command.

An Analogy

To illustrate the difference between the TRUNCATE TABLE command and the DELETE command (without a WHERE clause) one can imagine a trucker, who wants to empty a trailer full of sand at a construction site. To do so he has two possibilities. Either he empties the trailer in that he tilts him - this corresponds to the TRUNCATE TABLE command. Or he climbs onto the trailer and throws down one grain of sand after the next - this corresponds to the DELETE command.


Delete all rows of table 'person_hobby' using the DELETE command.
Verify that there are no rows left in 'person_hobby'.
Delete all rows of table 'hobby' using the TRUNCATE TABLE command.
What will happen? (Consider that there is an FK constraint from the table empty 'person_hobby' to 'hobby'.)

Click to see solution
-- Delete all rows of 'person_hobby' with a DELETE command
DELETE FROM person_hobby;

-- Are there any rows?
SELECT count(*) FROM person_hobby;

-- Try TRUNCATE TABLE command:
-- An exception will be thrown. Although there is no row in 'person_hobby' refering a row in 'hobby',
-- the definition of the FK constraint exists. This is the reason for the exception.

What will happen in the above example, if the TRUNCATE TABLE command is replaced by a DELETE command?

Click to see solution
-- As there is no row in 'person_hobby' refering to 'hobby', the DELETE command deletes all rows in 'hobby'.

The original data of the example database can be reconstructed as shown on the example database data page.

  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.



Connect with defaultLogic
What We've Done
Led Digital Marketing Efforts of Top 500 e-Retailers.
Worked with Top Brands at Leading Agencies.
Successfully Managed Over $50 million in Digital Ad Spend.
Developed Strategies and Processes that Enabled Brands to Grow During an Economic Downturn.
Taught Advanced Internet Marketing Strategies at the graduate level.

Manage research, learning and skills at Create an account using LinkedIn to manage and organize your omni-channel knowledge. is like a shopping cart for information -- helping you to save, discuss and share.

  Contact Us