DROP and TRUNCATE

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

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.

We've used these operations to clear or delete tables to check tasks in previous chapters:

The DROP TABLE command will completely remove the employees table from the database. Keep in mind that this action usually requires special permissions in many database management systems (DBMS).

If you're working on a project, you might not have the necessary access rights. In the next course on Advanced Techniques in SQL, you'll learn about roles and how to manage them.

The TRUNCATE TABLE command removes all data from the employees table, leaving it empty. However, it keeps the table's structure intact, so columns and constraints remain unchanged. You will need the right permissions to perform this action in a DBMS.

Be cautious when using these commands. Without database backups, you can't undo a table deletion or data removal.

Note

Developers often use soft deletion by adding a column like is_deleted with a BOOLEAN type. When a row is 'deleted', this column is set to true (or 1). This way, you can track deleted data without losing it.