SQL Alter Table

Overview

SQL Alter table is used to update the database table structure of an existing table. Alter table command can add, delete or modify any of the column in an exisitng table. Lot of time we get a requirement to either add a new column, delete an existing column or change the name, place, data type or data a column can hold, In all such requirement we can use SQL Alter statement.

Syntax for SQL Alter statement for add, drop and modify a column is mentioned below in their respective orders

Syntax

ADD a new column

ALTER TABLE table_name ADD new_column_name data_type;

DROP an exiting column

ALTER TABLE table_name DROP old_column_name;

Update an exiting column

ALTER TABLE table_name MODIFY COLUMN old_column_name data_type;

In MYSQL above command can be written as

ALTER TABLE table_name MODIFY column_name data_type;

In MYSQL if we have to update an exiting column by changing its name also then the command is

ALTER TABLE table_name change old_column_name new_column_name data_type;

Example

Let us have a better understanding with the help of an example

Consider table hostel_list_archive that we created in our previous step

+----+-------------------+-----------+----------+
| id | name              | occupancy | capacity |
+----+-------------------+-----------+----------+
|  1 | ASHOK BHAVAN      |         2 |     1090 |
|  2 | KAILASH BHAVAN    |         2 |      990 |
|  3 | RUDRAKSH BHAVAN   |         2 |      990 |
|  4 | KAVERI BHAVAN     |         1 |      690 |
|  5 | BHAGIRATHI BHAVAN |         4 |     2140 |
|  6 | NARMADA BHAVAN    |         6 |     3240 |
|  7 | GANGA BHAVAN      |         1 |      780 |
|  8 | YAMUNA BHAVAN     |         8 |     4580 |
+----+-------------------+-----------+----------+

Example Case 1:

Let us first add new column new_col varchar(100), SQL query for that will be

ALTER TABLE hostel_list_archive ADD new_col varchar(100) default 'TEST VAL';

+----+-------------------+-----------+----------+----------+
| id | name              | occupancy | capacity | new_col  |
+----+-------------------+-----------+----------+----------+
|  1 | ASHOK BHAVAN      |         2 |     1090 | TEST VAL |
|  2 | KAILASH BHAVAN    |         2 |      990 | TEST VAL |
|  3 | RUDRAKSH BHAVAN   |         2 |      990 | TEST VAL |
|  4 | KAVERI BHAVAN     |         1 |      690 | TEST VAL |
|  5 | BHAGIRATHI BHAVAN |         4 |     2140 | TEST VAL |
|  6 | NARMADA BHAVAN    |         6 |     3240 | TEST VAL |
|  7 | GANGA BHAVAN      |         1 |      780 | TEST VAL |
|  8 | YAMUNA BHAVAN     |         8 |     4580 | TEST VAL |
+----+-------------------+-----------+----------+----------+

Example Case 2:

Now let us see how to drop this new_col column, SQL Query for that will be

ALTER TABLE hostel_list_Archive DROP new_col;

+----+-------------------+-----------+----------+
| id | name              | occupancy | capacity |
+----+-------------------+-----------+----------+
|  1 | ASHOK BHAVAN      |         2 |     1090 |
|  2 | KAILASH BHAVAN    |         2 |      990 |
|  3 | RUDRAKSH BHAVAN   |         2 |      990 |
|  4 | KAVERI BHAVAN     |         1 |      690 |
|  5 | BHAGIRATHI BHAVAN |         4 |     2140 |
|  6 | NARMADA BHAVAN    |         6 |     3240 |
|  7 | GANGA BHAVAN      |         1 |      780 |
|  8 | YAMUNA BHAVAN     |         8 |     4580 |
+----+-------------------+-----------+----------+

Example Case 3:

Now let us see how we can update an existing column, SQL query will be

ALTER TABLE hostel_list_archive MODIFY COLUMN name varchar(300);

+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(2)       | NO   |     | 0       |       |
| name       | varchar(300) | YES  |     | NULL    |       |
| occupancy  | tinyint(1)   | YES  |     | NULL    |       |
| capacity   | int(5)       | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+