Structured Query Language/TRUNCATE

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 significantly better performance than with DELETE command. This results from the fact that 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 Foreign-Key-constraint from table t1 to t2, the command  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 Forgeign-Key-constraint definition.

The syntax of the TRUNCATE TABLE command is very simple.

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 empty the trailer by tilting it - this corresponds to the TRUNCATE TABLE command -, or climb onto the trailer and throw down one grain of sand after the next - this corresponds to the DELETE command.

Exercises
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'.)

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

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