SQL ALTER TABLE Query

Published : 14 February 2024
Updated : 01 August 2025

130 Views

In SQL, ALTER TABLE command is a part of Data Definition Language (DDL) that allows users to modify the structure of an existing table without affecting the data within it. ALTER TABLE command is widely used in database management to perform operations like adding or deleting columns, modifying data types, renaming columns , creating or destroying indexes and more.

In this article, we will dive deep into the ALTER TABLE command. By the end, you will have a clear understanding of how to use the ALTER TABLE command effectively, including its syntax, use cases and key concepts.

Syntax

ALTER TABLE TableName <operation_type>;

Operation_type : Specifies the action to be performed on the table, such as adding a column, modifying a column's datatype or adding constraints.

Demo Database

We will use this table to demonstrate and explain the concepts within this article

CREATE TABLE Employees (
    Id INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Mobile VARCHAR(15) UNIQUE KEY,
    Address varchar(300),
    Salary DECIMAL(10, 2)
);

Use Cases of ALTER TABLE Command

You can use ALTER TABLE command for following purpose :-

Modify the structure of a table.
Add, modify or drop columns.
Rename a table or a column.
Add or drop constraints.
Manage indexes associated with a table.

Adding a new Column

There will be two type of requirement of adding a column in a table

1. Adding a new column at the end of table

ALTER TABLE TableName 
ADD ColumnName datatype [constraints];

Example :-

Let's consider a scenario where we need to add a new column named 'JoiningDate' at the end of this table mean's as a last column of table

ALTER TABLE Employees ADD JoiningDate datetime;

Verification

To verify is column added or not, run desc or select query

desc Employees;

Field	Type	        Null	    Key	    Default	   Extra
Id	int	        NO  	    PRI		           auto_increment
Name	varchar(100)	NO			
Mobile	varchar(15)	YES  	    UNI		
Address	varchar(300)	YES			
Salary	decimal(10,2)	YES			
JoiningDate  datetime	YES

you can see JoiningDate column is added at the end of table

2. Adding a new column after a existing column

If you need to add new column after an existing column. you should follow below command

ALTER TABLE TableName 
ADD COLUMN ColumnName datatype  [constraints] AFTER ExistingColumnName;

Example :-

Let's consider a scenario where we need to add a new column named 'postalCode' after Address column name

ALTER TABLE Employees 
ADD PostalCode varchar(20) AFTER Address;

Verification : To verify is column added or not, run desc or select query

desc Employees;

Field	Type	        Null	    Key	    Default	   Extra
Id	int	        NO  	    PRI		           auto_increment
Name	varchar(100)	NO			
Mobile	varchar(15)	YES  	    UNI		
Address	varchar(300)	YES	
PostalCode varchar(20)	YES				
Salary	decimal(10,2)	YES			
JoiningDate  datetime	YES

you can see PostalCode column is added after Address column

Deleting or Dropping an Existing Column

ALTER TABLE TableName 
DROP COLUMN ColumnName;

Example :-

Let's consider a scenario, where we need to delete a column. in our case dropping column PostalCode

ALTER TABLE Employees
DROP COLUMN PostalCode;

Verification :- To verify is column deleted or not, run desc or select query

desc Employees;

Field	Type	        Null	    Key	    Default	   Extra
Id	int	        NO  	    PRI		           auto_increment
Name	varchar(100)	NO			
Mobile	varchar(15)	YES  	    UNI		
Address	varchar(300)	YES			
Salary	decimal(10,2)	YES			
JoiningDate  datetime	YES

Renaming a Table

RENAME TABLE OldTableName TO NewTableName;
           OR
ALTER TABLE OldTableName RENAME TO NewTableName;

If you want to learn more details about rename a table click here

Renaming a Column

if you need to rename a column. you should follow below command

ALTER TABLE TableName 
RENAME COLUMN OldColumnName TO NewColumnName;

Example :-

Let's consider a scenario, where we need to rename a column name in our case rename column 'Address' into 'TempAddress'. our query for this will be

ALTER TABLE Employees 
RENAME COLUMN Address TO TempAddress;

Verification :- To verify is column name changed or not, run desc or select query

desc Employees;

Field	Type	        Null	    Key	    Default	   Extra
Id	int	        NO  	    PRI		           auto_increment
Name	varchar(100)	NO			
Mobile	varchar(15)	YES  	    UNI		
TempAddress varchar(300) YES			
Salary	decimal(10,2)	YES			
JoiningDate  datetime	YES

Modify/Change datatype of a column

If you need to change datatype of a column. you should follow below command syntax

ALTER TABLE TableName
ALTER COLUMN ColumnName newdatatype;

The syntax for changing datatype of a column varies across different database systems, so let's explore how to achieve this in popular RDBMS

Let's consider a scenario, where we need to change data type of a column, in our case change data type of column TempAddress from varchar to text. our query for this on different RDBMS will be

1. MySQL

In MySQL, you need to use the MODIFY keyword instead of ALTER COLUMN

ALTER TABLE TableName 
MODIFY ColumnName newdatatype;

Example :-

ALTER TABLE Employees
MODIFY TempAddress text;

2. PostgreSQL

In PostgreSQL, the syntax is almost correct but the ALTER COLUMN part needs to be followed by the TYPE keyword

ALTER TABLE TableName
ALTER COLUMN ColumnName TYPE newdatatype;

Example :-

ALTER TABLE Employees
ALTER COLUMN TempAddress TYPE text;

3. SQL Server

In SQL Server, syntax will be same

ALTER TABLE TableName
ALTER COLUMN ColumnName newdatatype;

Example :-

ALTER TABLE Employees
ALTER COLUMN TempAddress text;

4. Oracle

ALTER TABLE TableName
MODIFY (ColumnName newdatatype);

Example :-

ALTER TABLE Employees
MODIFY (TempAddress text);

Verification

To verify is column datatype changed or not, run desc query

desc Employees;

Field	Type	        Null	    Key	    Default	   Extra
Id	int	        NO  	    PRI		           auto_increment
Name	varchar(100)	NO			
Mobile	varchar(15)	YES  	    UNI		
TempAddress text        YES			
Salary	decimal(10,2)	YES			
JoiningDate  datetime	YES

you can see TempAddress column datatype is changed from varchar to text in result set

Adding a Constraint

If you need to add a constraint on a column or set of columns, your query syntax will be like

ALTER TABLE TableName 
ADD CONSTRAINT ConstraintName ConstraintDefinition

Let's explore adding different type of constraints syntax

1. Adding a Primary Key Constraint

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName PRIMARY KEY (ColumnName);

If you want to learn more about Primary Key in SQL chick here

2. Adding a Foreign Key Constraint

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName FOREIGN KEY (ColumnName)
REFERENCES ParentTableName (ParentColumnName);

If you want to learn more about Foreign Key in SQL chick here

3. Adding a Unique Key Constraint

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName UNIQUE (ColumnName);

If you want to learn more about Unique Key in SQL click here

4. Adding a Check Constraint

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName CHECK (Condition);

Dropping or Deleting a Constraint

Dropping a constraints syntax varies across different RDBMS, below is the syntax of some popular RDBMS syntax

1. In MySQL

In MySQL, you need to first identify the name of the constraint (e.g., primary key, foreign key, unique, etc.) after that based on constraint you can follow below syntax to delete constraints

1. Drop a Primary Key:

ALTER TABLE TableName
DROP PRIMARY KEY;

2. Drop a Foreign Key

ALTER TABLE TableName
DROP FOREIGN KEY ConstraintName;

3. Drop a Unique Key Constraint

ALTER TABLE TableName
DROP INDEX ConstraintName;

2. In PostgreSQL

In PostgreSQL, you can drop constraints by referencing the constraint name, below is the query syntax

ALTER TABLE TableName
DROP CONSTRAINT ConstraintName;

Same query syntax will be followed for all type of constraints like Primary Key , Foreign Key , Unique Key etc

3. In SQL Server

In SQL Server, the syntax is similar to that of PostgreSQL

ALTER TABLE TableName
DROP CONSTRAINT ConstraintName;

Same query syntax will be followed for all type of constraints like Primary Key , Foreign Key , Unique Key etc

4. In Oracle

In Oracle, the syntax for dropping constraints is also similar to SQL Server and PostgreSQL.

ALTER TABLE TableName
DROP CONSTRAINT ConstraintName;

Same query syntax will be followed for all type of constraints deletion like Primary Key , Foreign Key , Unique Key etc

Key Points

Before altering tables, create backups to avoid accidental data loss.
The ALTER TABLE command modifies the structure but preserves the existing data
Before executing alter table commands on a production database, always test them in a development or staging environment to check their impact.
Review dependencies such as views, stored procedures or triggers that may be impacted by changes.
Modifying large tables can temporarily lock the table and impact database performance.
New columns are typically added with NULL values unless a default value is specified.
Constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK can be added or dropped using ALTER TABLE.