🔧 SQL ALTER TABLE Statement – Modify Your Table Structure Easily

Need to add a column, change a datatype, rename a column, or drop a constraint? The SQL ALTER TABLE command lets you update your database table structure on the fly without deleting the existing data.

In this guide, you’ll learn how to use ALTER TABLE to manage schema changes efficiently with real-world examples.

📘 What is SQL ALTER TABLE?

The ALTER TABLE statement allows you to modify the structure of an existing table. You can:

  • Add, modify, rename, or delete columns
  • Add or drop constraints
  • Rename the table itself

It’s a powerful tool for evolving your database schema without losing any existing data.

🧾 SQL ALTER TABLE Syntax

➕ Add a Column:

ALTER TABLE employees
ADD COLUMN job_title VARCHAR(50);

✏️ Modify a Column:

Note: The syntax varies by RDBMS.

-- In PostgreSQL / SQL Server
ALTER TABLE employees
ALTER COLUMN salary TYPE DECIMAL(10,2);

-- In MySQL
ALTER TABLE employees
MODIFY salary DECIMAL(10,2);

🗑️ Drop a Column:

ALTER TABLE employees
DROP COLUMN job_title;

🔁 Rename a Column (PostgreSQL example):

ALTER TABLE employees
RENAME COLUMN emp_name TO employee_name;

🏷️ Rename a Table:

ALTER TABLE employees
RENAME TO staff;

🔐 Managing Constraints Using ALTER TABLE

Add a Constraint:

ALTER TABLE employees
ADD CONSTRAINT chk_age CHECK (age >= 18);

Drop a Constraint:

ALTER TABLE employees
DROP CONSTRAINT chk_age;

🎯 Real-World Examples

ActionSQL Example
Add a created_at timestampADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Change email lengthMODIFY email VARCHAR(150)
Remove a no longer used columnDROP COLUMN middle_name
Add foreign key to another tableADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)

🧠 Best Practices

  • Always backup your database before applying structural changes.
  • Use ALTER TABLE during maintenance windows in production environments.
  • Test changes on a staging database to avoid unexpected issues.
  • Be cautious with dropping columns/constraints, as this can lead to data loss or broken relationships.

📝 Summary

  • ALTER TABLE lets you change table structure without affecting existing data.
  • You can add, delete, rename, or change columns and constraints.
  • It’s supported by all major RDBMSs including MySQL, PostgreSQL, SQL Server, and Oracle.
  • Always test before altering production tables.