Change a table with ALTER TABLE

40 min · Intermediate

Tables change after they are built: the store wants loyalty points, a column name turns out to be unclear, a text column is too short. How do you change a table that already holds rows, without losing them?

ADD COLUMN with and without a default

ALTER TABLE changes the design of a table that already exists, such as adding, renaming, changing or dropping a column. Each kind of change does something specific to the rows already stored.

ALTER TABLE t ADD COLUMN name TYPE adds a new column at the end of the table.

Example 1 · ShopLite categories get two new columns

SQL

ALTER TABLE categories ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;ALTER TABLE categories ADD COLUMN description VARCHAR(200);
SELECT * FROM categories ORDER BY category_id;

Output

+-------------+-------------+-----------+-----------+-------------+
| category_id | name        | parent_id | is_active | description |
+-------------+-------------+-----------+-----------+-------------+
|           1 | Electronics |      NULL |         1 | NULL        |
|           2 | Mobiles     |         1 |         1 | NULL        |
|           3 | Laptops     |         1 |         1 | NULL        |
|           4 | Accessories |         1 |         1 | NULL        |
|           5 | Books       |      NULL |         1 | NULL        |
|           6 | Fashion     |      NULL |         1 | NULL        |
|           7 | Toys        |      NULL |         1 | NULL        |
+-------------+-------------+-----------+-----------+-------------+

What this code does

All 7 existing categories got is_active = 1 from the default, and description = NULL because that column has no default and allows NULL.
The rows were not touched otherwise. The store can now hide a category by setting is_active to 0 instead of deleting it.

Example 2 · NOT NULL without a default fills in empty values

SQL

1CREATE TABLE ipl_players (2  player_id  INT AUTO_INCREMENT PRIMARY KEY,3  name       VARCHAR(40) NOT NULL,4  team       CHAR(3) NOT NULL5);6
7INSERT INTO ipl_players (name, team) VALUES8  ('Virat Kohli', 'RCB'), ('MS Dhoni', 'CSK'), ('Rohit Sharma', 'MI');9
10ALTER TABLE ipl_players ADD COLUMN matches INT NOT NULL;11ALTER TABLE ipl_players ADD COLUMN country VARCHAR(30) NOT NULL;12ALTER TABLE ipl_players ADD COLUMN role VARCHAR(20) NOT NULL DEFAULT 'Batter' AFTER name;13
14SELECT * FROM ipl_players ORDER BY player_id;

Output

+-----------+--------------+--------+------+---------+---------+
| player_id | name         | role   | team | matches | country |
+-----------+--------------+--------+------+---------+---------+
|         1 | Virat Kohli  | Batter | RCB  |       0 |         |
|         2 | MS Dhoni     | Batter | CSK  |       0 |         |
|         3 | Rohit Sharma | Batter | MI   |       0 |         |
+-----------+--------------+--------+------+---------+---------+

What this code does

matches and country are NOT NULL with no default, so MySQL filled the old rows with 0 and an empty text: the country cells look blank, but they are '', not NULL. Nothing warned about it.
role has a real default, so every player got Batter, and AFTER name placed the column second. Always give a default when adding a required column to a table with rows.

The rows already in the table must get some value in it, and which value depends on how you define the column.

No rules (ADD COLUMN gst_no VARCHAR(15)): every existing row gets NULL, meaning "no value yet".
With a default (ADD COLUMN loyalty_points INT NOT NULL DEFAULT 0): every existing row gets the default. This is the clean way to add a required column.
NOT NULL with no default: MySQL fills existing rows with the type's built-in empty value: 0 for numbers, an empty text '' for VARCHAR. The insert of a new row then fails if it leaves the column out. That silent '' is a trap.

By default the column goes last. AFTER other_column places it after a given column, and FIRST makes it the first column. Order only affects how SELECT * and DESCRIBE list the columns.

NOT NULL means a value is required, and DEFAULT is the value used when an insert leaves the column out. The Keys & Constraints module covers both in depth.

RENAME COLUMN and RENAME TABLE

ALTER TABLE t RENAME COLUMN old TO new changes only a column's name; its type, rules and data stay the same. It works in MySQL 8.0 and later.

Example 3 · Renaming a column and a table

SQL

1CREATE TABLE tbl_marks (2  roll  INT,3  amt   TINYINT4);5INSERT INTO tbl_marks (roll, amt) VALUES (1, 88), (2, 74);6
7ALTER TABLE tbl_marks RENAME COLUMN amt TO marks;8ALTER TABLE tbl_marks RENAME COLUMN roll TO roll_no;9RENAME TABLE tbl_marks TO exam_marks;10
11SELECT * FROM exam_marks ORDER BY roll_no;12SELECT amt FROM exam_marks;

Output

+---------+-------+
| roll_no | marks |
+---------+-------+
|       1 |    88 |
|       2 |    74 |
+---------+-------+

ERROR 1054 (42S22): Unknown column 'amt' in 'field list'

What this code does

The data survived both renames: same rows, same values, new names.
The old name amt no longer exists, so the last query fails with error 1054. Every query and app using the old name breaks in the same way.

RENAME TABLE old TO new (or ALTER TABLE old RENAME TO new) renames a whole table, again keeping every row.

Why rename? Names are read far more often than they are written. amt becomes amount, tbl_students becomes students, and every later query is easier to read. The risk is outside the table: every query, report and app that uses the old name breaks at once, for example with ERROR 1054 (42S22): Unknown column 'name' in 'field list'. On a shared database, rename only together with the code that uses the name.

Fix the error

Fix the query

Read the real error, then pick the line that fixes it.

The column was renamed, but the query still fails.

ALTER TABLE tbl_marks RENAME COLUMN amt TO marks;

SELECT amt FROM tbl_marks;

Error

ERROR 1054 (42S22): Unknown column 'amt' in 'field list'

MODIFY and CHANGE COLUMN

MODIFY changes a column's type or rules and keeps its name. CHANGE renames and redefines it in one step. Both replace the whole column definition, so write everything you want to keep.

MODIFY column new_definition changes a column's type or rules and keeps its name: MODIFY name VARCHAR(80). CHANGE old_name new_name new_definition renames and redefines in one step: CHANGE city home_city VARCHAR(40). With CHANGE you must always write the full type, even if only the name changes.

Example 4 · MODIFY replaces the whole definition

SQL

1CREATE TABLE hostel_residents (2  roll_no  INT,3  name     VARCHAR(30) NOT NULL DEFAULT 'Unknown',4  city     VARCHAR(20)5);6
7ALTER TABLE hostel_residents MODIFY name VARCHAR(50);8ALTER TABLE hostel_residents CHANGE city home_city VARCHAR(40);9
10DESCRIBE hostel_residents;

Output

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| roll_no   | int         | YES  |     | NULL    |       |
| name      | varchar(50) | YES  |     | NULL    |       |
| home_city | varchar(40) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

What this code does

MODIFY name VARCHAR(50) made the column longer but also dropped NOT NULL and the default Unknown: Null is now YES and Default NULL. The correct statement was MODIFY name VARCHAR(50) NOT NULL DEFAULT 'Unknown'.
CHANGE city home_city VARCHAR(40) renamed the column and widened it in one step; the type had to be written in full.

Example 5 · Shrinking a column that has data

SQL

1CREATE TABLE exam_scores (2  roll_no  INT,3  name     VARCHAR(30),4  score    INT5);6
7INSERT INTO exam_scores (roll_no, name, score) VALUES8  (1, 'Asha Rao', 95),9  (2, 'Ravi', 301);10
11ALTER TABLE exam_scores MODIFY name VARCHAR(20);12DESCRIBE exam_scores;13
14ALTER TABLE exam_scores MODIFY score TINYINT;

Output

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| roll_no | int         | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| score   | int         | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

ERROR 1264 (22003): Out of range value for column 'score' at row 2

What this code does

VARCHAR(30) to VARCHAR(20) worked, because every stored name has 20 characters or fewer.
Changing score to TINYINT fails with error 1264 at row 2: 301 does not fit in -128 to 127. The whole change is refused and no value is cut. Fix the bad row (301 is a typo) before you shrink the column.

Always copy the full definition from SHOW CREATE TABLE and change only what you mean to change.

MySQL converts every existing value to the new type. Making a column bigger is safe. Making it smaller fails, in strict mode, if any row does not fit, with the same errors as an insert: ERROR 1265 ... Data truncated for text that is too long, ERROR 1264 ... Out of range for numbers.

Predict the output

Predict the change

The column name is VARCHAR(30) NOT NULL DEFAULT 'Unknown'. What happens to NOT NULL?

ALTER TABLE hostel_residents

MODIFY name VARCHAR(50);

DROP COLUMN and what happens to the data

DROP COLUMN removes a column and every value stored in it, in all rows. There is no undo: the only way back is a backup.

ALTER TABLE t DROP COLUMN name removes a column and every value stored in it, in all rows. There is no undo: the only way back is a backup. The other columns and all rows stay.

Example 6 · DROP COLUMN removes the data for good

SQL

1CREATE TABLE order_lines_v2 (2  order_no      INT,3  item          VARCHAR(40),4  coupon_code   VARCHAR(12)5);6
7INSERT INTO order_lines_v2 VALUES (1, 'Notebook', 'FEST50'), (2, 'Pen', NULL);8
9ALTER TABLE order_lines_v2 DROP COLUMN coupon_code;10SELECT * FROM order_lines_v2 ORDER BY order_no;11
12ALTER TABLE order_lines_v213  ADD COLUMN price DECIMAL(7,2) NOT NULL DEFAULT 0,14  MODIFY item VARCHAR(60) NOT NULL;15DESCRIBE order_lines_v2;16
17ALTER TABLE order_lines_v2 DROP COLUMN coupon_code;

Output

+----------+----------+
| order_no | item     |
+----------+----------+
|        1 | Notebook |
|        2 | Pen      |
+----------+----------+
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| order_no | int          | YES  |     | NULL    |       |
| item     | varchar(60)  | NO   |     | NULL    |       |
| price    | decimal(7,2) | NO   |     | 0.00    |       |
+----------+--------------+------+-----+---------+-------+

ERROR 1091 (42000): Can't DROP 'coupon_code'; check that column/key exists

What this code does

After DROP COLUMN coupon_code, the code FEST50 is gone from the database; the other columns and both rows remain.
The second ALTER TABLE makes two changes at once, separated by a comma.
Dropping coupon_code again fails with error 1091, because the column no longer exists.

Dropping a column that does not exist gives ERROR 1091 (42000): Can't DROP 'city'; check that column/key exists. A table must keep at least one column, so dropping the last one gives error 1090 and tells you to use DROP TABLE instead.

Before dropping, check that no query or app still reads the column, and that the data is not needed for old reports. On real systems, teams often stop using a column first and drop it weeks later.

Several changes in one ALTER TABLE

One ALTER TABLE can hold several changes, separated by commas: ALTER TABLE t ADD COLUMN a INT, MODIFY b VARCHAR(80), DROP COLUMN c;. MySQL applies them together, and if one part fails, none of them is applied.

SQL

1CREATE TABLE train_tickets (2  pnr       CHAR(10) PRIMARY KEY,3  passenger VARCHAR(40) NOT NULL,4  fare      INT,5  coach     VARCHAR(5)6);7
8ALTER TABLE train_tickets9  ADD COLUMN berth VARCHAR(10) NULL,10  MODIFY fare DECIMAL(8,2) NOT NULL,11  DROP COLUMN coach;12
13DESCRIBE train_tickets;

Output

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| pnr       | char(10)     | NO   | PRI | NULL    |       |
| passenger | varchar(40)  | NO   |     | NULL    |       |
| fare      | decimal(8,2) | NO   |     | NULL    |       |
| berth     | varchar(10)  | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

This matters on big tables, because many changes make MySQL rebuild the table, copying every row. One statement with three changes rebuilds once instead of three times.

A safe routine for any change on real data:

Look at the current design with SHOW CREATE TABLE.
Try the exact statement on a copy or a test database first.
Take a backup, then run it, and check the result with DESCRIBE and a SELECT.

ALTER TABLE on a table that exists. Before: product_id, name, price, legacy_code. RENAME COLUMN name TO title renames a column. DROP COLUMN legacy_code removes it with its data. ADD COLUMN stock INT adds one. After: product_id, title, price, stock.

Order of execution

A safe change routine

Put the steps of a safe change on real data in order.

Next step

Check your understanding

11 quick questions

+-------------+-------------+-----------+-----------+-------------+ | category_id | name | parent_id | is_active | description | +-------------+-------------+-----------+-----------+-------------+ | 1 | Electronics | NULL | 1 | NULL | | 2 | Mobiles | 1 | 1 | NULL | | 3 | Laptops | 1 | 1 | NULL | | 4 | Accessories | 1 | 1 | NULL | | 5 | Books | NULL | 1 | NULL | | 6 | Fashion | NULL | 1 | NULL | | 7 | Toys | NULL | 1 | NULL | +-------------+-------------+-----------+-----------+-------------+

1CREATE TABLE ipl_players (2 player_id INT AUTO_INCREMENT PRIMARY KEY,3 name VARCHAR(40) NOT NULL,4 team CHAR(3) NOT NULL5);6 7INSERT INTO ipl_players (name, team) VALUES8 ('Virat Kohli', 'RCB'), ('MS Dhoni', 'CSK'), ('Rohit Sharma', 'MI');9 10ALTER TABLE ipl_players ADD COLUMN matches INT NOT NULL;11ALTER TABLE ipl_players ADD COLUMN country VARCHAR(30) NOT NULL;12ALTER TABLE ipl_players ADD COLUMN role VARCHAR(20) NOT NULL DEFAULT 'Batter' AFTER name;13 14SELECT * FROM ipl_players ORDER BY player_id;

+-----------+--------------+--------+------+---------+---------+ | player_id | name | role | team | matches | country | +-----------+--------------+--------+------+---------+---------+ | 1 | Virat Kohli | Batter | RCB | 0 | | | 2 | MS Dhoni | Batter | CSK | 0 | | | 3 | Rohit Sharma | Batter | MI | 0 | | +-----------+--------------+--------+------+---------+---------+

1CREATE TABLE tbl_marks (2 roll INT,3 amt TINYINT4);5INSERT INTO tbl_marks (roll, amt) VALUES (1, 88), (2, 74);6 7ALTER TABLE tbl_marks RENAME COLUMN amt TO marks;8ALTER TABLE tbl_marks RENAME COLUMN roll TO roll_no;9RENAME TABLE tbl_marks TO exam_marks;10 11SELECT * FROM exam_marks ORDER BY roll_no;12SELECT amt FROM exam_marks;

MODIFY and CHANGE COLUMN

MODIFY changes a column's type or rules and keeps its name. CHANGE renames and redefines it in one step. Both replace the whole column definition, so write everything you want to keep.

1CREATE TABLE hostel_residents (2 roll_no INT,3 name VARCHAR(30) NOT NULL DEFAULT 'Unknown',4 city VARCHAR(20)5);6 7ALTER TABLE hostel_residents MODIFY name VARCHAR(50);8ALTER TABLE hostel_residents CHANGE city home_city VARCHAR(40);9 10DESCRIBE hostel_residents;

1CREATE TABLE exam_scores (2 roll_no INT,3 name VARCHAR(30),4 score INT5);6 7INSERT INTO exam_scores (roll_no, name, score) VALUES8 (1, 'Asha Rao', 95),9 (2, 'Ravi', 301);10 11ALTER TABLE exam_scores MODIFY name VARCHAR(20);12DESCRIBE exam_scores;13 14ALTER TABLE exam_scores MODIFY score TINYINT;

1CREATE TABLE order_lines_v2 (2 order_no INT,3 item VARCHAR(40),4 coupon_code VARCHAR(12)5);6 7INSERT INTO order_lines_v2 VALUES (1, 'Notebook', 'FEST50'), (2, 'Pen', NULL);8 9ALTER TABLE order_lines_v2 DROP COLUMN coupon_code;10SELECT * FROM order_lines_v2 ORDER BY order_no;11 12ALTER TABLE order_lines_v213 ADD COLUMN price DECIMAL(7,2) NOT NULL DEFAULT 0,14 MODIFY item VARCHAR(60) NOT NULL;15DESCRIBE order_lines_v2;16 17ALTER TABLE order_lines_v2 DROP COLUMN coupon_code;

+----------+----------+ | order_no | item | +----------+----------+ | 1 | Notebook | | 2 | Pen | +----------+----------+ +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | order_no | int | YES | | NULL | | | item | varchar(60) | NO | | NULL | | | price | decimal(7,2) | NO | | 0.00 | | +----------+--------------+------+-----+---------+-------+ ERROR 1091 (42000): Can't DROP 'coupon_code'; check that column/key exists

1CREATE TABLE train_tickets (2 pnr CHAR(10) PRIMARY KEY,3 passenger VARCHAR(40) NOT NULL,4 fare INT,5 coach VARCHAR(5)6);7 8ALTER TABLE train_tickets9 ADD COLUMN berth VARCHAR(10) NULL,10 MODIFY fare DECIMAL(8,2) NOT NULL,11 DROP COLUMN coach;12 13DESCRIBE train_tickets;