SQL guide

SQL ALTER TABLE: Change an Existing Table

ALTER TABLE changes a table's structure without creating a new table. Use it to add, rename, or remove columns, rename a table, and—in supported databases—change column definitions or constraints. The exact syntax and operational cost depend on your database.

Basic syntax

ALTER TABLE table_name
action;

Replace table_name with the table to change and action with an operation such as ADD COLUMN or RENAME COLUMN. Check the syntax for your SQL dialect before running it.

Common ALTER TABLE examples

These examples show common forms supported by PostgreSQL and recent MySQL and SQLite releases. Some edge cases, especially dropping columns or changing constraints, vary by database and version.

Add a column

Add a nullable column first when existing rows do not yet have a value. Then backfill data and add stricter constraints in a separate step.

ALTER TABLE customers
ADD COLUMN phone_number VARCHAR(30);

Rename a column

Update queries and application code that still reference the old name. Check dependent views and integrations before deploying.

ALTER TABLE customers
RENAME COLUMN phone_number TO contact_number;

Rename a table

Renaming does not rewrite every query that refers to the table; update those callers as part of the same rollout.

ALTER TABLE customers
RENAME TO clients;

Drop a column

Dropping a column can permanently remove its data. Back it up and remove application dependencies first.

ALTER TABLE customers
DROP COLUMN contact_number;

Where SQL dialects differ

PostgreSQL: use ALTER COLUMN ... TYPE to change a column type. Some changes take strong table locks or require a table rewrite, so review the operation before running it on a large production table.

MySQL: use MODIFY COLUMN to change a definition, or CHANGE COLUMN to rename and redefine it. Online DDL behavior depends on the operation and storage engine.

SQLite: direct ALTER TABLE support is limited to renaming a table or column, adding a column, and dropping a column. Other schema changes can require rebuilding the table.

Reference: PostgreSQL ALTER TABLE, MySQL ALTER TABLE, and SQLite ALTER TABLE.

Before changing a production table

  1. Back up the table or confirm you have a tested restore path.
  2. Check views, queries, dashboards, and application code that depend on the existing schema.
  3. Test the statement against the same database engine and version in a non-production environment.
  4. For large tables, check locking, rewrite, and downtime behavior before scheduling the migration.
  5. Deploy dependent code and schema changes in an order that keeps both old and new versions working during rollout.

Need help writing the SQL?

Describe the table and change you want, then review the generated statement against your database documentation before running it.

Try the AI SQL generator