DROP and TRUNCATE

It's worth mentioning two more operations in DDL: DROP and TRUNCATE.

Let's briefly consider what each of these operations does:

DROP: Used to delete database objects such as tables, databases, and indexes.

TRUNCATE: Removes all rows from a table but preserves the table's structure.

I've used these operations to clear or delete tables to check tasks in previous chapters. Their syntax is quite simple; let's look at them:

This code will delete the employees table from the database. In many DBMSs, this operation requires certain permissions, and if you are working on a project, you won't have access to such an operation. You'll learn about roles and how to set them up in the next course, which covers Advanced SQL Concepts.

This code will delete all rows from the employees table, completely clearing it and making it empty. This operation won't affect the table's structure, meaning it won't affect columns or constraints. You also need permissions in the DBMS for such an operation because not everyone should have the ability to clear a table.

Use these operations carefully because if you don't have database snapshots, you won't be able to roll back table deletions or row truncations.

Note

Often, developers use soft deletion by introducing a new column is_deleted with a BOOLEAN data type, and when some rows are deleted, the status is set to true (or 1). This way, you can see the deleted data and not worry about its integrity.