ALTER TABLE

Databases are rarely static. As applications grow, business requirements change, and new features are introduced, database structures must evolve as well.

Imagine a student management system that initially stores only student names and ages. Later, the organization decides to store email addresses, phone numbers, and enrollment dates. Recreating the entire table would be inefficient and could result in data loss.

To solve this problem, SQL provides the ALTER TABLE statement.

The ALTER TABLE statement allows developers and database administrators to modify the structure of an existing table without deleting the table or losing its data. It is one of the most commonly used SQL commands in real-world database management because databases continuously evolve throughout the life of an application.

In this lesson, you will learn how ALTER TABLE works, why it is important, how to add columns, modify columns, remove columns, rename columns, and follow best practices for managing database structures safely.

What is ALTER TABLE?

The ALTER TABLE statement is used to change the structure of an existing table.

Unlike CREATE TABLE, which creates a new table, ALTER TABLE modifies a table that already exists.

Using ALTER TABLE, you can:

• Add new columns
• Remove existing columns
• Rename columns
• Change data types
• Add constraints
• Remove constraints
• Modify table properties

Example:

This command adds a new Email column to the Students table without affecting existing data.

Why is ALTER TABLE Important?

Business requirements change frequently.

Consider a simple student table:

Initially, this may be sufficient.

Later, the organization may require:

• Email addresses
• Phone numbers
• Enrollment dates
• Address information

Instead of recreating the table, ALTER TABLE allows modifications while preserving existing records.

Benefits include:

• Flexibility
• Easier maintenance
• Reduced downtime
• Preservation of data
• Faster development

Basic ALTER TABLE Syntax

The general syntax is:

Example:

Here:

• ALTER TABLE specifies the table modification command.
• Students is the table name.
• ADD Email VARCHAR(255) defines the change.

Adding a New Column

One of the most common uses of ALTER TABLE is adding new columns.

Example:

Before:

After:

The new column is added, and existing rows receive NULL values until data is provided.

Adding Multiple Columns

Some database systems allow multiple columns to be added at once.

Example:

This adds two new columns simultaneously.

Modifying a Column

Sometimes a column's data type needs to change.

Example:

This increases the maximum length of the Name column.

Before:

After:

This is useful when business requirements change.

Changing Column Data Types

Suppose a table stores salary values as integers:

Later, decimal precision becomes necessary.

Example:

Now salaries can store values such as:

Renaming a Column

Column names sometimes need improvement.

Example:

Before:

Rename:

After:

This improves readability and consistency.

Dropping a Column

If a column is no longer needed, it can be removed.

Example:

Before:

After:

The Address column is permanently removed.

Adding Constraints

Constraints can be added after table creation.

Example:

This creates a primary key for the table.

Adding a UNIQUE Constraint

Example:

Now duplicate email addresses are not allowed.

Adding a FOREIGN KEY

Suppose two tables exist:

Create a relationship:

This ensures referential integrity.

Removing Constraints

Constraints can also be removed.

Example:

This removes the primary key constraint.

Real-World Example

Consider an e-commerce application.

Initial table:

New requirements:

• Product Price
• Product Description
• Stock Quantity

Update table:

The application evolves without losing existing product records.

Common Errors

Table Does Not Exist

Wrong:

If Student table does not exist:

Always verify table names.

Duplicate Column Name

Wrong:

If Name already exists, SQL generates an error.

Invalid Data Type Conversion

Example:

Converting salary values into dates may fail.

Always ensure compatibility.

Dropping Important Columns

Removing a column permanently deletes its data.

Example:

All email information is lost.

Backup important data first.

Best Practices

Backup Before Major Changes

Always create backups before altering production tables.

Test Changes in Development

Apply modifications in a test environment before production deployment.

Use Meaningful Column Names

Choose descriptive names.

Example:

Avoid Unnecessary Changes

Frequent structural changes may complicate maintenance.

Document Modifications

Record table changes for future reference.

Interview Questions

What is ALTER TABLE used for?

ALTER TABLE modifies the structure of an existing table.

Can ALTER TABLE add new columns?

Yes.

Example:

Can ALTER TABLE remove columns?

Yes.

Example:

Why is ALTER TABLE important?

It allows databases to evolve without recreating tables or losing existing data.

Summary

The ALTER TABLE statement is one of the most powerful SQL commands for managing and modifying existing database structures. It allows developers to adapt databases to changing business requirements without rebuilding tables from scratch.

In this lesson, you learned:

• What ALTER TABLE is
• Why it is important
• Adding columns
• Modifying columns
• Renaming columns
• Dropping columns
• Adding constraints
• Removing constraints
• Best practices
• Common mistakes

Understanding ALTER TABLE is essential for maintaining and evolving professional database systems.

Next Step

Continue to the next lesson:

Rename Table →