You built a LEGO house (your database table).
Later, you decide:

• “Oops! I forgot to add a window.” ➝ Add a column
• “Hmm… I don’t need this door anymore.” ➝ Drop a column
• “This wall is too small; make it bigger!” ➝ Modify a column
• “I want to rename the house.” ➝ Rename the table

To do all this, MySQL gives you a magic tool called:

ALTER TABLE

---

1. Adding a New Column

Just like adding a new LEGO brick.

➤ Syntax:

ALTER TABLE table_name
ADD COLUMN column_name datatype;

➤ Example:

Add a new column email to students table:

ALTER TABLE students
ADD COLUMN email VARCHAR(100);

➤ Add column at a specific position:

ALTER TABLE students
ADD COLUMN middle_name VARCHAR(50) AFTER first_name;

---

2. Removing (Dropping) a Column

Removing a brick from your LEGO model.

➤ Syntax:

ALTER TABLE table_name
DROP COLUMN column_name;

➤ Example:

Remove middle_name column:

ALTER TABLE students
DROP COLUMN middle_name;

---

3. Modifying/Changing a Column

Make the LEGO brick bigger, smaller, or change its type.

➤ Syntax:

ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;

➤ Example:

Increase name size:

ALTER TABLE students
MODIFY COLUMN name VARCHAR(200);

➤ Rename a column:

ALTER TABLE students
CHANGE COLUMN full_name student_name VARCHAR(150);

---

4. Adding Constraints

Putting rules on your LEGO house, like “this window cannot be empty!”

➤ Add a NOT NULL:

ALTER TABLE students
MODIFY COLUMN email VARCHAR(100) NOT NULL;

➤ Add UNIQUE:

ALTER TABLE students
ADD CONSTRAINT unique_email UNIQUE(email);

➤ Add PRIMARY KEY:

ALTER TABLE students
ADD PRIMARY KEY(student_id);

➤ Add FOREIGN KEY:

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

---

5. Dropping Constraints

Removing the rules.

➤ Drop Unique:

ALTER TABLE students
DROP INDEX unique_email;

➤ Drop Foreign Key:

1. Find FK name:

SHOW CREATE TABLE orders;

2. Drop it:

ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

---

6. Renaming a Table

Give your LEGO house a new name.

➤ Syntax:

RENAME TABLE old_name TO new_name;

➤ Example:

RENAME TABLE students TO learners;

OR (MySQL 8+):

ALTER TABLE students RENAME TO learners;

---

7. Changing Column Order

Move a brick to another position.

➤ Example:

ALTER TABLE students
MODIFY COLUMN email VARCHAR(100) AFTER name;

---

Full Example in One Go

Let’s upgrade the employees table:

ALTER TABLE employees
ADD COLUMN age INT,
MODIFY COLUMN name VARCHAR(150),
ADD COLUMN join_date DATE AFTER age,
CHANGE COLUMN salary monthly_salary DECIMAL(10,2),
DROP COLUMN temporary_field;

---

PRACTICE QUESTIONS (with Solutions)

1. Add a column phone (VARCHAR 15) to customers table.

Answer:

ALTER TABLE customers
ADD COLUMN phone VARCHAR(15);

---

2. Rename column dob to birth_date in students table.

Answer:

ALTER TABLE students
CHANGE COLUMN dob birth_date DATE;

---

3. Remove the column discount from orders table.

Answer:

ALTER TABLE orders
DROP COLUMN discount;

---

4. Make the column email UNIQUE in users table.

Answer:

ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE(email);

---

5. Rename the table products to store_products.

Answer:

ALTER TABLE products
RENAME TO store_products;

---

Interview Questions (With Answers)

---

1. What is the use of ALTER TABLE?

Answer:
It is used to modify the structure of an existing table (add, remove, modify columns, rename the table, add constraints, etc.).

---

2. What is the difference between MODIFY and CHANGE?

Answer:

• MODIFY ➝ only changes datatype.
• CHANGE ➝ renames the column + changes datatype.

---

3. How do you drop a constraint in MySQL?

Answer:

• For UNIQUE or INDEX → DROP INDEX index_name.
• For FOREIGN KEY → DROP FOREIGN KEY fk_name.

---

4. Can we rename multiple columns at once?

Answer:
No, MySQL requires separate CHANGE statements for each column.

---

5. Does ALTER TABLE lock the table?

Answer:
Yes, depending on the operation, MySQL may lock the table temporarily while altering.

---

6. How to add multiple columns in one ALTER TABLE?

Answer:

ALTER TABLE employees
ADD COLUMN city VARCHAR(100),
ADD COLUMN country VARCHAR(100);

---

7. Is ALTER TABLE reversible?

Answer:
No. You must run another ALTER TABLE to undo the changes.

---

8. How to find the names of constraints on a table?

Answer:

SHOW CREATE TABLE table_name;

---

9. What happens when you drop a PRIMARY KEY?

Answer:
The index and constraint are removed; the column becomes normal again.

---

10. Can ALTER TABLE improve performance?

Answer:
Yes! Example: adding indexes can make searches faster.

---

ALTER TABLE is like a magic toolbox that lets you reshape your database tables anytime — add parts, remove parts, rename things, and add rules.

If you’re building any real application, ALTER TABLE is a MUST-KNOW superpower!