What value do existing rows get when you run ALTER TABLE ... ADD COLUMN on a populated table?
answer
- the column must hold something for old rows
- nothing is invented from nowhere
- the DEFAULT decides what old rows read
- NOT NULL plus no DEFAULT on a full table
basics
~20 sExisting rows get NULL unless the new column declares a DEFAULT, in which case every existing row is populated with that default value. Adding a NOT NULL column with no DEFAULT to a table that already holds rows is rejected.
solid answer
~40 s`ALTER TABLE orders ADD COLUMN tracking_code VARCHAR(40)` adds a nullable column, and every row already in the table reads NULL for it — the statement invents no data. If the definition carries a default, as in `ADD COLUMN status VARCHAR(20) DEFAULT 'pending'`, then the existing rows are populated with `'pending'`: the default is applied to the rows that are already there, not just to future inserts. The combination that fails is `NOT NULL` with no `DEFAULT` on a non-empty table, because the rows that exist would immediately violate the constraint; supply a default, or add the column nullable, backfill it, and tighten it afterwards. The new column is appended at the end of the table's column order unless the engine offers positional syntax.
code
sql · 8 lines-- existing rows read NULL
ALTER TABLE orders ADD COLUMN tracking_code VARCHAR(40);
-- existing rows are populated with 'pending'
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';
-- rejected on a non-empty table: existing rows would violate NOT NULL
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMP NOT NULL;go deeper
Be ready to state the two outcomes cleanly: no DEFAULT means existing rows read NULL, a DEFAULT means existing rows are populated with it. Knowing that NOT NULL with no default fails on a non-empty table is the third of three facts.
Explain why the NOT NULL case fails — the stored rows would violate the constraint the instant it exists — and give the nullable-add, backfill, tighten sequence. Contrast ADD COLUMN ... DEFAULT (backfills) with ALTER COLUMN SET DEFAULT (does not).
Show judgment about the backfill value: a fabricated sentinel becomes indistinguishable from real data forever, so prefer NULL plus an explicit UPDATE when no default is truthful. Be aware engines differ on whether NOT NULL with no default errors or silently substitutes.
Own the convention: which shape of ADD COLUMN is allowed in a migration by default, whether a backfill value must be reviewable, and what the team's rule is on positional column consumption that a new trailing column can break.
## What the statement does `ALTER TABLE <table> ADD COLUMN <name> <type> [column constraints]` extends an existing table's definition with one more column. Everything that may appear in a column definition inside `CREATE TABLE` — a data type, `DEFAULT`, `NOT NULL`, `UNIQUE`, a `CHECK`, a references clause — may in principle appear here too, subject to what the engine allows. The interesting part is not the syntax but what happens to the rows that were already stored before the column existed. ## Existing rows without a DEFAULT A column has to hold something for every row. When no default is declared, that something is NULL: ```sql ALTER TABLE orders ADD COLUMN tracking_code VARCHAR(40); -- every pre-existing order now reads NULL for tracking_code ``` This is why `ADD COLUMN` on its own is the least disruptive schema change there is: the column is nullable, the old rows are semantically "unknown", and code that does not mention the column is unaffected. ## Existing rows with a DEFAULT A declared default is not merely a rule for future inserts — for `ADD COLUMN` it also determines what the already-stored rows read: ```sql ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending'; -- every pre-existing order now reads 'pending' ``` That is the single most common misunderstanding in this area, and it is worth stating explicitly because the sibling operation behaves the opposite way: `ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'new'` changes the default for a column that already exists and leaves every stored value untouched. Adding a column with a default backfills; changing a default does not. ## NOT NULL with no default ```sql ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMP NOT NULL; -- rejected if rows exist ``` The engine has nothing to put in the existing rows, and NULL is exactly what the constraint forbids, so a conforming engine refuses the statement. On an empty table the same statement succeeds, which is why this bites in production but never in a fresh test database. Engines differ on the failure mode — some raise an error, some substitute an implicit type default such as `0` or the empty string, which is arguably worse because it silently manufactures data — so do not rely on the rejection as a safety net. The portable forms are: ```sql -- one statement, backfilled from the default ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMP NOT NULL DEFAULT TIMESTAMP '1970-01-01 00:00:00'; -- or three steps when there is no honest default value ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMP; -- nullable UPDATE orders SET shipped_at = ordered_at WHERE shipped_at IS NULL; ALTER TABLE orders ALTER COLUMN shipped_at SET NOT NULL; -- spelling varies by engine ``` The three-step form is preferable whenever no default is truthful: a fabricated sentinel like `1970-01-01` looks like real data forever afterwards, while NULL says "we do not know". ## Column order and expressions The new column is appended after the last existing column. Nothing in the standard lets you insert it in the middle; MySQL adds `FIRST` and `AFTER <column>` placement clauses as an extension. This matters for anything that consumes columns positionally — `SELECT *` into a fixed-shape struct, `INSERT INTO t VALUES (...)` without a column list, a CSV export — all of which are reasons to write explicit column lists in the first place. What may appear in the `DEFAULT` expression is also engine-dependent. A literal constant is universally accepted; function calls and parenthesised expressions are not. SQLite, for instance, requires the default in `ADD COLUMN` to be a constant and rejects `CURRENT_TIMESTAMP` there, even though the same default is legal in `CREATE TABLE`. When in doubt, add the column with a constant default (or none) and populate computed values with a subsequent `UPDATE`. ## What ADD COLUMN does not do It does not touch any other table, it does not change what existing queries return unless they use `SELECT *`, and it does not alter the meaning of the rows — a NULL in a newly added column means "this column did not exist when the row was written", which is often exactly right and occasionally needs a backfill to become useful.
- Where in the table's column order does the new column appear, and why should you care?At the end, after every existing column; the standard offers no way to place it elsewhere, though MySQL adds FIRST and AFTER clauses. It matters for anything positional: `SELECT *` consumed by index, `INSERT INTO t VALUES (...)` with no column list, fixed-shape exports. Naming columns explicitly in queries removes the exposure entirely.
- If you later run ALTER TABLE ... ALTER COLUMN ... SET DEFAULT, do the stored rows change?No. Changing the default on an existing column is a catalog change only: it affects inserts that omit the column from then on, and every value already stored stays exactly as it is. That asymmetry with ADD COLUMN ... DEFAULT, which does populate existing rows, catches people out regularly. To change stored values you need an UPDATE.
- Can the DEFAULT in ADD COLUMN be an expression such as CURRENT_TIMESTAMP?Engines differ, so treat it as non-portable. A literal constant is accepted everywhere; function calls and parenthesised expressions are not — SQLite, for example, requires a constant default in ADD COLUMN and rejects CURRENT_TIMESTAMP there. The portable pattern is to add the column with a constant default or none, then populate computed values with an UPDATE.
saying these in an interview costs you the question
- Thinks existing rows stay NULL even when a DEFAULT is declared
- Believes ADD COLUMN NOT NULL always works on a populated table
- Says a DEFAULT only ever applies to future inserts
- Assumes the new column can be placed between existing columns portably
- Confuses a column DEFAULT with a value the application supplies