Keys, UPDATE, ALTER, DELETE, DROP, TRUNCATE and safe updates

Key terminology

An index is a retrieval structure; not every index is a unique key. A UNIQUE index in MySQL permits multiple NULLs; use NOT NULL when your business identifier must always exist. Foreign-key actions such as RESTRICT, CASCADE and SET NULL must reflect the lifecycle you actually want.

Safe mutation pattern

USE academy;
SET SESSION SQL_SAFE_UPDATES = 1;
SELECT * FROM students WHERE student_id=2;
START TRANSACTION;
UPDATE students SET city='Pune' WHERE student_id=2;
SELECT ROW_COUNT();
SELECT * FROM students WHERE student_id=2;
ROLLBACK;

After ROLLBACK Rahul should retain Nashik. Repeat with COMMIT only when the intended change is verified. Safe-update mode rejects certain UPDATE/DELETE statements without an appropriate key restriction or LIMIT; it is not a substitute for reviewing a WHERE clause, and optimizer choices can matter.

Schema changes

ALTER TABLE students ADD COLUMN phone VARCHAR(20) NULL;
ALTER TABLE students MODIFY COLUMN city VARCHAR(100);
CREATE INDEX idx_students_city ON students(city);
SHOW INDEX FROM students;
ALTER TABLE students DROP COLUMN phone;

Large ALTERs can lock, copy or rebuild data depending on version and operation. Test migration behaviour with realistic data and have a recovery plan. MySQL DDL often implicitly commits; wrapping every schema command in START TRANSACTION does not make it reversible.

Compare deletion operations on a scratch table

CREATE TABLE scratch_students LIKE students;
INSERT INTO scratch_students SELECT * FROM students;
DELETE FROM scratch_students WHERE student_id=3;
SELECT COUNT(*) FROM scratch_students;
TRUNCATE TABLE scratch_students;
SELECT COUNT(*) FROM scratch_students;
DROP TABLE scratch_students;

DELETE removes matching rows and can be rolled back in a still-open InnoDB transaction. TRUNCATE removes all rows, resets auto-increment, implicitly commits and is subject to foreign-key restrictions. DROP removes the table definition and data. Do not demonstrate these against the only copy of useful data.

Controlled safe-mode exception

Prefer key-based filters. If an authorized lab genuinely requires temporarily disabling safe mode, store @@SESSION.sql_safe_updates, disable for that session, preview matching rows, perform the intended statement in an appropriate transaction, then restore the previous value. Do not permanently disable it globally to silence Workbench errors.

Official references

MySQL foreign keys MySQL TRUNCATE SQL safe updates

Interview and revision check

Can you undo TRUNCATE with ROLLBACK in MySQL?

No. TRUNCATE is DDL with an implicit commit. Use only disposable data or a deliberate recovery strategy.