✏️ SQL ALTER TABLE – Add, Drop, Modify Columns and Constraints

🧲 Introduction – What is SQL ALTER TABLE?

The ALTER TABLE statement in SQL is used to modify the structure of an existing table. You can add, modify, or drop columns and constraints without recreating the table.

🎯 In this guide, you’ll learn:

• How to add, drop, and modify columns
• How to add constraints like PRIMARY KEY or FOREIGN KEY
• Practical examples for real-world schema changes

---

✅ 1. Add a New Column

ALTER TABLE customers
ADD phone VARCHAR(20);

✅ Adds a new column phone to the customers table.

---

🔧 2. Modify an Existing Column

ALTER TABLE employees
MODIFY salary DECIMAL(12,2);
-- or in some DBMS:
ALTER TABLE employees
ALTER COLUMN salary TYPE DECIMAL(12,2);

✅ Updates the data type or size of an existing column.

---

🗑️ 3. Drop a Column

ALTER TABLE products
DROP COLUMN discount;

✅ Removes a column completely (and its data).

---

🔐 4. Add Constraints

-- Add PRIMARY KEY
ALTER TABLE orders
ADD PRIMARY KEY (order_id);

-- Add FOREIGN KEY
ALTER TABLE orders
ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id)
REFERENCES customers(id);

✅ Enforces data integrity between tables.

---

🧹 5. Drop Constraints

ALTER TABLE orders
DROP CONSTRAINT fk_customer;

✅ Removes named constraints. Syntax may vary per DBMS.

---

🧩 6. Rename a Column or Table

-- Rename column
ALTER TABLE employees
RENAME COLUMN lname TO last_name;

-- Rename table
ALTER TABLE users RENAME TO app_users;

✅ Not supported uniformly—check DBMS-specific syntax.

---

📘 Best Practices

✅ Recommended	❌ Avoid This
Test on development before production	Altering critical tables without a plan
Backup table before altering structure	Forgetting dependent constraints/indexes
Use clear constraint names	Letting DBMS assign default names

---

📌 Summary – Recap & Next Steps

ALTER TABLE gives you flexibility to evolve your schema as requirements change. It’s crucial for agile development and long-term maintenance.

🔍 Key Takeaways:

• Use ADD, MODIFY, DROP to change columns
• Manage constraints like PRIMARY KEY, FOREIGN KEY
• Syntax varies slightly between DBMSs

⚙️ Real-World Relevance:
Used in schema migrations, feature rollouts, maintenance scripts, and emergency fixes.

➡️ Next: Learn DROP TABLE to remove tables, or TRUNCATE to wipe data while keeping structure.

---

❓ FAQ – SQL ALTER TABLE

❓ Can I alter a column’s name?

✅ Yes. Use RENAME COLUMN (if supported).

❓ Can I add multiple columns in one command?

✅ Yes. Use a comma-separated list:

ALTER TABLE customers
ADD (city VARCHAR(50), state VARCHAR(50));

❓ Does ALTER TABLE lock the table?

✅ Often yes, temporarily—depending on DBMS and operation.

❓ How is ALTER TABLE different from CREATE TABLE?

✅ CREATE TABLE creates a new table; ALTER TABLE changes an existing one.

---

Share Now :