SQL ALTER TABLE statement

Here is a clear, practical, and complete guide to the SQL ALTER TABLE statement, including examples for MySQL, PostgreSQL, SQL Server, and Oracle.

 What SQL ALTER TABLE statement Does

ALTER TABLE modifies the structure of an existing table.
You can:

• Add columns

• Modify columns

• Drop columns

• Rename columns

• Add or drop constraints

• Rename the table

 Basic Syntax

1. ADD COLUMN

Add multiple columns (MySQL/PostgreSQL):

2. DROP COLUMN

SQL Server requires:

(Same syntax—but SQL Server cannot drop columns involved in constraints unless removed first.)

3. MODIFY / ALTER COLUMN

MySQL:

PostgreSQL:

SQL Server:

4. RENAME COLUMN

PostgreSQL:

SQL Server:

MySQL:

5. ADD CONSTRAINT

Primary key:

Unique:

Foreign key:

 6. DROP CONSTRAINT

PostgreSQL:

SQL Server:

MySQL:

(In MySQL, UNIQUE constraints are indexes.)

7. RENAME TABLE

MySQL / PostgreSQL:

SQL Server:

 8. SET DEFAULT / DROP DEFAULT

Add default:

Drop default:

(SQL syntax varies slightly across engines.)

 Important Notes & Pitfalls

•  ALTER TABLE locks the table in many databases (be careful in production)
•  Always back up before major schema changes
•  Dropping columns removes all stored data in that column
•  Changing column types can fail if existing data is incompatible
•  Foreign key changes may require dropping/recreating constraints

 Best Practices

•  Apply changes in small, safe migrations
•  Avoid long ALTER operations on huge tables during peak hours
•  Use CHECK, UNIQUE, and FOREIGN KEY constraints for data integrity
•  Use meaningful constraint names (e.g., fk_orders_userid)
• Test ALTER statements on staging before production