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 table_name
ADD (Column1  data_type,
Column2  data_type,
       …       
ColumnN  data_type); 

ALTER TABLE table_name
 DROP COLUMN columnN; 

ALTER TABLE table_name
 MODIFY column_name column_type; 

ALTER TABLE table_name
 ALTER COLUMN columnN column_type; 

ALTER TABLE table_name MODIFY COLUMN columnN data_type; 

ALTER TABLE table_name MODIFY columnN data_type NOT NULL; 

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

ALTER TABLE table_name
ADD CONSTRAINT UniqueConstraint CHECK (CONDITION); 

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

ALTER TABLE table_name
DROP CONSTRAINT UniqueConstraint; 

ALTER TABLE table_name
DROP CONSTRAINT PrimaryKey; 

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

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-

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 –

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 –

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 –

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

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 –