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.
Uses and Examples of ALTER TABLE
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 | +----------+------------------+------+-----+-------------------+-----------------------------+
Why Should You Use ALTER TABLE?
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.
