CodeCapsule

SQL ALTER TABLE statement

by

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

✅ What ALTER TABLE 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

ALTER TABLE table_name action;

🟦 1. ADD COLUMN

ALTER TABLE employees
ADD age INT;

Add multiple columns (MySQL/PostgreSQL):

ALTER TABLE employees
ADD address VARCHAR(200),
ADD hire_date DATE;

🟧 2. DROP COLUMN

ALTER TABLE employees
DROP COLUMN age;

SQL Server requires:

ALTER TABLE employees
DROP COLUMN age;

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

🟪 3. MODIFY / ALTER COLUMN

MySQL:

ALTER TABLE employees
MODIFY salary DECIMAL(12,2) NOT NULL;

PostgreSQL:

ALTER TABLE employees
ALTER COLUMN salary TYPE numeric(12,2);

SQL Server:

ALTER TABLE employees
ALTER COLUMN salary DECIMAL(12,2) NOT NULL;

🟨 4. RENAME COLUMN

PostgreSQL:

ALTER TABLE employees
RENAME COLUMN name TO full_name;

SQL Server:

EXEC sp_rename 'employees.name', 'full_name', 'COLUMN';

MySQL:

ALTER TABLE employees
CHANGE name full_name VARCHAR(100);

🟦 5. ADD CONSTRAINT

Primary key:

ALTER TABLE users
ADD CONSTRAINT pk_users PRIMARY KEY (id);

Unique:

ALTER TABLE users
ADD CONSTRAINT uq_email UNIQUE (email);

Foreign key:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id);

🟧 6. DROP CONSTRAINT

PostgreSQL:

ALTER TABLE users
DROP CONSTRAINT uq_email;

SQL Server:

ALTER TABLE users
DROP CONSTRAINT uq_email;

MySQL:

ALTER TABLE users
DROP INDEX uq_email;

(In MySQL, UNIQUE constraints are indexes.)

🟨 7. RENAME TABLE

MySQL / PostgreSQL:

ALTER TABLE old_name RENAME TO new_name;

SQL Server:

EXEC sp_rename 'old_name', 'new_name';

🔥 8. SET DEFAULT / DROP DEFAULT

Add default:

ALTER TABLE employees
ALTER COLUMN status SET DEFAULT 'active';

Drop default:

ALTER TABLE employees
ALTER COLUMN status 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

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *