Why to ALTER Table?

Let us assume a table was created as per requirement with a set of columns. Later the due to some additional requirements, the table should have some additional columns, or some columns might require to deleted or some column names needs to be changed to new columns. There are mainly two ways to fulfil the above requirements –

  1. DROP the table and recreate as per new requirements. This is possible suggestable when the table is empty. If the table is not empty, taking a backup of data, delete table, create new table, and load the data from backup is a time consuming and not suggestable always.
  2. Add/Delete/Modify the columns to the current table without touching the existing columns and corresponding data.

ALTER TABLE statement fulfils the requirement of proceeding with second option.

What is ALTER TABLE?

ALTER TABLE Statement used change the structure of the existing table by adding, deleting, or modifying the columns without modifying the data in it.

ALTER TABLE statement also used to rename the table or column of the existing table without touching the data in it.

ALTER TABLE ADD Syntax –

ALTER TABLE table_name
ADD (Column1  data_type,
Column2  data_type,
       …       
ColumnN  data_type); 

ALTER TABLE DROP Syntax -

ALTER TABLE table_name
 DROP COLUMN columnN; 

Syntax (Oracle,MySQL,MariaDB) -

ALTER TABLE table_name
 MODIFY column_name column_type; 

Syntax (SQL Server) -

ALTER TABLE table_name
 ALTER COLUMN columnN column_type; 

ALTER TABLE change the DATA TYPE of a column Syntax

ALTER TABLE table_name MODIFY COLUMN columnN data_type; 

ALTER TABLE command to add a NOT NULL constraint to a column Syntax -

ALTER TABLE table_name MODIFY columnN data_type NOT NULL; 

ALTER TABLE to ADD UNIQUE CONSTRAINT to a table Syntax -

ALTER TABLE table_name
ADD CONSTRAINT UniqueConstraint UNIQUE (column1, column2, ..., columnN); 

ALTER TABLE to ADD CHECK CONSTRAINT to a table Syntax -

ALTER TABLE table_name
ADD CONSTRAINT UniqueConstraint CHECK (CONDITION); 

ALTER TABLE to ADD PRIMARY KEY to a table Syntax -

ALTER TABLE table_name
ADD CONSTRAINT PrimaryKey PRIMARY KEY (column1, column2, ..., columnN); 

ALTER TABLE to DROP CONSTRAINT from a table Syntax -

ALTER TABLE table_name
DROP CONSTRAINT UniqueConstraint; 

ALTER TABLE to DROP PRIMARY KEY from a table Syntax -

ALTER TABLE table_name
DROP CONSTRAINT PrimaryKey; 

Example –

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary
001 Employee1 Director 2019-07-11 45000.00
002 Employee2 Director 2019-07-11 40000.00
003 Employee3 Manager Employee1 2019-07-11 27000.00
004 Employee4 Manager Employee2 2019-10-08 25000.00
005 Employee5 Analyst Employee3 2019-07-11 20000.00
006 Employee6 Analyst Employee3 2019-10-08 18000.00
007 Employee7 Clerk Employee3 2019-07-11 15000.00
008 Employee8 Salesman Employee4 2019-09-09 14000.00
009 Employee9 Salesman Employee4 2019-10-08 13000.00

Scenario – To add a column from existing table

Requirement – Add dept_id column to the employee_details table.

Query – The query is as follows -

ALTER TABLE employee_details ADD dept_id  int; 

By executing above query, we can add dept_id column to employee_details table. The output was shown as below-

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00
002 Employee2 Director 2019-07-11 40000.00
003 Employee3 Manager Employee1 2019-07-11 27000.00
004 Employee4 Manager Employee2 2019-10-08 25000.00
005 Employee5 Analyst Employee3 2019-07-11 20000.00
006 Employee6 Analyst Employee3 2019-10-08 18000.00
007 Employee7 Clerk Employee3 2019-07-11 15000.00
008 Employee8 Salesman Employee4 2019-09-09 14000.00
009 Employee9 Salesman Employee4 2019-10-08 13000.00

Scenario – Modify existing column type in the table

Requirement – Change the emp_id column type from INT to SMALLINT. The query was as follows-

  ALTER TABLE employee_details ALTER  COLUMN emp_id smallint; 

By executing above query, we can modify datatype of emp_id in employee_details table. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00
002 Employee2 Director 2019-07-11 40000.00
003 Employee3 Manager Employee1 2019-07-11 27000.00
004 Employee4 Manager Employee2 2019-10-08 25000.00
005 Employee5 Analyst Employee3 2019-07-11 20000.00
006 Employee6 Analyst Employee3 2019-10-08 18000.00
007 Employee7 Clerk Employee3 2019-07-11 15000.00
008 Employee8 Salesman Employee4 2019-09-09 14000.00
009 Employee9 Salesman Employee4 2019-10-08 13000.00

Scenario – Delete a column from table

Requirement – Delete/DROP the dept_id column from employee_details table. The query was as follows-

 ALTER TABLE employee_details DROP COLUMN "dept_Id"; 

By executing above query we can delete dept_Id column from employee_details. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary
001 Employee1 Director 2019-07-11 45000.00
002 Employee2 Director 2019-07-11 40000.00
003 Employee3 Manager Employee1 2019-07-11 27000.00
004 Employee4 Manager Employee2 2019-10-08 25000.00
005 Employee5 Analyst Employee3 2019-07-11 20000.00
006 Employee6 Analyst Employee3 2019-10-08 18000.00
007 Employee7 Clerk Employee3 2019-07-11 15000.00
008 Employee8 Salesman Employee4 2019-09-09 14000.00
009 Employee9 Salesman Employee4 2019-10-08 13000.00

Scenario – Modify an existing column with DEFAULT value

Requirement – Set the salary column default value to 15000.00 in employee_details table. The query was as below-

ALTER TABLE employee_details ALTER COLUMN salary int DEFAULT 15000; 

By executing above query, we can add salary column with default value of 15000. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary
001 Employee1 Director 2019-07-11 15000.00
002 Employee2 Director 2019-07-11 10000.00
003 Employee3 Manager Employee1 2019-07-11 15000.00
004 Employee4 Manager Employee2 2019-10-08 15000.00
005 Employee5 Analyst Employee3 2019-07-11 15000.00
006 Employee6 Analyst Employee3 2019-10-08 15000.00
007 Employee7 Clerk Employee3 2019-07-11 15000.00
008 Employee8 Salesman Employee4 2019-09-09 15000.00
009 Employee9 Salesman Employee4 2019-10-08 15000.00

Scenario – Adding NOT NULL constraint to the existing column of the table

Requirement – Set emp_id column to NOT NULL in the employee_details table. The query is as follows -

ALTER TABLE employee_details ALTER COLUMN emp_id int NOT NULL; 

By executing above query, we can add NOT NULL constraint on emp_id column in employee_details table.


Scenario – Adding PRIMARY KEY constraint on existing table

Requirement – Add primary key with the column emp_id to the employee_details table. The query is as follows -

 ALTER TABLE employee_details ADD CONSTRAINT PrimaryKey 
PRIMARY KEY (emp_id); 

By executing above query, we can add PRIMARY KEY constraint on emp_id column in employee_details table.


Scenario – Deleting existing PRIMARY KEY constraint on the table

Requirement – Delete primary key (PrimaryKey) on employee_details table. The query is as follows -

 ALTER TABLE employee_details DROP CONSTRAINT pk_emp_id; 

By executing above query, we can delete PRIMARY KEY constraint on emp_id column in employee_details table.


Scenario – Renaming the existing column name to new name

Note :- The rename works on MySQL, MariaDB, Oracle, and PostgreSQL databases.

Requirement – Rename column emp_name to employee_name in table employee_details.
The query is as follows -

ALTER TABLE employee_details RENAME emp_name TO employee_name; 

By executing above query, we can Rename emp_name TO employee_name column in a table. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary
001 Employee1 Director 2019-07-11 45000.00
002 Employee2 Director 2019-07-11 40000.00
003 Employee3 Manager Employee1 2019-07-11 27000.00
004 Employee4 Manager Employee2 2019-10-08 25000.00
005 Employee5 Analyst Employee3 2019-07-11 20000.00
006 Employee6 Analyst Employee3 2019-10-08 18000.00
007 Employee7 Clerk Employee3 2019-07-11 15000.00
008 Employee8 Salesman Employee4 2019-09-09 14000.00
009 Employee9 Salesman Employee4 2019-10-08 13000.00