ADD COLUMN
SQL · Working with Tables
Theory
ALTER TABLE ... ADD COLUMN appends a new column to an existing table. ALTER TABLE employees ADD COLUMN email VARCHAR(150) adds the column at the end of the column list, and every existing row gets NULL in it until you fill it with UPDATE. New rows can supply a value straight away.
If you give a DEFAULT, existing rows show that default instead of NULL: ADD COLUMN status VARCHAR(10) DEFAULT 'active'. Adding a NOT NULL column without a default fails on a table that already has rows, because those rows would break the rule; add it with a default, or add it nullable, backfill, then set NOT NULL. Most engines cannot place the column in the middle (MySQL is an exception with AFTER col). SELECT * output changes, which is one more reason to list columns explicitly.
Why we use it
Businesses keep collecting new facts, like a customer's email or an order's channel. ADD COLUMN extends the table to hold them without losing or reloading existing data.
When to use it
Use ADD COLUMN when a new attribute belongs to the entity. If the attribute can have many values per row (several phone numbers), create a separate related table instead of adding phone1, phone2 columns.
How it works
- ALTER TABLE employees ADD COLUMN email
- Is a DEFAULT given? Yes: Existing rows show the default. No: Existing rows get NULL.
- NOT NULL, no default, rows exist? Yes: Error: add a default or backfill. No: Column added.
- Fill old rows with UPDATE if needed
- New column is part of the table
Syntax
ALTER TABLE table_name
ADD COLUMN column datatype;Example
ALTER TABLE employees ADD COLUMN email VARCHAR(150);Key points
- New column is NULL for existing rows unless a default is set
- NOT NULL needs a DEFAULT when the table has rows
- The column is added at the end in most engines
- Backfill old rows with UPDATE after adding
- Repeating columns (phone1, phone2) usually belong in a separate table