The ALTER TABLE command is used to add, delete, or modify columns in an existing table.

Adding Columns

---

The following SQL query adds an Class column to the "Student" table

Example 1 :

 

alter table Student 
add (Class varchar(10) not null
);
         
                         

copy

Example 2:

 

alter table Student
add Weight decimal(3,1) not null
after Percentage;
           
                           

copy

The above example 2 will add a column called Weight to the Student table. It will be created as a not null column and will appear after the Percentage field in the table.

Modifying Columns

---

It can change column data type, size, default value, order of column.
To change the data type of a column "Percentage" in the Student table, use the following query:

 

 alter table Student 
 modify Percentage int;
 
          
                          

copy

Order of Column

---

Using 'ALTER TABLE' statement you can reorder the columns in a table.

 

alter table Student
modify Weight decimal(3,1) not null
after Class;
        
                        

copy

This ALTER TABLE example will modify two columns to the Student table - Weight and Class.

Changing a Column Name

---

When can change the name of one of our column using the alter command with the rename column clause
To change the column name,we will write:

ALTER TABLE 
CHANGE [COLUMN] old_col_name new_col_name colum_definition
                    

Lets us understand by an example

Write an SQL statement to change the existing column name "First_name" of table student to "Name"

ALTER TABLE
CHANGE[COLUMN] First_name Name varchar(30);
                            

copy

Removing table Components

---

If you want to remove a components of a table,then you have to use DROP clause with ALTER command
To remove the components of a table,we will write:

ALTER TABLE 
DROP PRIMARY KEY,DROP FOREIGN KEY column_name,DROP COLUMN column_name
                    

Lets us understand by an example

Write an SQL statement to drop the primary key of EMP table

ALTER TABLE EMP
DROP primary key CASCADE;
                            

copy

This CASCADE option also removes any foreign keys that reference primary key