SQL Alter Command

Introduction to SQL Alter Command

SQL Alter command can be applied on various levels of database like the databases, the tables and the columns. As the term says, it is used to make any alteration to the database system, and so it is considered to be one of the DML (Data Manipulation Language) commands. The most commonly used Alter operations are Add, Drop, Modify, change commands, for adding, deleting or changing the contents of the table or column.

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:

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;

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;

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;

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;

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 the 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?