MySQL ALTER TABLE Commands: How to Add, Delete, & Change Columns

MySQL (and MariaDB) allows you to change the structure of tables with the ALTER TABLE SQL command. Using the command, you can easily change the name of your table and columns, add or delete columns, or change the type of existing columns. Let’s see this in action.

First, let’s create a database and a table, which we’ll respectively name “test” and “employees.” Here’s how you’d do that:

mysql -u root -p
CREATE DATABASE test;
USE test;
CREATE TABLE employees ( id int NOT NULL, name varchar(255) );

Rename a Table

The syntax for renaming a table is:

ALTER TABLE table1 RENAME table2;

So, to change our employees table to “users,” execute the following:

ALTER TABLE employees RENAME users;

Helpful hint: ALTER TABLE on its own does nothing. Start with the command and the table name, then specify the alterations to be made.

Add a Column and Change Column Properties

The syntax for adding a column to a table is:

ALTER TABLE tablename ADD columnname datatype

To remove or delete a column:

ALTER TABLE tablename DROP COLUMN columname;

Here, we will add a string column called “address” by typing:

ALTER TABLE users ADD COLUMN address varchar(10);

Add a TIMESTAMP column named “date” by entering the following command:

ALTER TABLE users ADD date TIMESTAMP;

Add an index on the column named “id” by running:

ALTER TABLE users ADD INDEX (id);

It is also possible to do multiple additions at once:

ALTER TABLE users ADD uid INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD UNIQUE (uid);

Our table should now look like this:

mysql> DESCRIBE users;
+---------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | MUL | NULL | |
| name | varchar(255) | YES | | NULL | |
| address | varchar(10) | YES | | NULL | |
| date | timestamp | NO | | CURRENTTIMESTAMP | on update CURRENTTIMESTAMP |
| uid | int(10) unsigned | NO | PRI | NULL | auto_increment |
+---------+------------------+------+-----+-------------------+-----------------------------+

Modify a Column Type

The column modification syntax is:

ALTER TABLE tablename MODIFY COLUMN columnname datatype;

To modify the address field to allow for larger strings:

ALTER TABLE users MODIFY address VARCHAR(255);

We can also combine multiple modifications at once:

ALTER TABLE users MODIFY address VARCHAR(255), CHANGE name lastname VARCHAR(255) NOT NULL;

With this command, we also changed the column called “name” to “lastname” and disallowed NULL values for it.

Changing the Default Column Value

To change the default value on a column, use the SET DEFAULT syntax:

ALTER TABLE users ALTER address SET DEFAULT 'unknown';

Our final table now looks like this:

mysql> DESCRIBE users;
+----------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | MUL | NULL | |
| lastname | varchar(255) | NO | | NULL | |
| address | varchar(255) | YES | | unknown | |
| date | timestamp | NO | | CURRENTTIMESTAMP | on update CURRENTTIMESTAMP |
| uid | int(10) unsigned | NO | PRI | NULL | auto_increment |
+----------+------------------+------+-----+-------------------+-----------------------------+

Making changes to a MySQL database structure via the command line and ALTER TABLE can sometimes be faster and more flexible than using PHPMyAdmin or similar GUI tools.

The command offers even more features that we didn’t cover here. For more information, check out the MySQL documentation or ask a question down in the comments section.