MySQL ALTER TABLE Options: Defaults, Keys and Engines

ALTER TABLE does more than add and remove columns.

query.sql

SQL

CREATE TABLE contacts (
    id INT NOT NULL,
    email VARCHAR(100),
    city VARCHAR(50)
);
INSERT INTO contacts VALUES (1, 'a@example.com', 'Pune'), (2, 'b@example.com', 'Delhi');

Change a default

query.sql

SQL

1
2

ALTER TABLE contacts ALTER COLUMN city SET DEFAULT 'Unknown';
ALTER TABLE contacts ALTER COLUMN city DROP DEFAULT;

SET DEFAULT and DROP DEFAULT change only the default, not the data or the type. They are fast, because MySQL only updates the table’s metadata.

Add and remove a primary key

query.sql

SQL

1
2
3

ALTER TABLE contacts ADD PRIMARY KEY (id);
ALTER TABLE contacts MODIFY id INT NOT NULL AUTO_INCREMENT;
SHOW COLUMNS FROM contacts;

Output

OUTPUT

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| email | varchar(100) | YES  |     | NULL    |                |
| city  | varchar(50)  | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set

query.sql

SQL

ALTER TABLE contacts DROP PRIMARY KEY;

Output

OUTPUT

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

You cannot drop the primary key of a table while its column is AUTO_INCREMENT, because an AUTO_INCREMENT column must be indexed. Remove the AUTO_INCREMENT property first, then drop the key.

query.sql

SQL

1
2
3

ALTER TABLE contacts MODIFY id INT NOT NULL;
ALTER TABLE contacts DROP PRIMARY KEY;
SHOW CREATE TABLE contacts\G

Output

OUTPUT

*************************** 1. row ***************************
       Table: contacts
Create Table: CREATE TABLE `contacts` (
  `id` int NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `city` varchar(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set

Add a unique rule

query.sql

SQL

1
2

ALTER TABLE contacts ADD CONSTRAINT uq_contacts_email UNIQUE (email);
INSERT INTO contacts VALUES (3, 'a@example.com', 'Kochi');

Output

OUTPUT

ERROR 1062 (23000): Duplicate entry 'a@example.com' for key 'contacts.uq_contacts_email'

If the table already has duplicates, adding a UNIQUE rule fails and tells you the offending value. Find and fix duplicates first:

query.sql

SQL

SELECT email, COUNT(*) AS copies FROM contacts GROUP BY email HAVING COUNT(*) > 1;

Output

OUTPUT

Empty set

Add an index

query.sql

SQL

1
2

ALTER TABLE contacts ADD INDEX idx_city (city);
SHOW INDEX FROM contacts;

Output

OUTPUT

+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| contacts |          0 | uq_contacts_email |            1 | email       | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| contacts |          1 | idx_city          |            1 | city        | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set

Change table options

query.sql

SQL

1
2

ALTER TABLE contacts COMMENT = 'People we write to';
ALTER TABLE contacts AUTO_INCREMENT = 500;

You can also change ENGINE, CHARSET and ROW_FORMAT, but these rebuild the whole table.

Rename a table

query.sql

SQL

1
2

ALTER TABLE contacts RENAME TO people;
SHOW TABLES;

Output

OUTPUT

+-----------------+
| Tables_in_learn |
+-----------------+
| people          |
+-----------------+
1 row in set

info

MySQL-specific

ADD COLUMN ... AFTER, MODIFY, CHANGE and ADD INDEX are MySQL syntax. Standard SQL changes a column’s type with ALTER COLUMN ... TYPE (PostgreSQL) and does not have AFTER.