ALTER TABLE Statement in SQL

The ALTER TABLE statement in SQL is used to modify an existing table’s structure. It allows you to add, delete, or modify columns, as well as change constraints and other properties of a table.

Syntax for ALTER TABLE

ALTER TABLE table_name
ADD/MODIFY/DROP COLUMN column_name data_type;

table_name: The name of the table to be altered.
ADD: Adds a new column to the table.
MODIFY or ALTER COLUMN: Changes an existing column's data type, default value, or other properties.
DROP COLUMN: Removes a column from the table.

Basic Examples

1. Adding a New Column

ALTER TABLE Students
ADD Email VARCHAR(100);

This adds a new column Email to the Students table.

2. Modifying a Column's Data Type

ALTER TABLE Students
MODIFY Age SMALLINT;

This changes the Age column’s data type to SMALLINT.

3. Renaming a Column (MySQL, PostgreSQL, SQL Server)

ALTER TABLE Students
RENAME COLUMN Name TO FullName;

This renames the column Name to FullName.

4. Deleting a Column

ALTER TABLE Students
DROP COLUMN Email;

This removes the Email column from the Students table.

Variants of ALTER TABLE

1. Adding a New Constraint

ALTER TABLE Students
ADD CONSTRAINT unique_email UNIQUE (Email);

This ensures that all values in the Email column are unique.

2. Removing a Constraint

ALTER TABLE Students
DROP CONSTRAINT unique_email;

Removes the unique constraint on the Email column.

3. Changing a Column’s Default Value

ALTER TABLE Students
ALTER COLUMN Age SET DEFAULT 18;

This sets the default Age to 18 if no value is provided.

4. Renaming a Table

ALTER TABLE Students
RENAME TO StudentRecords;

This changes the table name from Students to StudentRecords.

Explaining the Statement

How ALTER Works

Identifies the table to be modified.
Performs the specified modification (add, delete, modify, etc.).
Applies changes without affecting existing data (except when dropping a column).

What Happens Behind the Scenes?

Some databases lock the table during the operation to prevent conflicts.
If a column is dropped, all data in that column is lost permanently.
Renaming a column does not change existing data, only its label.

Common Errors and Solutions

Error	Cause	Solution
Column doesn’t exist	The column you’re modifying doesn’t exist.	Check the column name before modifying.
Data type conversion error	The new data type is incompatible with old data.	Ensure data type changes are compatible.
Constraint violation	Dropping a column that is part of a foreign key.	Remove the constraint before dropping the column.

SQL Representation

Create Table

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Age INT,
    Class VARCHAR(10)
);

Modify Table

ALTER TABLE Students
ADD Email VARCHAR(100);

This adds an Email column to the Students table.

Rename a Column

ALTER TABLE Students
RENAME COLUMN Name TO FullName;

Changes Name to FullName.

Drop a Column

ALTER TABLE Students
DROP COLUMN Email;

Removes the Email column.

Key Points

Use ALTER TABLE to change the structure of a table without losing data (except when dropping a column).
Always check for dependent constraints before dropping a column.
Test changes in a development database first to avoid breaking live systems.
Some operations may lock the table temporarily, so use with caution in production databases.