Right, so you’ve built your table. It’s a thing of beauty. It holds your data with the tender embrace of a perfectly normalized schema. But time passes, requirements change, and that perfect table now looks… less perfect. Maybe marketing needs to track a new user preference, or you need to purge a column that was a bad idea from the start (we’ve all been there). This is where ALTER TABLE comes in—your digital wrench for performing table surgery. It’s powerful, but like any surgery, you don’t want to do it blindfolded.

The Art of the ADD COLUMN

Adding a column is the most common and usually the safest ALTER operation. The syntax is straightforward, but the devil is in the details.

ALTER TABLE users
ADD COLUMN IF NOT EXISTS twitter_handle VARCHAR(15);

First, notice the IF NOT EXISTS. This is your best friend. It makes the statement idempotent, meaning you can run it in a script a thousand times, and if the column is already there, it’ll just shrug and move on. This is a lifesaver for deployment scripts. Now, about that VARCHAR(15): Twitter handles have a max length, so defining it as VARCHAR(255) would be lazy and wasteful. Be precise.

The big question when adding a column: what about existing rows? The database slaps a NULL in there for every existing row by default. If that’s not what you want, you need to specify a DEFAULT constraint.

ALTER TABLE users
ADD COLUMN signup_date DATE NOT NULL DEFAULT CURRENT_DATE;

This is fine. But beware: if you’re adding a NOT NULL column without a default, the operation will fail spectacularly. The database has no idea what value to put in for the millions of rows you already have, and it rightly refuses to guess. Always provide a DEFAULT for NOT NULL columns on existing tables.

Dropping Columns: Point of No Return

Dropping a column is the equivalent of throwing your data into a shredder. There is no “undo”. Most databases mark the space as reclaimable rather than instantly nuking it, but from your perspective, it’s gone.

ALTER TABLE users
DROP COLUMN IF EXISTS my_space_profile_url;

Thank the tech gods for IF EXISTS. It prevents an error if the column is already gone, which, again, is fantastic for scripts. The real danger here is dependencies. Did you have an index on that column? A view? A trigger? The database will usually complain and stop you, which is good. But sometimes the dependency is in some application code two repositories away. Always, always check what uses a column before you drop it. A quick pg_depend in PostgreSQL or looking at INFORMATION_SCHEMA in MySQL can save you from a world of pain.

The Quagmire of MODIFY COLUMN / ALTER COLUMN

This is where things get messy. Changing a column’s data type or constraints is often a blocking, expensive operation that can bring your database to its knees. The database often has to rewrite the entire table, locking it for the duration.

Want to change a VARCHAR(50) to a VARCHAR(100)? This is usually a metadata-only change in modern databases (PostgreSQL, recent MySQL) because you’re only increasing the limit. It’s fast and painless.

ALTER TABLE posts
ALTER COLUMN title TYPE VARCHAR(100);

But try to change an INTEGER to a BIGINT, or a VARCHAR to a TEXT? That’s often a full table rewrite. On a billion-row table, this is not an “alter”; it’s a “go get a coffee, maybe several coffees, and hope the disk doesn’t fill up” operation.

The most common pitfall is trying to make a column NOT NULL when it contains existing NULL values. You can’t. You have to clean your data first.

-- First, fix the data:
UPDATE users SET phone_number = 'unknown' WHERE phone_number IS NULL;

-- Then, add the constraint:
ALTER TABLE users
ALTER COLUMN phone_number SET NOT NULL;

Trying to do it in one step is like trying to put a “Wet Paint” sign on a door that’s already covered in fingerprints. Fix the mess, then make the rule.

A Word on Constraints

You can ADD and DROP constraints directly, which is often smarter than bundling them in a column modification.

-- Adding a check constraint
ALTER TABLE products
ADD CONSTRAINT positive_price CHECK (price > 0);

-- Dropping one because the business folks said we have to give things away for free now
ALTER TABLE products
DROP CONSTRAINT positive_price;

Adding a UNIQUE or FOREIGN KEY constraint will trigger a scan of the table to validate all existing data. If you’re sure the data is clean, some databases let you add the constraint with NOT VALID (PostgreSQL) or by disabling the check (others) to speed it up, deferring the validation until later. This is advanced, trench-level stuff that can save hours of downtime.

The golden rule of ALTER TABLE? Never run it on a live, production table without testing the exact operation on a staging copy first. See how long it takes, what it locks, and what breaks. Your future self, the one who isn’t dealing with a 3 a.m. outage, will thank you.