SQL ALTER TABLE
ALTER TABLE changes a table's structure after it exists — adding, modifying, renaming, or dropping columns and constraints.
Add a column
SQL
ALTER TABLE customers ADD phone VARCHAR(20);
Drop a column
SQL
ALTER TABLE customers DROP COLUMN phone;
Modify a column
| DB | Syntax |
|---|---|
| MySQL / MariaDB | ALTER TABLE customers MODIFY email VARCHAR(255) NOT NULL; |
| PostgreSQL | ALTER TABLE customers ALTER COLUMN email TYPE VARCHAR(255); |
| SQL Server | ALTER TABLE customers ALTER COLUMN email VARCHAR(255) NOT NULL; |
Rename
SQL
ALTER TABLE customers RENAME TO clients; -- PG / MySQL ALTER TABLE customers RENAME COLUMN email TO email_addr; -- modern DBs
Add and drop constraints
SQL
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id); ALTER TABLE orders DROP CONSTRAINT fk_customer;
Hot vs cold
Most modern engines do online ALTERs that don't block reads or writes — but very large tables can still take minutes to hours. Always check the engine's online-DDL docs and test in staging first.
Tip: Backward-compatible changes (add nullable column, add new table) are safe to deploy any time. Incompatible changes (drop column, rename column, change type) need a multi-step migration plan.
Example
Exercise
Add a new phone column to customers.
ALTER TABLE customers
phone VARCHAR(20);
Three letters; the add-column keyword.
Discussion
Loading…