SQL Alter Command

Introduction to SQL Alter Command

SQL (Structured Query Language) is used to perform operations on databases and tables using various SQL commands such as DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction Control Language). In DDL, we use commands such as CREATE, DROP, ALTER etc to create and modify the structure of the database or the objects in the database. The ALTER command is used to add, modify or delete the columns in a table. In this topic, we are going to learn about SQL Alter Command.

Syntax

ALTER command can be used to add columns to an existing table, drop a column from a table, rename an existing column, and change the data type of a column. Below are the syntax used for the different operations which can be performed using Alter command.

The syntax for adding a new column to an existing table is as below:

ALTER TABLE table_name ADD COLUMN column_name datatype;

The syntax for adding a column with a default value to an existing table is as below:

ALTER TABLE table_name ADD COLUMN column_name datatype DEFAULT ‘value’;

The syntax for changing the data type of an existing column as below:

ALTER TABLE table_name MODIFY column_name datatype;

The syntax for renaming a column as below:

ALTER TABLE table_name RENAME old_column_name To new_column_name;

The syntax for dropping a column as below:

ALTER TABLE table_name DROP column_name;

The syntax for adding the NOT NULL constraint to a column as below:

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

How to use SQL Alter Command with Examples?

To understand the ALTER command operations, let us consider the below table ‘EMPLOYEE’ as an example. The table ‘EMPLOYEE’ is already created and with the use of Alter command, many modifications such as adding the column, renaming a column, dropping a column etc can be done as described below.

EMPLOYEE table as below:

ID	NAME	AGE	SALARY
134	Sonal	24	23000.00
145	Nikhil	27	28990.00
167	Soham	26	25000.00
234	Yash	32	35000.00
189	Ritu	25	29000.00
190	Rajesh	28	29000.00

Adding a single column to the EMPLOYEE table as below

ALTER TABLE EMPLOYEE ADD COLUMN DOB DATE;

The above ALTER command will add the column ‘DOB’ to the existing table ‘EMPLOYEE’. Here the column ‘DOB’ will have a null value for each row in the existing table.

We can see the result by using the below statement:

Select * from EMPLOYEE;

ID	NAME	AGE	SALARY	DOB
134	Sonal	24	23000.00	null
145	Nikhil	27	28990.00	null
167	Soham	26	25000.00	null
234	Yash	32	35000.00	null
189	Ritu	25	29000.00	null
190	Rajesh	28	29000.00	null

Adding a column with the default value

Let us assume that the employees have joined on the same day, so the Date of joining column can have the same default value for all the employees.

ALTER TABLE EMPLOYEE ADD COLUMN DOJ DATE DEFAULT '1990-08-09';

The above ALTER statement will add the column ‘DOJ’ with a default value of ‘1990-08-09’ to the EMPLOYEE table. We can see the result of the above command by using the below statement:

Select * from EMPLOYEE;

ID	NAME	AGE	SALARY	DOJ
134	Sonal	24	23000.00	1990-08-09
145	Nikhil	27	28990.00	1990-08-09
167	Soham	26	25000.00	1990-08-09
234	Yash	32	35000.00	1990-08-09
189	Ritu	25	29000.00	1990-08-09
190	Rajesh	28	29000.00	1990-08-09

Renaming an existing column

By the usage of the below command, we can rename the column ‘ID’ from the existing ‘EMPLOYEE’ table to ‘EMP_ID’.

ALTER TABLE EMPLOYEE rename ID to EMP_ID;

We can see the result of the above command by the below statement:

Select * from EMPLOYEE;

EMP_ID	NAME	AGE	SALARY	DOJ
134	Sonal	24	23000.00	1990-08-09
145	Nikhil	27	28990.00	1990-08-09
167	Soham	26	25000.00	1990-08-09
234	Yash	32	35000.00	1990-08-09
189	Ritu	25	29000.00	1990-08-09
190	Rajesh	28	29000.00	1990-08-09

Dropping a column from the existing table

ALTER TABLE EMPLOYEE DROP COLUMN DOJ;

The above command will drop the column ‘DOJ’ from the ‘EMPLOYEE’ table.

We can see the results by the below statement:

Select * from EMPLOYEE;

EMP_ID	NAME	AGE	SALARY
134	Sonal	24	23000.00
145	Nikhil	27	28990.00
167	Soham	26	25000.00
234	Yash	32	35000.00
189	Ritu	25	29000.00
190	Rajesh	28	29000.00

Adding NOT NULL Constraint to a column

Here the table ‘EMPLOYEE’ is already created and if we want to add a NOT NULL constraint to the column ‘SALARY’, then we can do it as below.

ALTER TABLE EMPLOYEE MODIFY SALARY DECIMAL(18, 2) NOT NULL;

Modifying the Datatype of a column

The below statement can be used to modify the data type of an existing column as below:

ALTER TABLE EMPLOYEE MODIFY NAME CHAR(50);

Advantages

Below are the advantages of using Alter command:

• Alter command is very useful when we want to modify the database or tables contained in a database.
• With the help of Alter command, many modifications can be made if any change in designing of the database is needed without disturbing the existing database or objects related to it.
• We can add constraints to a column even after the table is created.

Conclusion

The need for the ALTER command comes in handy when we want to change or modify the existing database if any business requirement comes into the picture after the designing is completed. We can easily modify the database or the tables in it by using the Alter command. But the developers should check with the ALTER permissions provided for users while using Alter command for Database tables. Also when we use Alter command to add primary key, we should keep it in our notice that the primary key column must already have been created i.e. when the table first got created to not contain Null values.

Recommended Articles

This is a guide to SQL Alter Command. Here we discuss how to use SQL adding the Alter Commands with Examples and Advantages. You may also look at the following article to learn more –

1. SQL Management Tools
2. T-SQL String Functions
3. What is SQL Injection?
4. How to Connect Database to MySQL?