SQL Alter Table

Previous Page Next Page

Modifying Existing Tables

It's common that after creating a table and starting to use it, you may realize you forgot to include a column, specified a column name incorrectly, or need to add or remove constraints.

In such situations, you can use the ALTER TABLE statement to modify an existing table by adding, changing, or deleting columns.

Let's consider we have a shippers table in our database, structured as follows:

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| shipper_id   | int         | NO   | PRI | NULL    | auto_increment |
| shipper_name | varchar(60) | NO   |     | NULL    |                |
| phone        | varchar(60) | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

We'll use the shippers table for all of our ALTER TABLE statements.

Now, suppose we want to expand the existing shippers table by adding one more column. Let's explore how we can accomplish this using SQL commands.

Adding a New Column

The basic syntax for adding a new column to an existing table is as follows:

ALTER TABLE table_name ADD column_name data_type constraints;

The next statement adds a new column called fax to the shippers table.

Example

Try this code »

ALTER TABLE shippers ADD fax VARCHAR(20);

After executing the statement above, if you view the table structure using the command DESCRIBE shippers; in MySQL command-line, it will appear as follows:

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| shipper_id   | int         | NO   | PRI | NULL    | auto_increment |
| shipper_name | varchar(60) | NO   |     | NULL    |                |
| phone        | varchar(60) | NO   |     | NULL    |                |
| fax          | varchar(20) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

Note: When adding a NOT NULL column to an existing table, you must specify an explicit default value. This default value will be used to populate the new column for every existing row in your table.

Tip: When adding a new column to a table and neither NULL nor NOT NULL is specified, the column is treated as if NULL had been specified.

By default, MySQL adds new columns at the end of a table. However, if you want to add a new column after a specific existing column, you can use the AFTER clause, as shown below:

mysql> ALTER TABLE shippers ADD fax VARCHAR(20) AFTER shipper_name;

MySQL provides another clause, FIRST, which you can use to add a new column at the beginning of a table. Simply replace the AFTER clause with FIRST in the previous example to add the column fax at the start of the shippers table.

Changing Column Position

In MySQL, if you've already created a table but are unhappy with the current position of a column within the table, you can change it at any time using the following syntax:

ALTER TABLE table_name
MODIFY column_name column_definition AFTER column_name;

The following statement places the fax column after the shipper_name column in the shippers table.

mysql> ALTER TABLE shippers MODIFY fax VARCHAR(20) AFTER shipper_name;

Adding Constraints

Our current shippers table has a significant issue. It currently allows the insertion of records with duplicate phone numbers, which should not be allowed. To enforce uniqueness, you can add a UNIQUE constraint to the phone column. The basic syntax for adding this constraint to existing table columns is as follows:

ALTER TABLE table_name ADD UNIQUE (column_name,...);

The following statement adds a UNIQUE constraint to the phone column.

mysql> ALTER TABLE shippers ADD UNIQUE (phone);

After executing this statement, attempting to insert a duplicate phone number will result in an error.

Similarly, if you've created a table without a PRIMARY KEY, you can add one with:

ALTER TABLE table_name ADD PRIMARY KEY (column_name,...);

The following statement adds a PRIMARY KEY constraint to the shipper_id column, if it's not already defined.

mysql> ALTER TABLE shippers ADD PRIMARY KEY (shipper_id);

Removing Columns

The basic syntax for removing a column from an existing table is as follows:

ALTER TABLE table_name DROP COLUMN column_name;

The following statement removes the newly added column fax from the shippers table.

mysql> ALTER TABLE shippers DROP COLUMN fax;

After executing the statement above, if you view the table structure, it will look like this:

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| shipper_id   | int         | NO   | PRI | NULL    | auto_increment |
| shipper_name | varchar(60) | NO   |     | NULL    |                |
| phone        | varchar(20) | NO   | UNI | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

Changing Data Type of a Column

You can change the data type of a column in SQL Server using the ALTER clause, as follows:

ALTER TABLE table_name ALTER COLUMN column_name new_data_type;

However, the MySQL database server does not support the ALTER COLUMN syntax. Instead, it supports an alternate MODIFY clause that you can use to modify the column, as shown below:

ALTER TABLE table_name MODIFY column_name new_data_type;

The following statement changes the current data type of the phone column in our shippers table from VARCHAR to CHAR, and adjusts the length from 20 to 15.

mysql> ALTER TABLE shippers MODIFY phone CHAR(15);

Similarly, in MySQL, you can use the MODIFY clause to specify whether a column in a table should allow null values or not. You can do this by re-specifying the existing column definition and adding the NULL or NOT NULL constraint at the end, as shown below:

mysql> ALTER TABLE shippers MODIFY shipper_name CHAR(15) NOT NULL;

Renaming Tables

The basic syntax for renaming an existing table in MySQL is as follows:

ALTER TABLE current_table_name RENAME new_column_name;

The following statement renames our shippers table to shipper.

mysql> ALTER TABLE shippers RENAME shipper;

In MySQL, you can achieve the same renaming of a table using the RENAME TABLE statement, like this:

mysql> RENAME TABLE shippers TO shipper;

Previous Page Next Page