SQL ALTER TABLE Statement Explained

Once a table exists in your database, you rarely get its design perfectly right the first time. Maybe a new feature needs an extra column, an old column needs to go, or a column's data type was chosen too narrowly. The ALTER TABLE statement is how you make these structural changes without dropping and recreating the table — and without losing the data already in it.

ALTER TABLE is a DDL (Data Definition Language) statement, just like CREATE TABLE. It modifies the table's structure, not its rows — for changing row values you'd use UPDATE instead.

Adding a Column

To add a new column to an existing table, use ADD COLUMN (or just ADD in most databases). Here we add an email column to the employees table used throughout this course:

ALTER TABLE employees
ADD COLUMN email VARCHAR(100);

Result:

Query OK, 0 rows affected

Existing rows get NULL in the new column automatically. If you want every existing row to start with a real value instead, pair this with a DEFAULT constraint or follow up with an UPDATE.

Dropping a Column

To remove a column — permanently, along with all the data stored in it — use DROP COLUMN:

ALTER TABLE employees
DROP COLUMN email;

Result:

Query OK, 0 rows affected

There's no undo here short of restoring from a backup, so double-check you're dropping the right column on the right table before running this in production.

Changing a Column's Data Type

This is where syntax diverges the most between databases. MySQL uses MODIFY COLUMN, while PostgreSQL and SQL Server use ALTER COLUMN with slightly different follow-up syntax:

-- MySQL
ALTER TABLE employees
MODIFY COLUMN name VARCHAR(150);

-- PostgreSQL
ALTER TABLE employees
ALTER COLUMN name TYPE VARCHAR(150);

Widening a column (e.g. VARCHAR(100) to VARCHAR(150)) is usually safe and instant. Narrowing it, or changing to an incompatible type, can fail if existing data wouldn't fit — the database will refuse the change rather than silently truncate your data.

Renaming a Column

Renaming also varies by database:

-- MySQL (8.0+)
ALTER TABLE employees
RENAME COLUMN dept TO department;

-- PostgreSQL
ALTER TABLE employees
RENAME COLUMN dept TO department;

Result:

Query OK, 0 rows affected

Renaming a column is a structural change only — it doesn't touch the data, just the name you reference going forward. Be aware it can silently break application code or views that still reference the old column name.

In general, treat ALTER TABLE on a large production table with care: some operations lock the table while they run, and on very large tables that can mean real downtime. Testing the change on a staging copy first is always worth the extra step.

---