SQL ALTER TABLE

The ALTER TABLE statement is used to modify the structure of an existing table without losing the data already stored in it. After a table is created, there will often be a need to make changes — such as adding a new column, removing an old one, changing a data type, or renaming the table itself.

Think of ALTER TABLE like renovating a room. The furniture (data) stays in place, but the layout (structure) can be changed — adding a new shelf (column), removing an old one, or renaming the room (table).

The Reference Table

All examples will modify the following Students table:

1. ADD — Adding a New Column

The ADD keyword adds a new column to an existing table. New rows will have NULL in the new column unless a DEFAULT value is specified.

Syntax

ALTER TABLE table_name
ADD column_name datatype [constraints];

Example: Add an Email Column

ALTER TABLE Students
ADD Email VARCHAR(100);

After ALTER:

Existing rows get NULL in the new Email column until values are updated.

Add Column With a Default Value

ALTER TABLE Students
ADD Status VARCHAR(10) DEFAULT 'Active';

Existing rows will have 'Active' in the Status column automatically.

Add Column at a Specific Position

-- Add column after a specific column (MySQL)
ALTER TABLE Students
ADD Phone VARCHAR(15) AFTER City;

-- Add column at the very beginning
ALTER TABLE Students
ADD RegistrationNo INT FIRST;

2. DROP COLUMN — Removing a Column

The DROP COLUMN keyword removes a column and all its data permanently from the table.

Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

Example: Remove the Email Column

ALTER TABLE Students
DROP COLUMN Email;

Warning: Dropping a column deletes all the data in that column permanently. This action cannot be undone.

3. MODIFY — Changing a Column's Data Type or Constraints

The MODIFY keyword changes the data type, size, or constraints of an existing column without renaming it.

Syntax (MySQL)

ALTER TABLE table_name
MODIFY column_name new_datatype [new_constraints];

Example: Change Age from INT to TINYINT

ALTER TABLE Students
MODIFY Age TINYINT NOT NULL;

Example: Increase the Size of StudentName

ALTER TABLE Students
MODIFY StudentName VARCHAR(100);

The column name remains the same, but the maximum length is increased from 50 to 100 characters.

4. RENAME COLUMN — Renaming a Column

The column name can be changed using RENAME COLUMN (MySQL 8.0+) or CHANGE (older MySQL versions).

Using RENAME COLUMN (MySQL 8.0+)

ALTER TABLE Students
RENAME COLUMN StudentName TO FullName;

Using CHANGE (Works in all MySQL versions)

ALTER TABLE Students
CHANGE StudentName FullName VARCHAR(50);

With CHANGE, the column's data type must also be specified even if it is not being changed.

5. RENAME TABLE — Renaming the Table

ALTER TABLE Students
RENAME TO SchoolStudents;

This renames the table from Students to SchoolStudents. All existing data and structure are preserved.

6. ADD CONSTRAINT — Adding Constraints to Existing Columns

Add a NOT NULL constraint

ALTER TABLE Students
MODIFY City VARCHAR(30) NOT NULL;

Add a UNIQUE constraint

ALTER TABLE Students
ADD CONSTRAINT uq_email UNIQUE (Email);

Add a FOREIGN KEY

ALTER TABLE Students
ADD CONSTRAINT fk_course
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID);

Add a CHECK constraint

ALTER TABLE Students
ADD CONSTRAINT chk_age CHECK (Age >= 16 AND Age <= 60);

7. DROP CONSTRAINT — Removing Constraints

-- Drop a FOREIGN KEY
ALTER TABLE Students
DROP FOREIGN KEY fk_course;

-- Drop a UNIQUE constraint
ALTER TABLE Students
DROP INDEX uq_email;

-- Drop the PRIMARY KEY
ALTER TABLE Students
DROP PRIMARY KEY;

Multiple Alterations in One Statement

Multiple changes can be made to a table in a single ALTER TABLE statement by separating them with commas:

ALTER TABLE Students
ADD Email VARCHAR(100),
ADD Phone VARCHAR(15),
MODIFY Age TINYINT,
DROP COLUMN City;

This adds two columns, modifies one, and removes one — all in a single operation. This is more efficient than running four separate ALTER TABLE statements.

ALTER TABLE Command Summary

Key Points to Remember

• ALTER TABLE changes the structure of a table — not the data rows themselves (unless a column is dropped).
• Dropping a column with DROP COLUMN permanently removes that column and all its data.
• When using CHANGE to rename a column, the full column definition (data type) must be respecified.
• Multiple modifications can be combined in a single ALTER TABLE statement.
• Always test schema changes on a non-production copy of the database first.

Summary

ALTER TABLE is the command used to modify the structure of an existing table — adding, removing, or changing columns, renaming tables, and managing constraints. It is one of the most commonly used DDL (Data Definition Language) commands in database management. It allows database schemas to evolve over time without needing to recreate tables from scratch.