SQL Alter Table

The ALTER TABLE statement changes an existing table's structure by adding, modifying, or removing columns and constraints.

Why ALTER TABLE matters

Requirements change after a table is built, and ALTER TABLE lets you adjust the structure without losing the data already stored. You can add a new column, remove one you no longer need, change a column's data type, or attach and drop constraints. This makes it the main tool for evolving a schema over time.

Adding a column

The most common change is adding a column. You give ALTER TABLE the table name, the ADD keyword, and the new column definition, just as you would inside CREATE TABLE. Existing rows receive NULL in the new column unless you specify a DEFAULT value.

Adding columns

-- Add a single column
ALTER TABLE employees
ADD phone VARCHAR(20);

-- Add a column with a default for existing rows
ALTER TABLE employees
ADD department VARCHAR(50) DEFAULT 'Unassigned';

Modifying and dropping columns

You can also change an existing column's data type or remove a column entirely. The exact keyword for changing a type differs by system: many databases use ALTER COLUMN, while MySQL uses MODIFY COLUMN. Dropping a column deletes that column and all values stored in it, so treat it with care.

Changing and removing columns

-- Change a column's data type (standard / SQL Server / PostgreSQL)
ALTER TABLE employees
ALTER COLUMN phone TYPE VARCHAR(30);

-- Remove a column completely
ALTER TABLE employees
DROP COLUMN department;
ActionClause
Add a columnADD column_name data_type
Remove a columnDROP COLUMN column_name
Change a data typeALTER COLUMN ... (or MODIFY in MySQL)
Rename a columnRENAME COLUMN old_name TO new_name
Note: ALTER TABLE syntax is one of the least standardized parts of SQL. Keywords for modifying and renaming columns vary between MySQL, PostgreSQL, SQL Server, and others, so confirm the exact form for your database.
  • Give new columns a DEFAULT when existing rows should not be left with NULL.
  • Changing a data type can fail if current values do not fit the new type, so review the data first.
  • Dropping a column is permanent; back up or export the values if they might be needed.
  • On large tables, structural changes can be slow and may briefly lock the table, so schedule them thoughtfully.

Keep Going

SQL Interview QuestionsPractice real questions asked for SQLDatabase Administrator SalarySee what Database Administrator roles pay in the marketAuto Apply for JobsApply to open roles related to SQL and tweak your resume