ALTER TABLE — Add & Drop Columns

Mid-season, the franchise wants to store each player's jersey number. You don't want to delete the players table and rebuild it — you just want one more column. That's exactly what ALTER TABLE is for.

Definition

ALTER TABLE is a DDL statement that modifies the structure of an existing table — for example adding a column, removing a column, changing a column's data type, or adding/removing constraints — while keeping the table and its other data.

• Existing table — unlike CREATE, the table must already exist.
• Keeps the data — all rows remain; only the structure changes.

This topic covers ADD and DROP COLUMN. Changing a data type (ALTER COLUMN) has its own topic next.

Syntax

-- Add a column (SQL Server)
ALTER TABLE table_name
ADD column_name data_type [constraint];

-- Remove a column
ALTER TABLE table_name
DROP COLUMN column_name;

ADD — keep everything + 1 new column

ALTER TABLE players ADD jersey_no INT;

SELECT * FROM players;

Step by step: 1️⃣ ALTER TABLE players picks the table → 2️⃣ ADD jersey_no INT adds the column.

Existing rows get NULL in the new column until you fill it (with UPDATE). The new column always appears at the end — SQL Server has no "add after column X" option.

ADD a column that needs a value: NOT NULL + DEFAULT

What if the new column must never be NULL? A table that already has rows can't get a plain NOT NULL column — what value would the old rows get? Give it a DEFAULT so SQL Server can fill the existing rows:

ALTER TABLE players
ADD titles INT NOT NULL DEFAULT 0;

You can add several columns at once in SQL Server by separating them with commas: ALTER TABLE players ADD jersey_no INT, role VARCHAR(20);

DROP COLUMN — remove a column

ALTER TABLE players DROP COLUMN jersey_no;

Step by step: 1️⃣ ALTER TABLE players → 2️⃣ DROP COLUMN jersey_no. The table goes back to id, name, team, country, runs.

Warning: The column AND its data are removed for good. If 6 players had jersey numbers, all 6 numbers are gone. There is no "undo".

Adding and removing constraints

ALTER TABLE can also add rules to an existing table (SQL Server):

ALTER TABLE players
ADD CONSTRAINT ck_players_runs CHECK (runs >= 0);

ALTER TABLE players
DROP CONSTRAINT ck_players_runs;

Common mistakes

• ❌ ALTER TABLE players ADD COLUMN jersey_no INT in SQL Server → syntax error. ✅ SQL Server uses just ADD (MySQL/PostgreSQL accept ADD COLUMN).
• ❌ ALTER TABLE players DROP jersey_no ✅ DROP COLUMN jersey_no — the word COLUMN is required for dropping.
• ❌ Adding a NOT NULL column without a DEFAULT to a table that has rows → error. ✅ Add a DEFAULT, or add it as NULL, fill it, then make it NOT NULL.
• ❌ Dropping a column that has a constraint (e.g. a DEFAULT) in SQL Server → "The object 'DF__players__…' is dependent on column…". ✅ DROP CONSTRAINT first, then DROP COLUMN.
• ❌ Expecting the new column to be filled automatically. ✅ It's NULL until you UPDATE it (unless it has NOT NULL + DEFAULT).

SQL Server vs MySQL / PostgreSQL

Interview tip

Q: You add a new nullable column to a table with 1 million rows. What values do the existing rows have? A: NULL. A nullable column with no default leaves old rows as NULL. If you add it as NOT NULL with a DEFAULT, SQL Server fills the existing rows with the default value instead.