SQL Tutorial › Database Design › ALTER TABLE

SQL ALTER TABLE

Q: What is the difference between MODIFY and CHANGE in MySQL?

MODIFY redefines a column but keeps its name. CHANGE can rename it and redefine it at the same time.

Q: How do I rename a table in MySQL?

ALTER TABLE old_name RENAME TO new_name; or RENAME TABLE old_name TO new_name;

IntermediateLesson 57 of 6911 min read8 queries run on MySQL 8.4

Requirements change after a table is created: a new column for phone numbers, a longer name field, a renamed column, a new index. ALTER TABLE changes the structure of an existing table without losing its data. This lesson runs each kind of change on a copy of the sample customers table and shows the result with DESCRIBE.

In this lesson

Add, modify, rename and drop columns
Rename a table
Add and drop keys, indexes and constraints
Change large tables safely

Sample table

Every example works on a copy, so the original stays intact:

MySQL

CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;DESCRIBE clients;

ResultMySQL 8.4

mysql> CREATE TABLE clients AS SELECT customer_id, customer_name, email, city…

Query OK, 7 rows affected

mysql> DESCRIBE clients

Field	Type	Null	Default
customer_id	int	NO	NULL
customer_name	varchar(50)	NO	NULL
email	varchar(60)	YES	NULL
city	varchar(30)	YES	NULL

4 rows in set

ADD COLUMN

MySQL

CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients  ADD COLUMN phone VARCHAR(15),  ADD COLUMN loyalty_points INT NOT NULL DEFAULT 0 AFTER customer_name;SELECT customer_id, customer_name, loyalty_points, phone FROM clients LIMIT 3;

ResultMySQL 8.4

mysql> CREATE TABLE clients AS SELECT customer_id, customer_name, email, city…

Query OK, 7 rows affected

mysql> ALTER TABLE clients

Query OK, 0 rows affected

mysql> SELECT customer_id, customer_name, loyalty_points, phone FROM clients …

customer_id	customer_name	phone
1	Ananya Sharma	NULL
2	Rohit Kumar	NULL
3	Fatima Khan	NULL

3 rows in set

How this query works, step by step

One ALTER TABLE can make several changes, separated by commas.
phone is added at the end and is NULL for existing rows.
loyalty_points is NOT NULL DEFAULT 0, so every existing row gets 0; AFTER customer_name places it right after that column (FIRST puts it first).

MODIFY COLUMN: change type or constraints

MySQL

CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients MODIFY customer_name VARCHAR(100) NOT NULL;DESCRIBE clients;

ResultMySQL 8.4

mysql> CREATE TABLE clients AS SELECT customer_id, customer_name, email, city…

Query OK, 7 rows affected

mysql> ALTER TABLE clients MODIFY customer_name VARCHAR(100) NOT NULL

Query OK, 7 rows affected

mysql> DESCRIBE clients

Field	Type	Null	Default
customer_id	int	NO	NULL
customer_name	varchar(100)	NO	NULL
email	varchar(60)	YES	NULL
city	varchar(30)	YES	NULL

4 rows in set

Watch out

MODIFY replaces the whole column definition. If the column had NOT NULL or a DEFAULT and you leave it out, it is gone. Always restate everything you want to keep.

Shrinking a column fails when existing data would not fit (in strict mode):

MySQL

CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients MODIFY customer_name VARCHAR(10);

ResultMySQL 8.4

mysql> CREATE TABLE clients AS SELECT customer_id, customer_name, email, city…

Query OK, 7 rows affected

mysql> ALTER TABLE clients MODIFY customer_name VARCHAR(10)

ERROR 1265 (01000): Data truncated for column 'customer_name' at row 1

RENAME COLUMN and CHANGE

MySQL

CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients RENAME COLUMN customer_name TO full_name;ALTER TABLE clients CHANGE city home_city VARCHAR(40);SELECT customer_id, full_name, home_city FROM clients LIMIT 2;

ResultMySQL 8.4

mysql> CREATE TABLE clients AS SELECT customer_id, customer_name, email, city…

Query OK, 7 rows affected

mysql> ALTER TABLE clients RENAME COLUMN customer_name TO full_name

Query OK, 0 rows affected

mysql> ALTER TABLE clients CHANGE city home_city VARCHAR(40)

Query OK, 0 rows affected

mysql> SELECT customer_id, full_name, home_city FROM clients LIMIT 2

customer_id	full_name	home_city
1	Ananya Sharma	Mumbai
2	Rohit Kumar	Delhi

2 rows in set

How this query works, step by step

RENAME COLUMN old TO new only renames (MySQL 8.0+).
CHANGE old new definition renames and redefines the column in one step (here VARCHAR(40)).

DROP COLUMN

MySQL

CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients DROP COLUMN email;SELECT * FROM clients LIMIT 2;

ResultMySQL 8.4

mysql> CREATE TABLE clients AS SELECT customer_id, customer_name, email, city…

Query OK, 7 rows affected

mysql> ALTER TABLE clients DROP COLUMN email

Query OK, 0 rows affected

mysql> SELECT * FROM clients LIMIT 2

customer_id	customer_name	city
1	Ananya Sharma	Mumbai
2	Rohit Kumar	Delhi

2 rows in set

The column and all its data are deleted permanently.

Rename a table

MySQL

CREATE TABLE clients AS SELECT customer_id, customer_name FROM customers;ALTER TABLE clients RENAME TO client_list;SHOW TABLES LIKE 'client%';

ResultMySQL 8.4

mysql> CREATE TABLE clients AS SELECT customer_id, customer_name FROM customers

Query OK, 7 rows affected

mysql> ALTER TABLE clients RENAME TO client_list

Query OK, 0 rows affected

mysql> SHOW TABLES LIKE 'client%'

Tables_in_company (client%)
client_list

1 row in set

RENAME TABLE a TO b; does the same and can rename several tables at once.

Keys, indexes and constraints

MySQL

CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients  ADD PRIMARY KEY (customer_id),  ADD UNIQUE KEY uq_client_email (email),  ADD INDEX idx_client_city (city),  ADD CONSTRAINT chk_name CHECK (customer_name <> '');SHOW INDEX FROM clients;

ResultMySQL 8.4

mysql> CREATE TABLE clients AS SELECT customer_id, customer_name, email, city…

Query OK, 7 rows affected

mysql> ALTER TABLE clients

Query OK, 7 rows affected

mysql> SHOW INDEX FROM clients

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Visible	Expression
clients	0	PRIMARY	1	customer_id	A	7	NULL	NULL		BTREE	YES	NULL
clients	0	uq_client_email	1	email	A	7	NULL	NULL	YES	BTREE	YES	NULL
clients	1	idx_client_city	1	city	A	7	NULL	NULL	YES	BTREE	YES	NULL

3 rows in set

How this query works, step by step

CREATE TABLE ... AS SELECT copied data but no keys, so they are added now.
One statement adds a primary key, a unique key, a normal index and a check.
SHOW INDEX lists the three indexes; the check constraint is not an index.

Remove them with DROP PRIMARY KEY, DROP INDEX name, DROP CHECK name and DROP FOREIGN KEY name.

Changing big tables safely

Many changes in MySQL 8 are instant or in-place (adding a column at the end, renaming, adding an index) and do not block reads and writes for long.
Others (changing a column’s type) rebuild the table, copying every row. On a large table that can take a long time.
Ask for the cheap algorithm and let MySQL refuse if it cannot: ALTER TABLE big ADD COLUMN note VARCHAR(50), ALGORITHM=INSTANT;
Test schema changes on a copy, take a backup first, and run them in a quiet period.
ALTER TABLE is DDL: it commits any open transaction and cannot be rolled back.

Exercise: Extend products

On a copy of products called catalog, add a discount_pct INT NOT NULL DEFAULT 0 column after price, rename stock to units_in_stock, and show the structure.

Expected resultMySQL 8.4

mysql> CREATE TABLE catalog AS SELECT * FROM products

Query OK, 9 rows affected

mysql> ALTER TABLE catalog

Query OK, 0 rows affected

mysql> DESCRIBE catalog

Field	Type	Null	Default
product_id	int	NO	NULL
product_name	varchar(50)	NO	NULL
category	varchar(30)	YES	NULL
price	int	YES	NULL
discount_pct	int	NO	0
units_in_stock	int	YES	NULL

6 rows in set

Hint 1

Create the copy with CREATE TABLE catalog AS SELECT * FROM products;, then one ALTER TABLE with two changes.

MySQL

CREATE TABLE catalog AS SELECT * FROM products;ALTER TABLE catalog  ADD COLUMN discount_pct INT NOT NULL DEFAULT 0 AFTER price,  RENAME COLUMN stock TO units_in_stock;DESCRIBE catalog;

ResultMySQL 8.4

mysql> CREATE TABLE catalog AS SELECT * FROM products

Query OK, 9 rows affected

mysql> ALTER TABLE catalog

Query OK, 0 rows affected

mysql> DESCRIBE catalog

Field	Type	Null	Default
product_id	int	NO	NULL
product_name	varchar(50)	NO	NULL
category	varchar(30)	YES	NULL
price	int	YES	NULL
discount_pct	int	NO	0
units_in_stock	int	YES	NULL

6 rows in set

Quick quiz

1. Which clause renames a column without changing its type?

2. What happens to a NOT NULL constraint you forget to repeat in MODIFY?

3. Can ALTER TABLE be rolled back inside a transaction?

ALTER TABLE FAQ

How do I add a column to an existing table in SQL?

ALTER TABLE table_name ADD COLUMN column_name datatype [constraints]; Use AFTER other_column or FIRST to choose its position in MySQL.

What is the difference between MODIFY and CHANGE in MySQL?

MODIFY redefines a column but keeps its name. CHANGE can rename it and redefine it at the same time.

How do I rename a table in MySQL?

ALTER TABLE old_name RENAME TO new_name; or RENAME TABLE old_name TO new_name;

Does ALTER TABLE lock the table?

It depends on the change. Instant and in-place changes allow normal work; changes that rebuild the table can block writes for their duration. Specify ALGORITHM and LOCK to control it.