Skip to content

ALTER TABLE Statement 🔧

Prerequisites: CREATE TABLE

Mentor's Note: Requirements change. You built a "Users" table, but now the client wants to store "Phone Numbers". You don't need to delete the table and start over; you just Renovate it! 💡

🌟 The Scenario: The Home Renovation 🏠

  • Add Column: Building a new balcony.
  • Drop Column: Removing an old fireplace.
  • Modify Column: Expanding a small window into a large glass door.

💻 1. ADD Column (Expansion) ➕

Add a new field to your table.

-- Syntax
ALTER TABLE table_name ADD column_name datatype;

-- Example: Add Email to Students
ALTER TABLE students ADD email VARCHAR(100);

💻 2. DROP Column (Cleanup) ✂️

Remove a field you no longer need. Warning: Deletes all data in that column!

-- Syntax
ALTER TABLE table_name DROP COLUMN column_name;

-- Example: Remove Age (Calculate it from DOB instead)
ALTER TABLE students DROP COLUMN age;

💻 3. MODIFY / ALTER Column (Renovation) 🛠️

Change the data type or size of a column.

-- Expand name from 50 to 100 chars
ALTER TABLE students MODIFY first_name VARCHAR(100);

ALTER TABLE students ALTER COLUMN first_name VARCHAR(100);

ALTER TABLE students ALTER COLUMN first_name TYPE VARCHAR(100);

💡 Important Rules

  1. Not Null to Null: You can usually easily relax a rule.
  2. Null to Not Null: You can only do this if the column currently has NO null values.
  3. Reducing Size: You cannot shrink a VARCHAR(100) to VARCHAR(10) if you already have a 50-character name stored.

📈 Learning Path