Skillversitas

DROP & TRUNCATE

⚠️ WARNING

DROP and TRUNCATE are destructive operations. Deleted data cannot be recovered without a backup. Use them with care!

DROP TABLE - Dropping a Table

DROP TABLE removes an entire table, including its structure and data.

DROP TABLE OLD_EMPLOYEES;

DROP TABLE IF EXISTS OLD_EMPLOYEES;

TRUNCATE TABLE - Removing All Data

TRUNCATE TABLE removes all data but preserves the table structure.

TRUNCATE TABLE EMPLOYEE;

Comparing DELETE, TRUNCATE, and DROP

Feature DELETE TRUNCATE DROP
Deletes Data Yes Yes Yes
Drops Structure No No Yes
WHERE Supported Not supported Not supported
Rollback Yes (within a transaction) No (usually) No
Auto Increment Not reset Reset N/A
Speed Slow (row-by-row) Fast (bulk) Fast
Trigger Fires Does not fire N/A

DELETE - Removing Specific Data

DELETE FROM EMPLOYEE WHERE status = 'Deceased';

DELETE FROM EMPLOYEE;

When to Use What?

  • DELETE - Remove specific data with a WHERE condition
  • TRUNCATE - Empty a table, keep the structure
  • DROP - Remove a table entirely

Safe Example

-- Back up first
CREATE TABLE EMPLOYEE_BACKUP AS
SELECT * FROM EMPLOYEE;

-- Then truncate
TRUNCATE TABLE EMPLOYEE;

Safety Tips

  • Always use WHERE with DELETE
  • Run a SELECT first to verify the data that will be deleted
  • Use transactions for bulk operations

After understanding deletion operations, let's learn how to save queries as Views in the next chapter.