EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial SQL Alter Command
 

SQL Alter Command

Priya Pedamkar
Article byPriya Pedamkar

Updated July 28, 2023

SQL Alter Command

 

 

Introduction to SQL Alter Command

SQL Alter command can be applied on various levels of databases 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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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 is 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 design 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

We hope that this EDUCBA information on “SQL Alter Command” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW