ALTER TABLE and DROP TABLE: MySQL

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonModify an existing table's structure with ALTER TABLE and remove tables safely.

Requirements change

No design survives contact with reality. A month after launch someone wants a phone number on customers, a longer product title, or a NOT NULL rule that was forgotten. ALTER TABLE changes a table's structure while keeping its data.

Throughout this lesson we work with a small practice table:

sql

CREATE DATABASE IF NOT EXISTS practice;
USE practice;
DROP TABLE IF EXISTS gadgets;
CREATE TABLE gadgets (
  id    INT PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(30) NOT NULL,
  price DECIMAL(8,2) NOT NULL
);
INSERT INTO gadgets (name, price) VALUES ('Lamp', 19.50), ('Fan', 45.00), ('Kettle', 32.25);

Add a column

sql

ALTER TABLE gadgets ADD COLUMN colour VARCHAR(20) NULL;
ALTER TABLE gadgets ADD COLUMN in_stock BOOLEAN NOT NULL DEFAULT TRUE AFTER name;
DESCRIBE gadgets;

result

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| name     | varchar(30)  | NO   |     | NULL    |                |
| in_stock | tinyint(1)   | NO   |     | 1       |                |
| price    | decimal(8,2) | NO   |     | NULL    |                |
| colour   | varchar(20)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

New columns appear at the end by default. AFTER name (or FIRST) positions them. Existing rows get the default, or NULL if there is none. A NOT NULL column added to a table with rows needs a DEFAULT, or MySQL fills in a zero-like value (empty string, 0) which might not be what you want.

Modify a column

MODIFY changes the type or rules but keeps the name. You must restate the whole definition, including NOT NULL and DEFAULT, because anything you leave out is reset:

sql

ALTER TABLE gadgets MODIFY name VARCHAR(80) NOT NULL;
DESCRIBE gadgets;

result

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| name     | varchar(80)  | NO   |     | NULL    |                |
| in_stock | tinyint(1)   | NO   |     | 1       |                |
| price    | decimal(8,2) | NO   |     | NULL    |                |
| colour   | varchar(20)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

Widening a column (VARCHAR(30) to VARCHAR(80)) is safe. Tightening a rule is not always possible. The existing rows have a NULL colour, so making the column NOT NULL is refused:

sql

ALTER TABLE gadgets MODIFY colour VARCHAR(20) NOT NULL DEFAULT 'black';

result

ERROR 1138 (22004): Invalid use of NULL value

Fix the data first, then apply the stricter rule:

sql

UPDATE gadgets SET colour = 'black' WHERE colour IS NULL;
ALTER TABLE gadgets MODIFY colour VARCHAR(20) NOT NULL DEFAULT 'black';

Narrowing a column fails too if any value is too long:

sql

ALTER TABLE gadgets MODIFY name VARCHAR(3) NOT NULL;

result

ERROR 1406 (22001): Data too long for column 'name' at row 1

Changing between unrelated types, such as text to a number, converts the values and may fail or lose information. Always check the data and take a backup first.

Rename and change columns

RENAME COLUMN (MySQL 8.0) is the clearest way to rename:

sql

ALTER TABLE gadgets RENAME COLUMN colour TO color;

The older CHANGE old new definition renames and redefines at once. It needs the full definition again. Remember that renaming a column breaks any application code, views or stored routines that use the old name, so rename deliberately.

Drop a column

sql

ALTER TABLE gadgets DROP COLUMN color;
DESCRIBE gadgets;

result

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| name     | varchar(80)  | NO   |     | NULL    |                |
| in_stock | tinyint(1)   | NO   |     | 1       |                |
| price    | decimal(8,2) | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

The column and all its data are gone, and there is no undo outside a backup. Dropping a column also fails if a constraint or index depends on it.

Constraints and indexes

The same statement adds and removes rules:

sql

ALTER TABLE gadgets ADD CONSTRAINT chk_gadgets_price CHECK (price > 0);
ALTER TABLE gadgets ADD UNIQUE KEY uq_gadgets_name (name);
ALTER TABLE gadgets ADD INDEX idx_gadgets_price (price);
SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'practice' AND TABLE_NAME = 'gadgets' ORDER BY INDEX_NAME;

result

+-------------------+-------------+------------+
| INDEX_NAME        | COLUMN_NAME | NON_UNIQUE |
+-------------------+-------------+------------+
| idx_gadgets_price | price       |          1 |
| PRIMARY           | id          |          0 |
| uq_gadgets_name   | name        |          0 |
+-------------------+-------------+------------+

sql

INSERT INTO gadgets (name, price) VALUES ('Lamp', 25.00);

result

ERROR 1062 (23000): Duplicate entry 'Lamp' for key 'gadgets.uq_gadgets_name'

To remove them: DROP CHECK chk_gadgets_price, DROP INDEX uq_gadgets_name, DROP FOREIGN KEY fk_name and DROP PRIMARY KEY.

You can combine several changes in one statement, separated by commas. That is faster than several statements, because MySQL may rebuild the table only once:

sql

ALTER TABLE gadgets DROP INDEX idx_gadgets_price, ADD COLUMN weight_g INT UNSIGNED NULL;

Rename a table

sql

RENAME TABLE gadgets TO devices;
SHOW TABLES LIKE 'devices';

result

+------------------------------+
| Tables_in_practice (devices) |
+------------------------------+
| devices                      |
+------------------------------+

RENAME TABLE can swap several tables atomically, which is a handy trick for deployments: RENAME TABLE devices TO devices_old, devices_new TO devices;.

Drop a table

sql

DROP TABLE IF EXISTS devices;

DROP TABLE removes the definition and all rows immediately. It fails if another table has a foreign key pointing to it, until you drop the child first. IF EXISTS makes the statement safe to repeat. TRUNCATE TABLE keeps the structure but removes the rows.

Large tables and live systems

On a table with millions of rows, an ALTER TABLE may take minutes. MySQL 8 can perform many changes "in place" or "instantly" (adding a column at the end is often instant), but others copy the whole table and block writes. In production:

Run the same ALTER on a copy of the data first and time it.
Check the allowed algorithm: ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT; fails immediately if instant is impossible, instead of locking the table unexpectedly.
Take a backup before destructive changes.
Tools such as pt-online-schema-change or gh-ost exist for very large, busy tables.

Common mistakes

Forgetting to restate NOT NULL or DEFAULT in MODIFY.
Narrowing a column without checking the data.
Renaming or dropping a column that application code still uses.
Running a big ALTER on a busy production table without a plan.
Running DROP TABLE in the wrong database. Check with SELECT DATABASE(); first.

Exercise

Create a table trips with id, destination (VARCHAR 40) and days (INT). Add a price decimal column with a default of 0, widen destination to 100 characters, rename days to duration_days, and finally list the columns.

Show one possible answer

sql

DROP TABLE IF EXISTS trips;
CREATE TABLE trips (id INT PRIMARY KEY AUTO_INCREMENT, destination VARCHAR(40) NOT NULL, days INT NOT NULL);
ALTER TABLE trips
  ADD COLUMN price DECIMAL(8,2) NOT NULL DEFAULT 0,
  MODIFY destination VARCHAR(100) NOT NULL,
  RENAME COLUMN days TO duration_days;
DESCRIBE trips;

result

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int          | NO   | PRI | NULL    | auto_increment |
| destination   | varchar(100) | NO   |     | NULL    |                |
| duration_days | int          | NO   |     | NULL    |                |
| price         | decimal(8,2) | NO   |     | 0.00    |                |
+---------------+--------------+------+-----+---------+----------------+

Key points

ALTER TABLE changes a table's structure without recreating it by hand: ADD, MODIFY, CHANGE, RENAME and DROP for columns, constraints and indexes.
Changing a column type can fail or convert data. Check existing values and take a backup first.
On large live tables, ALTER TABLE can lock or slow the table. Plan the change and test it on a copy.
DROP TABLE removes the table and its data permanently. Use IF EXISTS in scripts and think twice.

Requirements change

Add a column

Modify a column

Rename and change columns

Drop a column

Constraints and indexes

Rename a table

Drop a table

Large tables and live systems

Common mistakes

Exercise

Key points

Check yourself