SQL - ALTER TABLE Command

The SQL ALTER TABLE command is used to modify a table s structure by adding, deleting columns in an existing table. You can also use the ALTER TABLE command to add and drop various constraints on an existing table.

Since this command modifies the structure of a table, it is a part of Data Definition Language. This is also where the ALTER TABLE command differs from UPDATE command; while ALTER TABLE interacts with the structure of a table to modify it, UPDATE only interacts with the data present in the table without disturbing its structure.

Syntax

Following is the basic syntax of an ALTER TABLE command −

ALTER TABLE table_name [alter_option ...];

Where, the alter_option depends on the type of operation to be performed on a table.

Adding a new column

If you need to add a new column to a table you should use the ADD COLUMN option to ALTER TABLE statement as shown below −

ALTER TABLE table_name ADD column_name datatype;

Example

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is the example to ADD a New Column to an existing table −

ALTER TABLE CUSTOMERS ADD SEX char(1);

Output

Executing the query above will produce the following output −

(0 rows affected)

Verification

To verify whether the CUSTOMERS table is altered by adding a new column SEX, use the SELECT statement to retrieve the records of the table −

SELECT * FROM CUSTOMERS;

Now, the CUSTOMERS table will be displayed as −

+----+---------+-----+-----------+----------+------+
| ID | NAME    | AGE | ADDRESS   | SALARY   | SEX  |
+----+---------+-----+-----------+----------+------+
|  1 | Ramesh  |  32 | Ahmedabad |  2000.00 | NULL |
|  2 | Ramesh  |  25 | Delhi     |  1500.00 | NULL |
|  3 | kaushik |  23 | Kota      |  2000.00 | NULL |
|  4 | kaushik |  25 | Mumbai    |  6500.00 | NULL |
|  5 | Hardik  |  27 | Bhopal    |  8500.00 | NULL |
|  6 | Komal   |  22 | MP        |  4500.00 | NULL |
|  7 | Muffy   |  24 | Indore    | 10000.00 | NULL |
+----+---------+-----+-----------+----------+------+

Adding a column after an existing column

If you want to add a column before or after an existing column you need to use AFTER followed by the name of the existing column (after the column definition).

ALTER TABLE table_name ADD COLUMN (column_name column_definition...) AFTER existing_column

Example

Following query adds a column named address after the Location.

ALTER TABLE Employee ADD COLUMN Address VARCHAR(50) AFTER Location;

Output

The output will be displayed as −

(0 rows affected)

Verification

To verify the above query if you describe the table using the EXEC sp_help command you can observe the created column in the field pst as shown below −

EXEC sp_help employee;

Now, the structure of the newly altered table will be displayed as shown below −

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| Name     | varchar(255) | YES  |     | NULL    |       |
| Salary   | int          | NO   |     | NULL    |       |
| Location | varchar(255) | YES  |     | NULL    |       |
| Address  | varchar(50)  | YES  |     | NULL    |       |
| Phone    | int          | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.15 sec)

Adding a column at start

You can add the new column at the start using the FIRST statement along with alter, following is the syntax to do so −

ALTER TABLE table_name ADD COLUMN (column_name column_definition...) FIRST

Example

Following query adds a column with name ID at start −

ALTER TABLE Employee ADD COLUMN ID INT FIRST;

Output

The output will be displayed as −

(0 rows affected)

Verification

To verify the above query if you describe the table using the EXEC sp_help command you can observe the created column in the field pst as shown below −

EXEC sp_help employee;

Now, the structure of the newly altered table will be displayed as shown below −

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| ID       | int          | YES  |     | NULL    |       |
| Name     | varchar(255) | YES  |     | NULL    |       |
| Salary   | int          | NO   |     | NULL    |       |
| Location | varchar(255) | YES  |     | NULL    |       |
| Address  | varchar(50)  | YES  |     | NULL    |       |
| Phone    | int          | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.04 sec)

Adding an index

You can add index to an existing column of a table using the ADD INDEX statement along with the ALTER statement −

ALTER TABLE table_name ADD INDEX index_name [index_type] (key_part,...) [index_option] .

Example

Following query adds an index on the columns Name and Salary −

ALTER TABLE Employee ADD INDEX sample_index (Name, Salary);

Output

The output will be displayed as −

(0 rows affected)

Verification

You can verify the indices of the table using the EXEC sp_helpindex statement as shown. It is the SQL server equivalent of SHOW INDEX statement in MySQL.

EXEC sp_helpindex Employee;

Now, the structure of modified indexes in a table will be displayed as shown below −

+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinapty | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employee | 1          | sample_index | 1            | Name        | A         | 0           | NULL     | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| employee | 1          | sample_index | 2            | Salary      | A         | 0           | NULL     | NULL   |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.32 sec)

Adding a primary key

Following is the syntax of adding a primary key on an existing table of a database −

ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

Example

ALTER TABLE Employee ADD COLUMN ID INT FIRST;

This will produce the following output −

(0 rows affected)

Following query creates a primary key on the column ID −

ALTER TABLE Employee ADD CONSTRAINT MyPrimaryKey PRIMARY KEY(ID);

Output

The output will be displayed as −

(0 rows affected)

Verification

To verify the above query if you describe the table using the EXEC sp_help command you can observe PRI against the ID field under the column Key −

EXEC sp_help employee;

The altered structure of a table will be displayed −

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| ID       | int          | NO   | PRI | NULL    |       |
| Name     | varchar(255) | YES  | MUL | NULL    |       |
| Salary   | int          | NO   |     | NULL    |       |
| Location | varchar(255) | YES  |     | NULL    |       |
| Address  | varchar(50)  | YES  |     | NULL    |       |
| Phone    | int          | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Adding a foreign key

Following is the syntax of creating a foreign key on an existing table of a database −

ALTER TABLE table_name ADD CONSTRAINT key FORIEGN KEY (column_name);

Example

Assume we have created another table named test as shown below −

CREATE table Test (ID int PRIMARY KEY);

Let us create a column named ID in the table Employee −

ALTER TABLE Employee ADD COLUMN ID INT FIRST;

(0 rows affected)

Following query creates a primary key on the column ID −

ALTER TABLE Employee ADD CONSTRAINT fk FOREIGN KEY(ID) REFERENCES test(ID);

Output

The output will be displayed as −

(0 rows affected)

Adding a constraint

Following is the syntax to add constraint to a column of an existing table −

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(column1, column2...);

Example

Following query adds UNIQUE constraint to the table Employee −

ALTER TABLE Employee ADD CONSTRAINT con UNIQUE(Phone);

Output

The output will be displayed as −

(0 rows affected)

Dropping a column

You can drop an existing column by using the DROP statement along with ALTER following is the syntax to do so −

ALTER TABLE table_name DROP COLUMN column_name;

Example

Following is the example to DROP sex column from the existing table.

ALTER TABLE CUSTOMERS DROP COLUMN SEX;

Output

Executing the query above will produce the following output −

(0 rows affected)

Verification

To verify whether the CUSTOMERS table is altered by adding a new column SEX, use the SELECT statement to retrieve the records of the table −

SELECT * FROM CUSTOMERS;

Now, the CUSTOMERS table is changed and following would be the output from the SELECT statement.

+----+---------+-----+-----------+----------+
| ID | NAME    | AGE | ADDRESS   | SALARY   |
+----+---------+-----+-----------+----------+
|  1 | Ramesh  |  32 | Ahmedabad |  2000.00 |
|  2 | Ramesh  |  25 | Delhi     |  1500.00 |
|  3 | kaushik |  23 | Kota      |  2000.00 |
|  4 | kaushik |  25 | Mumbai    |  6500.00 |
|  5 | Hardik  |  27 | Bhopal    |  8500.00 |
|  6 | Komal   |  22 | MP        |  4500.00 |
|  7 | Muffy   |  24 | Indore    | 10000.00 |
+----+---------+-----+-----------+----------+

Dropping an index

Following is the syntax to drop an index on a table −

ALTER TABLE table_name DROP INDEX index_name

Example

Following query drops the index created above on the table Employee −

ALTER TABLE Employee DROP INDEX sample_index;

Output

The output is displayed as −

(0 rows affected)

Dropping a Primary Key

Following is the syntax to drop a primary key −

ALTER TABLE table_name DROP PRIMARY key_name;

Example

Suppose we have created a table and added a PRIMARY KEY as shown below −

create table sample (ID INT);
alter table sample add CONSTRAINT PRIMARY KEY (ID);

To verify the above query if you describe the table using the EXEC sp_help command you can observe PRI against the ID field under the column Key −

EXEC sp_help sample;

The table structure will be displayed as −

+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| ID    | int  | NO   | PRI | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.11 sec)

Following query drops the above created Primary key −

alter table sample drop PRIMARY KEY;

Output

The output will be displayed as −

(0 rows affected)

Verification

If you describe the sample table using the EXEC sp_help statement you can observe that the primary constraint is removed −

EXEC sp_help sample;

The structure of the altered table sample will be produced −

+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| ID    | int  | NO   |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

Dropping a foreign key

Following is the syntax to drop a foreign key −

ALTER TABLE table_name DROP FOREIGN KEY key_name;

Example

Following query drops the foreign key fk of the table employee −

ALTER TABLE Employee DROP FOREIGN KEY fk;

Output

(0 rows affected)