SQL Command ALTER

Overview: SQL command ALTER is used to add, Modify, drop or delete columns in an existing SQL table.

Full statement or keyword used is called ALTER TABLE statement with others keyword to identify what task need to perform on SQL table.

The ALTER TABLE command also adds and deletes various constraints in a table.

Lets first understand the syntax for Alter Table

Syntax:

Syntax to add a Add Column in an existing SQL table:

ALTER TABLE table_name 
ADD column_name

Syntax to modify column in a SQL table:

ALTER TABLE table_name 
MODIFY COLUMN column_name datatype;

Syntax to DROP COLUMN in an existing SQL table:

ALTER TABLE table_name 
DROP COLUMN column_name;

Syntax to DROP PRIMARY KEY constraint from SQL table

ALTER TABLE table_name
DROP CONSTRAINT PrimaryKey;

Lets take few examples to understand it more based on below Employee_info table

Example #1: SQL ALTER TABLE to ADD column

ALTER TABLE Employee_info

ADD SEX VARCHAR(10);

then after this command new column is added with name SEX

SELECT * FROM Employee_info

Output:

Example #2: SQL ALTER datatype of column

Let use modify keyword with ALTER TABLE command to modify or ALTER the datatype of column. Here we are going to convert VARCHAR() into Char() datatype

ALTER TABLE Employee_info 
MODIFY COLUMN SEX Char(1);

Now the table datatype for SEX column will take only one word like “M” for male and “F” for female.

Example #3: Drop column using SQL command Alter

Lets take example to drop column name Age from Employee_info table. below is the query which can be used to drop column using SQL command ALTER Table.

ALTER TABLE Employee_info
DROP COLUMN Age;

Output:

Conclusion:

Hope this tutorial helpful for you to learned how to use ALTER TABLE to add, modify and drop a column. Careful to use Alter table because when you will perform a query then it cannot be revert and you need to create deleted column again. This is powerful query which is used mostly in day to day SQL activities.