11. MySQL Part 2: UPDATE, ALTER, DELETE, Keys, Constraints and Users
11.4 DELETE, TRUNCATE and DROP
Teen commands – teen vegle parinam. Interview madhe "DELETE vs TRUNCATE vs DROP" ha prashna jawal jawal nakki yeto.
DELETE FROM enrollments WHERE score < 50; -- remove selected rows
DELETE FROM enrollments; -- remove all rows (structure stays)
TRUNCATE TABLE enrollments; -- remove all rows fast, reset AUTO_INCREMENT
DROP TABLE enrollments; -- remove the table itself
DROP TABLE IF EXISTS enrollments; -- no error if missing
| Point | DELETE | TRUNCATE | DROP |
|---|---|---|---|
| Type | DML | DDL | DDL |
| What goes | Selected rows (or all) | All rows | Rows and table structure |
| WHERE allowed | Yes | No | No |
| Rollback (InnoDB) | Yes, inside a transaction | No (implicit commit) | No (implicit commit) |
| AUTO_INCREMENT | Continues from last value | Reset to 1 | Table is gone |
| Triggers fire | Yes (DELETE triggers) | No | No |
| Speed on large tables | Slower (row by row, logged) | Fast | Fast |
| Table referenced by a foreign key | Allowed if no child rows break | Fails | Fails (drop child first) |
Why this matters for security
An application database user should almost never have DROP or TRUNCATE rights. If an attacker achieves SQL injection through a user that only has SELECT, INSERT, UPDATE, DELETE on one database, they cannot drop your tables. Least privilege limits the blast radius.
Ravindra Bagale's Tip
Many students only say "TRUNCATE is a faster version of DELETE". Go further: TRUNCATE is DDL, it can't be rolled back, AUTO_INCREMENT is reset, and triggers don't run. Mention these four points and the interviewer will be happy. And there's no undo after DROP – a backup is the only way back.
Ravindra Bagale's Tip – मराठी
बरेच students "TRUNCATE म्हणजे DELETE चं fast version" एवढंच सांगतात. पुढे जा: TRUNCATE DDL आहे, rollback होत नाही, AUTO_INCREMENT reset होतो, triggers चालत नाहीत. हे चार मुद्दे सांगितले की interviewer खूश. आणि DROP नंतर undo नाही – backup हाच उपाय.
Ravindra Bagale's Tip – हिंदी
बहुत से students बस इतना कहते हैं "TRUNCATE यानी DELETE का fast version". आगे बढ़ो: TRUNCATE DDL है, rollback नहीं होता, AUTO_INCREMENT reset होता है, triggers नहीं चलते. ये चार बातें बताईं तो interviewer ख़ुश. और DROP के बाद undo नहीं है – backup ही उपाय है.
Practice task
Create a copy table with CREATE TABLE enroll_copy AS SELECT * FROM enrollments;. Try DELETE with ROLLBACK, then TRUNCATE, insert a row and note its id, then DROP the copy.