Chapter 13 of 35

ALTER, DROP & TRUNCATE

In the previous chapter, UPDATE and DELETE changed the rows inside a table. But sometimes the table itself has to change. The business now wants to store customers' phone numbers, so a column has to be added. A column name was badly chosen and must be renamed. A table full of old logs must be emptied. A temporary table is no longer needed at all.

For these jobs SQL has three statements: ALTER TABLE (change a table's structure), DROP (remove a table or database completely) and TRUNCATE (empty a table quickly). This chapter covers all three and ends with one of the most asked SQL interview questions: DELETE vs TRUNCATE vs DROP.

All outputs are real MySQL 8.4 output. As in the last chapter, these statements change things, so each section starts from a freshly loaded company_db. Re-run the company_db.sql script (see Setting Up MySQL) whenever you want to reset.


DDL vs DML: Structure vs Data

SQL statements are grouped into families. The two that matter here:

Family

Works on

Statements

DDL (Data Definition Language)

The structure: databases, tables, columns, constraints, indexes

CREATE, ALTER, DROP, TRUNCATE, RENAME

DML (Data Manipulation Language)

The rows stored inside tables

INSERT, UPDATE, DELETE (and SELECT, often grouped here or as DQL)

Notice that TRUNCATE is listed as DDL even though it "only" removes rows. That surprising classification is the key to understanding how it behaves, as we will see.

Important MySQL fact: DDL statements cause an implicit commit. They are saved immediately and cannot be undone with ROLLBACK, and they also commit any transaction that was open before them.


ALTER TABLE: Changing a Table's Structure

ALTER TABLE changes the definition of an existing table (its columns, their types, defaults, constraints, indexes or its name) while keeping the rows already stored in it.

Why not simply drop the table and create it again with the new design? Because the table is full of real data. ALTER TABLE changes the structure and carries the existing rows across.

The general shape is:

ALTER TABLE table_name
  action1,
  action2, ...;

Here is the starting structure of customers:

DESCRIBE customers;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| customer_id | int         | NO   | PRI | NULL    |       |
| name        | varchar(50) | NO   |     | NULL    |       |
| city        | varchar(50) | YES  |     | NULL    |       |
| signup_date | date        | NO   |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

ADD COLUMN

Add a phone number column:

ALTER TABLE customers ADD COLUMN phone VARCHAR(15);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
SELECT * FROM customers;
+-------------+---------------+-----------+-------------+-------+
| customer_id | name          | city      | signup_date | phone |
+-------------+---------------+-----------+-------------+-------+
|         101 | Rahul Verma   | Delhi     | 2023-01-05  | NULL  |
|         102 | Kavya Menon   | Bengaluru | 2023-02-17  | NULL  |
|         103 | Aditya Joshi  | Pune      | 2023-03-22  | NULL  |
|         104 | Neha Agarwal  | Mumbai    | 2023-05-09  | NULL  |
|         105 | Siddharth Rao | NULL      | 2023-08-30  | NULL  |
+-------------+---------------+-----------+-------------+-------+
5 rows in set (0.01 sec)

The new column is added at the end, and every existing row gets NULL in it. That makes sense: the database cannot know the phone numbers of existing customers. (The word COLUMN is optional; ADD phone VARCHAR(15) works too.)

You can choose the position with FIRST or AFTER column, and give a default value that existing rows receive:

ALTER TABLE customers
  ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE AFTER name;
SELECT * FROM customers;
+-------------+---------------+-----------+-----------+-------------+-------+
| customer_id | name          | is_active | city      | signup_date | phone |
+-------------+---------------+-----------+-----------+-------------+-------+
|         101 | Rahul Verma   |         1 | Delhi     | 2023-01-05  | NULL  |
|         102 | Kavya Menon   |         1 | Bengaluru | 2023-02-17  | NULL  |
|         103 | Aditya Joshi  |         1 | Pune      | 2023-03-22  | NULL  |
|         104 | Neha Agarwal  |         1 | Mumbai    | 2023-05-09  | NULL  |
|         105 | Siddharth Rao |         1 | NULL      | 2023-08-30  | NULL  |
+-------------+---------------+-----------+-----------+-------------+-------+
5 rows in set (0.00 sec)

Every existing customer is now active (1, since BOOLEAN is stored as TINYINT(1) in MySQL).

A Surprise: NOT NULL Without a DEFAULT

What if you add a NOT NULL column but give no default? The existing rows cannot be NULL, so what do they get?

ALTER TABLE customers ADD COLUMN loyalty_points INT NOT NULL;
SELECT customer_id, name, loyalty_points FROM customers;
+-------------+---------------+----------------+
| customer_id | name          | loyalty_points |
+-------------+---------------+----------------+
|         101 | Rahul Verma   |              0 |
|         102 | Kavya Menon   |              0 |
|         103 | Aditya Joshi  |              0 |
|         104 | Neha Agarwal  |              0 |
|         105 | Siddharth Rao |              0 |
+-------------+---------------+----------------+
5 rows in set (0.00 sec)

MySQL silently fills in the type's implicit default: 0 for numbers and '' (empty string) for strings. It does not raise an error. (Dates are the exception: the implicit default would be '0000-00-00', which the default strict mode with NO_ZERO_DATE rejects, so ADD COLUMN d DATE NOT NULL on a filled table fails with error 1292.) That is convenient but dangerous: a value of 0 now looks like real data ("this customer has zero points") when it really means "never set". It is better to state the default you mean explicitly. (Some other databases, such as SQL Server, refuse this statement on a non-empty table.)

MODIFY COLUMN: Change Type or Constraints

MODIFY redefines a column while keeping its name. You must write the complete new definition. Anything you leave out (NOT NULL, DEFAULT, comment) is dropped.

Changing a type is only allowed if the existing data fits. Try shrinking name to 5 characters:

ALTER TABLE customers MODIFY COLUMN name VARCHAR(5);
ERROR 1265 (01000): Data truncated for column 'name' at row 1

"Rahul Verma" does not fit in 5 characters, and in MySQL's default strict mode the whole ALTER is refused rather than cutting your data. Making a column NOT NULL fails the same way if any row already holds NULL:

ALTER TABLE customers MODIFY COLUMN city VARCHAR(50) NOT NULL;
ERROR 1138 (22004): Invalid use of NULL value

Siddharth Rao has no city. The fix is to clean the data first (for example UPDATE customers SET city = 'Unknown' WHERE city IS NULL) and then run the ALTER. Widening a column is always safe:

ALTER TABLE customers MODIFY COLUMN name VARCHAR(100) NOT NULL;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

Notice "5 rows affected": MySQL had to rebuild the table and copy the 5 rows into the new format. On a table with millions of rows this can take a long time, which is why schema changes on big production tables are planned carefully.

RENAME COLUMN and CHANGE COLUMN

To rename a column without touching its type (MySQL 8.0 and later):

ALTER TABLE customers RENAME COLUMN phone TO mobile;

CHANGE is the older, MySQL-specific form that renames and redefines in one go, so you must repeat the full definition:

ALTER TABLE customers CHANGE COLUMN mobile mobile_no VARCHAR(20);

Clause

Renames?

Changes definition?

Needs full definition?

RENAME COLUMN old TO new

Yes

No

No

MODIFY col definition

No

Yes

Yes

CHANGE old new definition

Yes

Yes

Yes

Be careful when renaming: every query, view and application that used the old name will break.

Changing Only the Default

ALTER COLUMN ... SET DEFAULT (or DROP DEFAULT) changes the default without redefining the column, and it does not touch existing rows. It only affects future INSERTs that omit the column:

ALTER TABLE customers ALTER COLUMN loyalty_points SET DEFAULT 100;

DROP COLUMN

Remove columns (several actions can go in one statement, separated by commas):

ALTER TABLE customers DROP COLUMN is_active, DROP COLUMN mobile_no;
DESCRIBE customers;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| customer_id    | int          | NO   | PRI | NULL    |       |
| name           | varchar(100) | NO   |     | NULL    |       |
| city           | varchar(50)  | YES  |     | NULL    |       |
| signup_date    | date         | NO   |     | NULL    |       |
| loyalty_points | int          | NO   |     | 100     |       |
+----------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

The data in a dropped column is gone for good. MySQL also protects columns that relationships depend on:

ALTER TABLE employees DROP COLUMN dept_id;
ERROR 1828 (HY000): Cannot drop column 'dept_id': needed in a foreign key constraint 'employees_ibfk_1'

Similarly, you cannot change departments.dept_id to BIGINT while employees.dept_id (an INT) references it:

ERROR 3780 (HY000): Referencing column 'dept_id' and referenced column 'dept_id' in foreign key constraint 'employees_ibfk_1' are incompatible.

You would first drop the foreign key, change both columns, then add the key back. Adding and dropping constraints with ALTER TABLE (ADD CONSTRAINT, DROP FOREIGN KEY, DROP CHECK ...) is shown in the next chapter, SQL Constraints.

Renaming a Table

Two equivalent ways:

ALTER TABLE customers RENAME TO clients;
RENAME TABLE clients TO customers;      -- and back again

RENAME TABLE can rename several tables in one atomic statement, which is handy for swapping a freshly built table into place: RENAME TABLE orders TO orders_old, orders_new TO orders;. Foreign keys in other tables automatically follow the renamed table.

Big Tables and ALGORITHM=INSTANT

Some ALTERs only change the table's metadata and finish instantly even on huge tables; others must rebuild every row. In MySQL 8.0.29 and later, adding a column (at any position) and dropping a column can usually be done instantly. You can ask for that explicitly, and MySQL will refuse rather than silently do a slow rebuild:

ALTER TABLE customers ADD COLUMN notes TEXT, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
ALTER TABLE customers MODIFY COLUMN name VARCHAR(100) NOT NULL, ALGORITHM=INSTANT;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.

As a beginner you do not need to tune this, but it explains why "just add a column" can be quick while "change a column's type" on a large table may need a maintenance window.

ALTER TABLE Cheat Sheet

Task

Syntax (MySQL)

Add a column

ADD [COLUMN] col type [FIRST | AFTER other]

Remove a column

DROP [COLUMN] col

Change type / nullability

MODIFY [COLUMN] col new_definition

Rename a column

RENAME COLUMN old TO new

Rename and redefine

CHANGE [COLUMN] old new definition

Set / remove a default

ALTER [COLUMN] col SET DEFAULT v / DROP DEFAULT

Rename the table

RENAME TO new_name

Add / drop constraints

ADD CONSTRAINT ..., DROP PRIMARY KEY, DROP FOREIGN KEY name (see SQL Constraints)

Other databases use slightly different keywords (for example PostgreSQL uses ALTER COLUMN col TYPE ... instead of MODIFY), but the ideas are the same.


DROP: Removing a Table or Database

DROP TABLE permanently removes a table: its structure, all its rows, its indexes, constraints and triggers. DROP DATABASE removes a whole database with every table inside it.

CREATE TABLE login_logs (
  log_id    INT AUTO_INCREMENT PRIMARY KEY,
  emp_id    INT,
  login_at  DATETIME
);

DROP TABLE login_logs;
SELECT * FROM login_logs;
ERROR 1146 (42S02): Table 'company_db.login_logs' doesn't exist

The table no longer exists at all, so even a SELECT fails. Dropping it again is an error:

DROP TABLE login_logs;
ERROR 1051 (42S02): Unknown table 'company_db.login_logs'

In scripts that may run more than once (like our company_db.sql, which starts with DROP DATABASE IF EXISTS), add IF EXISTS. MySQL then just records a note instead of failing:

DROP TABLE IF EXISTS login_logs;
Query OK, 0 rows affected, 1 warning (0.01 sec)

Foreign Keys Protect Parent Tables

DROP TABLE departments;
ERROR 3730 (HY000): Cannot drop table 'departments' referenced by a foreign key constraint 'employees_ibfk_1' on table 'employees'.

If departments vanished, every employees.dept_id would point to nothing. Drop children before parents (the reverse of the creation order you saw in CREATE DATABASE & CREATE TABLE), or drop them together in one statement:

DROP TABLE employees, departments;
Query OK, 0 rows affected (0.03 sec)

DROP DATABASE

DROP DATABASE company_db;

Everything in it is gone: all tables, views, procedures. There is no "Are you sure?" and no recycle bin. On real servers, the only way back from an accidental DROP is a backup. Many teams do not even give everyday application accounts the DROP privilege.


TRUNCATE: Emptying a Table Fast

TRUNCATE TABLE removes all rows from a table but keeps the table's structure. Internally MySQL drops and recreates the table, which is why it is fast and why it is classified as DDL.

TRUNCATE TABLE table_name;

There is no WHERE clause. It is all or nothing:

TRUNCATE TABLE login_logs WHERE emp_id = 5;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE emp_id = 5' at line 1

Difference 1: AUTO_INCREMENT Is Reset

Let's compare DELETE and TRUNCATE. Create the login_logs table again (same CREATE TABLE as in the DROP section) and insert three rows:

INSERT INTO login_logs (emp_id, login_at) VALUES
  (1, '2024-05-01 09:00:00'), (2, '2024-05-01 09:05:00'), (3, '2024-05-01 09:12:00');
SELECT * FROM login_logs;
+--------+--------+---------------------+
| log_id | emp_id | login_at            |
+--------+--------+---------------------+
|      1 |      1 | 2024-05-01 09:00:00 |
|      2 |      2 | 2024-05-01 09:05:00 |
|      3 |      3 | 2024-05-01 09:12:00 |
+--------+--------+---------------------+
3 rows in set (0.00 sec)

Empty it with DELETE, then insert a new row:

DELETE FROM login_logs;
INSERT INTO login_logs (emp_id, login_at) VALUES (4, '2024-05-02 10:00:00');
SELECT * FROM login_logs;
+--------+--------+---------------------+
| log_id | emp_id | login_at            |
+--------+--------+---------------------+
|      4 |      4 | 2024-05-02 10:00:00 |
+--------+--------+---------------------+
1 row in set (0.00 sec)

The counter continued from 4. DELETE removes rows but the table "remembers" where its AUTO_INCREMENT counter was. Now TRUNCATE and insert again:

TRUNCATE TABLE login_logs;
INSERT INTO login_logs (emp_id, login_at) VALUES (5, '2024-05-03 11:00:00');
SELECT * FROM login_logs;
+--------+--------+---------------------+
| log_id | emp_id | login_at            |
+--------+--------+---------------------+
|      1 |      5 | 2024-05-03 11:00:00 |
+--------+--------+---------------------+
1 row in set (0.00 sec)

Back to 1. The table was recreated from scratch, counter included.

Difference 2: TRUNCATE Cannot Be Rolled Back

A DELETE inside a transaction can be undone:

START TRANSACTION;
DELETE FROM login_logs;
ROLLBACK;
SELECT COUNT(*) FROM login_logs;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

TRUNCATE cannot, because as DDL it commits immediately:

START TRANSACTION;
TRUNCATE TABLE login_logs;
ROLLBACK;
SELECT COUNT(*) FROM login_logs;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

The ROLLBACK did nothing; the row is gone. (Careful in interviews: this is MySQL and Oracle behaviour. In PostgreSQL and SQL Server, TRUNCATE inside a transaction can be rolled back.)

Hidden Danger: DDL Commits Your Open Transaction

The implicit commit does not just affect the DDL statement itself. It also commits whatever you did before it in the same transaction:

START TRANSACTION;
DELETE FROM orders WHERE status = 'Cancelled';
ALTER TABLE customers ADD COLUMN phone VARCHAR(15);   -- DDL: implicit COMMIT here
ROLLBACK;

SELECT order_id, status FROM orders WHERE status = 'Cancelled';
Empty set (0.00 sec)

The cancelled order was meant to be protected by the transaction, but the ALTER committed the DELETE, so the ROLLBACK had nothing left to undo. Never mix DDL into a transaction you might want to roll back.

Difference 3: Foreign Keys Block TRUNCATE

orders is a child table (nothing references it), so it can be truncated:

TRUNCATE TABLE orders;
Query OK, 0 rows affected (0.05 sec)

Notice "0 rows affected" even though 8 rows were removed. TRUNCATE does not count rows, because it does not delete them one by one. But a parent table cannot be truncated:

TRUNCATE TABLE customers;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`company_db`.`orders`, CONSTRAINT `orders_ibfk_1`)

This fails even though orders is now empty. TRUNCATE does not check row by row whether anything still points to a customer; it simply refuses for any table referenced by a foreign key. DELETE FROM customers would work here, because DELETE checks each row and finds no orders referencing them.

Why Is TRUNCATE Faster?

DELETE works row by row: for each row it checks constraints, writes undo information (so it can be rolled back), updates every index and fires any DELETE triggers. For a million rows, that is a million times. TRUNCATE skips all of this: it throws away the table's storage and creates a new empty one. The cost is roughly the same whether the table has 10 rows or 10 million. The price you pay for this speed is everything in the comparison below: no WHERE, no rollback, no triggers, no row count, and no truncating of parent tables.


DELETE vs TRUNCATE vs DROP

This comparison is asked in almost every SQL or DBMS interview. Here it is for MySQL with InnoDB:

Point

DELETE

TRUNCATE

DROP

Type

DML

DDL

DDL

What is removed

Some or all rows

All rows

Rows and the table itself

WHERE clause

Yes

No

No

Table structure afterwards

Kept

Kept

Gone

AUTO_INCREMENT counter

Continues

Reset to start

Gone with the table

Rollback (MySQL)

Yes, inside a transaction

No (implicit commit)

No (implicit commit)

DELETE triggers fire

Yes, per row

No

No (triggers are dropped too)

Speed on big tables

Slow (row by row)

Fast

Fast

Rows affected reported

Actual count

0

0

Table referenced by a foreign key

Allowed if no child rows point to the deleted rows

Refused (error 1701)

Refused (error 3730)

Typical use

Remove specific rows

Reset a staging/log table

Remove a table you no longer need

A simple way to remember it: DELETE removes the furniture you choose, TRUNCATE empties the whole room in one go, DROP demolishes the room.


Common Mistakes

Mistake

What happens

Fix

Leaving out NOT NULL / DEFAULT in MODIFY

Column silently becomes nullable, default lost

Always write the complete definition (check with SHOW CREATE TABLE)

Adding NOT NULL without DEFAULT to a filled table

Existing rows get 0 / '' and look like real data

Give an explicit default

Making a column NOT NULL while NULLs exist

Error 1138

UPDATE the NULLs first

Shrinking a column below existing data

Error 1265 (strict mode)

Check MAX(CHAR_LENGTH(col)) first

Expecting ROLLBACK to undo TRUNCATE or ALTER

Changes are already committed

Take a backup; keep DDL out of transactions

Using TRUNCATE to "delete some rows"

Syntax error: no WHERE allowed

Use DELETE ... WHERE

Dropping a parent table first

Error 3730

Drop children first, or both in one statement

Renaming a column that code depends on

Application queries break

Search the codebase first; migrate in steps


Interview Questions

What is the difference between DELETE, TRUNCATE and DROP?

DELETE is DML, removes chosen rows (WHERE), fires triggers and can be rolled back. TRUNCATE is DDL, removes all rows quickly, resets AUTO_INCREMENT and (in MySQL) cannot be rolled back. DROP removes the table's data and its structure entirely.

Why is TRUNCATE classified as DDL?

Because it does not delete rows one at a time. It drops and recreates the table's storage, i.e. it works on the table definition, and it causes an implicit commit like other DDL.

Can TRUNCATE be rolled back?

Not in MySQL or Oracle (it commits implicitly). In PostgreSQL and SQL Server it can, if run inside a transaction.

Does TRUNCATE reset AUTO_INCREMENT? Does DELETE?

TRUNCATE resets it to the starting value. DELETE does not; new rows continue from the old counter.

Why can't I TRUNCATE a table even though its child table is empty?

MySQL refuses TRUNCATE on any table referenced by a foreign key (error 1701), without checking actual rows. Use DELETE, or drop/disable the foreign key.

How do you add a column in a specific position in MySQL?

ALTER TABLE t ADD COLUMN c type AFTER other_col; or FIRST to put it at the beginning.

Difference between MODIFY and CHANGE in MySQL?

MODIFY changes a column's definition but not its name. CHANGE can rename and redefine at the same time and needs both old and new names plus the full definition. To only rename, use RENAME COLUMN.

What happens to existing rows when you add a column?

They get the column's default: the explicit DEFAULT if given, NULL if the column is nullable, otherwise MySQL's implicit default for the type (0 for numbers, '' for strings; a NOT NULL DATE column with no default is rejected in strict mode).

What is an implicit commit?

Statements like CREATE, ALTER, DROP and TRUNCATE automatically commit the current transaction before (and after) running, so earlier uncommitted changes become permanent and cannot be rolled back.


Summary

  • DDL changes structure (CREATE, ALTER, DROP, TRUNCATE); DML changes rows (INSERT, UPDATE, DELETE). In MySQL, DDL causes an implicit commit.

  • ALTER TABLE adds (ADD), removes (DROP), redefines (MODIFY), renames (RENAME COLUMN, CHANGE) columns and renames tables, keeping existing data.

  • ALTERs that conflict with existing data (too long, NULLs, foreign keys) are refused; clean the data first.

  • DROP TABLE / DROP DATABASE remove objects completely; use IF EXISTS in scripts, and drop children before parents.

  • TRUNCATE TABLE empties a table fast, resets AUTO_INCREMENT, has no WHERE, fires no triggers, cannot be rolled back in MySQL and is refused on tables referenced by foreign keys.

  • DELETE = remove chosen rows; TRUNCATE = remove all rows; DROP = remove the table.

We have run into foreign keys, NOT NULL and UNIQUE again and again. In the next chapter, SQL Constraints, we will study every constraint properly: what it guarantees, how to add and remove it, and how cascade rules work.