ALTER TABLE – Changing the Structure of a SQL Table

(because sometimes your table needs a little upgrade)

Quick Syntax Time

Wanna add a new column? Change one? Drop it entirely? ALTER TABLE is your tool.

ALTER TABLE table_name
ADD column_name data_type;

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
MODIFY column_name new_data_type;

“Hey SQL, I wanna tweak the design of my table — not the data, just the layout.”

Add a New Column – let’s say email

You suddenly realize…
“Bro, how do I not have emails saved?!”

ALTER TABLE friends
ADD email VARCHAR(100);

Output:

Query OK, 1 row affected

id	name	age	phone	email
1	Olivia	26	5550001234	NULL
2	Mason	24	5556712345	NULL
3	Zoe	30	5559083344	NULL
4	Ethan	21	5554421199	NULL
5	Harper	25	5552223344	NULL

Remove a Column – you don’t want email anymore?

“Ugh… forget it, too much spam anyway.”

ALTER TABLE friends
DROP COLUMN email;

Output:

Query OK, 1 row affected

id	name	age	phone
1	Olivia	26	5550001234
2	Mason	24	5556712345
3	Zoe	30	5559083344
4	Ethan	21	5554421199
5	Harper	25	5552223344

Change a Column’s Type – phone needs more space?

Phone numbers getting longer? International maybe? No stress.

ALTER TABLE friends
MODIFY phone VARCHAR(20);

Output:

Query OK, 1 row affected

Now you can store up to 20 characters in the phone column.
Even numbers like +1-555-222-3344 will fit.

Recap – ALTER is your table’s personal stylist

• ADD – bring in a new column
• DROP COLUMN – remove the clutter
• MODIFY – upgrade what’s already there

It’s like renovating your data house — no moving out required.

Now that you know how to change your tables around, if you want to learn more about removing things from your database, you can read more here:
More about removing from your database