DELETE vs TRUNCATE vs DROP

Delete is used to delete the records from a table. A where clause can be put in delete statement. For example if you want to delete all records from a users table, than the SQL will be

delete from users;

A where clause can be put to delete only users having first_name start with a

delete from users where first_name like 'a%';

With delete call, triggers are fired and indexes are updated. Also it puts a lock on the table. If the table contains a large number of records, delete statement can take a good amount of time. In those

situations truncate can be used.

To run truncate command on table, use the following SQL.

truncate table users;

Truncate removes all the records permanently. It cannot be rolled back. Truncate is much faster as it does not fires trigger or updates indexes. In truncate, where clause cannot be applied. Be careful with truncate, because if you want certain triggers to fire when records are removed, truncate is not the right choice.

Also truncate cannot be fired on tables, where the table columns are used as a foreign key reference from another table. For example, if user_id of users table is used as a foreign key reference from say audit table, than the users table cannot be truncated. If you try to truncate users table, you might get similar errors as shown below (The following error is from PostgresQL)

ERROR:  cannot truncate a table referenced in a foreign key constraint

DETAIL:  Table "audit" references "users".

HINT:  Truncate table "audit" at the same time, or use TRUNCATE ... CASCADE.

Delete and Truncate deals with only the records of the table. If you want to drop the table completely than drop is used.

To drop table users, use the following SQL

drop table users;

Delete is DML commands whereas Drop and Truncate is a DDL command.