SQL ALTER TABLE

Once a table is created in the database, there are many occasions where one may wish to change the structure of the table. Typical cases include the following:

- Add a column
- Drop a column
- Change a column name
- Change the data type for a column

Please note that the above is not an exhaustive list. There are other instances where ALTER TABLE is used to change the table structure, such as changing the primary key specification or adding a unique constraint to a column.

The SQL syntax for ALTER TABLE is

ALTER TABLE "table_name"
[alter specification]

[alter specification] is dependent on the type of alteration we wish to perform. For the uses cited above, the [alter specification] statements are:

• Add a column: ADD "column 1" "data type for column 1"
• Drop a column: DROP "column 1"
• Change a column name: CHANGE "old column name" "new column name" "data type for new column name"
• Change the data type for a column: MODIFY "column 1" "new data type"

Let's run through examples for each one of the above, using the "customer" table created in the CREATE TABLE section:

Table customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Address	char(50)
City	char(50)
Country	char(25)
Birth_Date	date

First, we want to add a column called "Gender" to this table. To do this, we key in:

ALTER table customer add Gender char(1)

Resulting table structure:

Table customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Address	char(50)
City	char(50)
Country	char(25)
Birth_Date	date
Gender	char(1)

Next, we want to rename "Address" to "Addr". To do this, we key in,

ALTER table customer change Address Addr char(50)

Resulting table structure:

Table customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Addr	char(50)
City	char(50)
Country	char(25)
Birth_Date	date
Gender	char(1)

Then, we want to change the data type for "Addr" to char(30). To do this, we key in,

ALTER table customer modify Addr char(30)

Resulting table structure:

Table customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Addr	char(30)
City	char(50)
Country	char(25)
Birth_Date	date
Gender	char(1)

Finally, we want to drop the column "Gender". To do this, we key in,

ALTER table customer drop Gender

Resulting table structure:

Table customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Addr	char(30)
City	char(50)
Country	char(25)
Birth_Date	date

Link to this page: If you find this page useful, we encourage you to link to this page. Simply copy and paste the code below to your website, blog, or profile.

<a href="http://www.1keydata.com/sql/sql-alter-table.html">SQL ALTER TABLE Statement</a>