# SQL ALTER TABLE: Change an Existing Table

- URL: https://betteranalyst.com/sql/alter-table

SQL guide

`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

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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](https://www.postgresql.org/docs/current/sql-altertable.html), [MySQL ALTER TABLE](https://dev.mysql.com/doc/refman/8.4/en/alter-table.html), and [SQLite ALTER TABLE](https://www.sqlite.org/lang_altertable.html).

## 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](https://betteranalyst.com/ai-sql-query-generator)