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
Khup students "TRUNCATE mhanje DELETE cha fast version" evdhach sangtat. Pudhe jaa: TRUNCATE DDL aahe, rollback hot nahi, AUTO_INCREMENT reset hoto, triggers chalat nahit. He char muddhe sangitle ki interviewer khush. Aani DROP nantar undo nahi – backup hach upay.
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.