SQL Alter Table

In this tutorial you will learn how to alter or modify an existing table using SQL.

## Modifying Existing Tables
It is quite possible that after creating a table, as you start using it, you may discover you've forgot to mention any column or constraint or specified a wrong name for the column.

In such situation you can use the `ALTER TABLE` statement to alter or change an existing table by adding, changing, or deleting a column in the table.

Consider we've a shippers table in our database, whose structure is as follows:

Now suppose that we want to expand the existing shippers table by adding one more column. But, the question is how we can do this using SQL commands? Well let's find out.

## Adding a New Column
The basic syntax for adding a new column to an existing table can be given with:

```sql
ALTER TABLE table_name ADD column_name data_type constraints;
```
The following statement adds a new column fax to the shippers table.

```sql
ALTER TABLE shippers ADD fax VARCHAR(20);
```
Now, after executing the above statement if you see the table structure using the command `DESCRIBE shippers;` on MySQL command-line, it looks as follow:

<div class="callout callout-success mb-3">Tip: When adding a new column to the table, if neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified.</div>

MySQL add new columns at the end by default. However, if you want to add a new column after a specific column you can use the AFTER clause, as follow:

```sql
mysql> ALTER TABLE shippers ADD fax VARCHAR(20) AFTER shipper_name;
```
MySQL provide another clause `FIRST` that you can use to add a new column at first place within a table. Just replace the clause `AFTER` with `FIRST` in the previous example to add the column fax at the beginning of the shippers table.

## Changing Column Position
In MySQL, if you've already created a table but unhappy with the existing column position within the table, you can change it any time using the following syntax:

```sql
ALTER TABLE table_name
MODIFY column_name column_definition AFTER column_name;
```
The following statement place the column fax after shipper_name column in shippers table.

```sql
mysql> ALTER TABLE shippers MODIFY fax VARCHAR(20) AFTER shipper_name;
```
## Adding Constraints
Our current shippers table has one major problem. If you insert records with duplicate phone numbers it wouldn't stop you from doing that, which is not good, it should be unique.

You can fix this by adding a constraint UNIQUE to the phone column. The basic syntax for adding this constraint to existing table columns can be given with:

```sql
ALTER TABLE table_name ADD UNIQUE (column_name,...);
```
The following statement adds a constraint UNIQUE to the phone column.

```sql
mysql> ALTER TABLE shippers ADD UNIQUE (phone);
```
After executing this statement if you try to insert a duplicate phone number, you'll get an error.

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

```sql
ALTER TABLE table_name ADD PRIMARY KEY (column_name,...);
```
The following statement adds a constraint PRIMARY KEY to the shipper_id column, if not defined.

```sql
mysql> ALTER TABLE shippers ADD PRIMARY KEY (shipper_id);
```
## Removing Columns
The basic syntax for removing a column from an existing table can be given with:

```sql
ALTER TABLE table_name DROP COLUMN column_name;
```
The following statement removes our newly added column fax from the shippers table.

```sql
mysql> ALTER TABLE shippers DROP COLUMN fax;
```
Now, after executing the above statement if you see the table structure, it looks as follow:

```sql
ALTER TABLE table_name ALTER COLUMN column_name new_data_type;
```
The MySQL database server however does not support the ALTER COLUMN syntax. It supports an alternate MODIFY clause that you can use to modify the column as follows:

```sql
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 length from 20 to 15.

```sql
mysql> ALTER TABLE shippers MODIFY phone CHAR(15);
```
Similarly, you can use the MODIFY clause to switch between whether a column in the MySQL table should allow null values or not by re-specifying the existing column definition and add the NULL or NOT NULL constraint at the end, like this:

```sql
mysql> ALTER TABLE shippers MODIFY shipper_name CHAR(15) NOT NULL;
```
## Renaming Tables
The basic syntax for renaming an existing table in MySQL can be given with:

```sql
ALTER TABLE current_table_name RENAME new_column_name;
```
The following statement renames our shippers table shipper.

```sql
mysql> ALTER TABLE shippers RENAME shipper;
```
You can also achieve the same thing in MySQL using the `RENAME TABLE` statement, as follow:

```sql
mysql> RENAME TABLE shippers TO shipper;
```