✏️ SQL ALTER TABLE – Modify an Existing Table in SQL

The SQL ALTER TABLE statement allows you to change the structure of an existing table — add or delete columns, modify data types, rename columns, or add constraints — all without recreating the table.

If you’re managing real-world databases, schema changes are unavoidable. ALTER TABLE gives you the flexibility to adapt your database design as your project evolves.

---

📘 What is the SQL ALTER TABLE Statement?

ALTER TABLE is a Data Definition Language (DDL) command that enables you to:

• Add new columns
• Modify existing columns
• Drop (remove) columns
• Rename columns (in some RDBMS)
• Add or drop constraints (like PRIMARY KEY, FOREIGN KEY)
• Change column data types

---

🧾 SQL ALTER TABLE Syntax

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;  -- MySQL

ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_datatype;  -- PostgreSQL

---

✅ Examples of SQL ALTER TABLE

1. Add a Column

ALTER TABLE employees
ADD email VARCHAR(100);

Adds a new column email to the employees table.

---

2. Drop a Column

ALTER TABLE employees
DROP COLUMN email;

Removes the email column and all its data.

---

3. Modify Column Data Type

MySQL:

ALTER TABLE employees
MODIFY COLUMN first_name VARCHAR(150);

PostgreSQL:

ALTER TABLE employees
ALTER COLUMN first_name TYPE VARCHAR(150);

---

4. Rename a Column

(Supported in some RDBMS)

ALTER TABLE employees
RENAME COLUMN first_name TO fname;

This syntax works in PostgreSQL and some versions of MySQL (8.0+).

---

5. Add a New Constraint

ALTER TABLE employees
ADD CONSTRAINT emp_email_unique UNIQUE (email);

Adds a UNIQUE constraint to the email column.

---

6. Drop a Constraint

ALTER TABLE employees
DROP CONSTRAINT emp_email_unique;

Note: Constraint names may vary or need to be looked up via system views.

---

🔄 Real-World Use Cases

Scenario	ALTER TABLE Use
Adding a new field to capture user info	ADD column
Removing an unused or deprecated field	DROP column
Enforcing uniqueness after launch	ADD UNIQUE constraint
Increasing field length to prevent cutoff	MODIFY/ALTER column type
Fixing a naming error	RENAME COLUMN

---

🧠 Best Practices

Best Practice	Why It Matters
Backup your database before schema changes	Prevents accidental data loss
Use version control for schema changes	Tracks database changes systematically
Test ALTER statements on staging first	Avoids breaking production systems
Document all schema modifications	Improves team communication and traceability
Avoid DROP COLUMN on critical fields	Can lead to data loss if done improperly

---

📝 Summary

• ALTER TABLE lets you change the structure of a table without deleting and recreating it
• You can add, drop, rename, or modify columns, and add/remove constraints
• Syntax varies slightly across MySQL, PostgreSQL, and SQL Server
• Always test and backup before altering production tables