SQL ALTER TABLE

The SQL ALTER TABLE statement is used to add, delete/drop, or modify columns in an existing SQL table. It is also used to add or drop constraints on an existing table.

SQL ALTER TABLE – Add Column

The ADD statement along with the ALTER TABLE adds columns in the existing SQL table. Sometimes, we may need to add some more information to an existing table. In that case, in place of recreating the whole table again, we can use add option.

Syntax

ALTER TABLE table_name ADD 
(columnname_1 datatype, 
columnname_2 datatype, 
.....
columnname_n datatype) ;

Example

Suppose, we have an employee table in MySQL server as below.

mysql> select * from employee_salary;
+-------+---------+-----------+--------+
| empno | name    | city      | salary |
+-------+---------+-----------+--------+
|     1 | Sagar   | Kolkata   |  85000 |
|     2 | Shankar | New Delhi |  80000 |
|     3 | Kushal  | Noida     |  70000 |
|     4 | Ranjit  | Ahmedabad |  60000 |
+-------+---------+-----------+--------+
4 rows in set (0.08 sec)

Now let’s check how to add a column department in the employee table.

mysql>alter table employee add department varchar(50);
Query OK, 0 rows affected (0.62 sec)

mysql> select * from employee;
+-------+---------+-----------+--------+------------+
| empno | name    | city      | salary | department |
+-------+---------+-----------+--------+------------+
|     1 | Sagar   | Kolkata   |  85000 | NULL       |
|     2 | Shankar | New Delhi |  80000 | NULL       |
|     3 | Kushal  | Noida     |  70000 | NULL       |
|     4 | Ranjit  | Ahmedabad |  60000 | NULL       |
+-------+---------+-----------+--------+------------+
4 rows in set (0.00 sec)

Now if you want to add multiple columns at once, you can follow the below example. Here, we are adding two columns at once, age and sex.

mysql> alter table employee add (age int, sex varchar(10));
Query OK, 0 rows affected (0.62 sec)

mysql> select * from employee;
+-------+---------+-----------+--------+------------+------+------+
| empno | name    | city      | salary | department | age  | sex  |
+-------+---------+-----------+--------+------------+------+------+
|     1 | Sagar   | Kolkata   |  85000 | NULL       | NULL | NULL |
|     2 | Shankar | New Delhi |  80000 | NULL       | NULL | NULL |
|     3 | Kushal  | Noida     |  70000 | NULL       | NULL | NULL |
|     4 | Ranjit  | Ahmedabad |  60000 | NULL       | NULL | NULL |
+-------+---------+-----------+--------+------------+------+------+
4 rows in set (0.00 sec)

SQL ALTER TABLE – MODIFY

This command is used to modify an existing column in a table. Multiple columns also can be modified at once.

Syntax: (Orcale, MySQL, MariaDB)

ALTER TABLE table_name modify column_name datatype;

Syntax: (SQL Server)

ALTER TABLE table_name alter column column_name;

Example

Let’s look at an example that shows how to modify a column in MySQL table using the ALTER TABLE – MODIFY statement.

mysql> alter table employee modify department varchar(80);
Query OK, 4 rows affected (2.01 sec)

This MySQL ALTER TABLE statement will modify the column department to the datatype  of varchar(80) which was earlier varchar(50).

mysql> desc employee;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| empno      | int         | NO   |     | NULL    |       |
| name       | varchar(50) | NO   |     | NULL    |       |
| city       | varchar(50) | NO   |     | NULL    |       |
| salary     | int         | NO   |     | NULL    |       |
| department | varchar(50) | YES  |     | NULL    |       |
| age        | int         | YES  |     | NULL    |       |
| sex        | varchar(10) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.08 sec)

SQL ALTER TABLE – DROP column

The DROP table statement along with ALTER TABLE is used to drop columns from an existing SQL table. This command is useful to drop unnecessary columns from the table at a later stage.

Syntax

ALTER TABLE table_name DROP column column_name;

Example

Let’s look at an example that shows how to drop a column from a MySQL table using the ALTER TABLE – MODIFY statement.

mysql> alter table employee drop column department;
Query OK, 0 rows affected (2.15 sec)

This MySQL ALTER TABLE statement will drop the column department from the employee table.

mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| empno  | int         | NO   |     | NULL    |       |
| name   | varchar(50) | NO   |     | NULL    |       |
| city   | varchar(50) | NO   |     | NULL    |       |
| salary | int         | NO   |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
| sex    | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.07 sec)