Creating Tables and Changing Data · Lesson 24 of 30

SQL ALTER TABLE, TRUNCATE and DROP

5 min read Updated Nov 7, 2026 Saurabh Samir

On this page

Adding and changing columns
Constraints and indexes
Dropping columns and renaming tables
DELETE vs TRUNCATE vs DROP
Practice
Summary

Requirements change: the business wants customers' phone numbers, a column needs a bigger size, a constraint must be added. ALTER TABLE changes an existing table's structure without losing its data. This lesson also clears up one of the most common interview questions: the difference between DELETE, TRUNCATE and DROP.

In this lesson

Adding, modifying, renaming and dropping columns
Adding and dropping constraints and indexes
Renaming tables
DELETE vs TRUNCATE vs DROP

Adding and changing columns

ALTER TABLE customers
  ADD COLUMN phone VARCHAR(15) AFTER email,                    -- new optional column
  ADD COLUMN loyalty_points INT NOT NULL DEFAULT 0;            -- existing rows get 0

ALTER TABLE customers MODIFY COLUMN city VARCHAR(60);          -- change type/size
ALTER TABLE customers RENAME COLUMN signup_date TO joined_on;  -- MySQL 8+

UPDATE customers SET phone = '9876543210', loyalty_points = 120 WHERE customer_id = 1;

SELECT customer_id, name, phone, loyalty_points, joined_on
FROM customers
WHERE customer_id <= 3;

Output

+-------------+------------+------------+----------------+------------+
| customer_id | name       | phone      | loyalty_points | joined_on  |
+-------------+------------+------------+----------------+------------+
|           1 | Asha Verma | 9876543210 |            120 | 2025-01-15 |
|           2 | Ravi Kumar | NULL       |              0 | 2025-02-03 |
|           3 | Meera Nair | NULL       |              0 | 2025-02-20 |
+-------------+------------+------------+----------------+------------+

SELECT column_name AS col, data_type AS type, is_nullable AS nullable, column_default AS dflt
FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = 'customers'
ORDER BY ordinal_position;

Output (after the ALTERs above)

+----------------+---------+----------+------+
| col            | type    | nullable | dflt |
+----------------+---------+----------+------+
| customer_id    | int     | NO       | NULL |
| name           | varchar | NO       | NULL |
| email          | varchar | NO       | NULL |
| phone          | varchar | YES      | NULL |
| city           | varchar | YES      | NULL |
| joined_on      | date    | NO       | NULL |
| loyalty_points | int     | NO       | 0    |
+----------------+---------+----------+------+

information_schema is a built-in set of views describing every database object – handy for documentation and scripts. (DESCRIBE customers shows similar information.)

Constraints and indexes

ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock >= 0);
ALTER TABLE products ADD CONSTRAINT uq_product_name UNIQUE (name);
CREATE INDEX idx_orders_date ON orders (order_date);         -- speeds up date filters (chapter 9)

ALTER TABLE products DROP CHECK chk_stock;                   -- MySQL 8.0.19+ (or DROP CONSTRAINT)
DROP INDEX idx_orders_date ON orders;

On a large production table, an ALTER can take minutes and lock writes. Teams schedule such changes, test them on a copy first, and use online schema-change tools for very big tables.

Dropping columns and renaming tables

ALTER TABLE customers ADD COLUMN temp_notes TEXT;
ALTER TABLE customers DROP COLUMN temp_notes;                -- the data in it is gone for good

RENAME TABLE categories TO product_categories;
SELECT COUNT(*) AS categories FROM product_categories;
RENAME TABLE product_categories TO categories;               -- foreign keys follow the rename

Output

+------------+
| categories |
+------------+
|          4 |
+------------+

DELETE vs TRUNCATE vs DROP

CREATE TABLE log_demo (id INT AUTO_INCREMENT PRIMARY KEY, msg VARCHAR(20));
INSERT INTO log_demo (msg) VALUES ('a'), ('b'), ('c');

DELETE FROM log_demo;                         -- removes rows; AUTO_INCREMENT continues
INSERT INTO log_demo (msg) VALUES ('after delete');
SELECT * FROM log_demo;

TRUNCATE TABLE log_demo;                      -- empties the table and resets AUTO_INCREMENT
INSERT INTO log_demo (msg) VALUES ('after truncate');
SELECT * FROM log_demo;

DROP TABLE log_demo;                          -- removes the table itself
SELECT COUNT(*) AS still_exists FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name = 'log_demo';

Output

+----+--------------+
| id | msg          |
+----+--------------+
|  4 | after delete |
+----+--------------+
+----+----------------+
| id | msg            |
+----+----------------+
|  1 | after truncate |
+----+----------------+
+--------------+
| still_exists |
+--------------+
|            0 |
+--------------+

	DELETE	TRUNCATE	DROP
Removes	chosen rows (WHERE)	all rows	the whole table: rows, structure, indexes
Command type	DML	DDL	DDL
WHERE allowed	yes	no	no
Speed on big tables	slow (row by row, logged)	fast (recreates the table)	fast
AUTO_INCREMENT	keeps counting	reset	gone
Rollback in a transaction	yes	no in MySQL (implicit commit)	no in MySQL
Triggers fire	yes	no	no

Be careful with DROP and TRUNCATE

In MySQL, DDL statements commit immediately and can't be rolled back. DROP TABLE IF EXISTS in a script is fine for setup; against production data, always double-check the database you're connected to and have a backup.

Practice

Try it yourself

Add a discount_pct column to products (default 0), give Books a 10% discount, and show each book's discounted price.

Show solution

ALTER TABLE products ADD COLUMN discount_pct TINYINT NOT NULL DEFAULT 0;
UPDATE products SET discount_pct = 10 WHERE category_id = 2;
SELECT name, price, discount_pct, ROUND(price * (100 - discount_pct) / 100, 2) AS sale_price
FROM products
WHERE category_id = 2;

Output

+-------------------+--------+--------------+------------+
| name              | price  | discount_pct | sale_price |
+-------------------+--------+--------------+------------+
| Clean Code        | 450.00 |           10 |     405.00 |
| SQL in 10 Minutes | 350.00 |           10 |     315.00 |
| JavaScript Guide  | 550.00 |           10 |     495.00 |
+-------------------+--------+--------------+------------+

Quick check

Which statement resets AUTO_INCREMENT?

TRUNCATE TABLE (DELETE doesn't).

Can you roll back a TRUNCATE in MySQL?

No – it's DDL and commits implicitly.

How do you rename a column in MySQL 8?

ALTER TABLE t RENAME COLUMN old_name TO new_name;

Summary

ALTER TABLE adds, modifies, renames and drops columns and constraints while keeping data.
DELETE removes chosen rows (DML, can roll back); TRUNCATE empties fast and resets AUTO_INCREMENT; DROP removes the table.
Test structural changes on a copy and back up before changing production.

Interview practice: views, procedures, triggers and SQL security.

Finished this lesson?

Mark it complete to track your progress, then carry on with the next lesson.

Next lesson