SQL ALTER TABLE

What does the SQL ALTER TABLE statement do?

ALTER TABLE modifies an existing table's structure, such as adding, dropping, or changing columns and constraints.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ALTER TABLE: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, Redshift, BigQuery

SQL ALTER TABLE Full Explanation

ALTER TABLE is a Data Definition Language (DDL) statement that lets you change the schema of an existing table without recreating it. Typical actions include adding or deleting columns, renaming columns or the table itself, altering data types, setting or dropping default values, and managing constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK). ALTER TABLE statements are transactional in databases that support transactional DDL, meaning changes can be rolled back when wrapped in a transaction. Some back-ends lock the table during alteration, which can block reads or writes. Certain operations (like changing a column from VARCHAR to INT) may require the table to be rewritten, which can be slow on large datasets. Always test impactful alterations in staging first.

SQL ALTER TABLE Syntax

-- Add a column
ALTER TABLE table_name
ADD COLUMN new_col datatype [constraint];

-- Drop a column
ALTER TABLE table_name
DROP COLUMN col_name;

-- Modify a column definition
ALTER TABLE table_name
ALTER COLUMN col_name SET DATA TYPE new_datatype;

-- Rename a column
ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

-- Add a constraint
ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (col_name);

-- Rename table
ALTER TABLE old_table_name RENAME TO new_table_name;

SQL ALTER TABLE Parameters

table_name (identifier) - The target table to alter.
action_clause – keyword (s) - ADD, DROP, ALTER, RENAME, etc.
column_definition (expression) - Column name plus data type and optional constraints.
constraint_definition (expression) - PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK clauses.
constraint_name (identifier) - Optional name for the constraint.
new_datatype (data type) - The new data type when altering a column.

Example Queries Using SQL ALTER TABLE

-- 1. Add a NOT NULL column with default
ALTER TABLE customers
ADD COLUMN signup_ts TIMESTAMP NOT NULL DEFAULT NOW();

-- 2. Drop an obsolete column
ALTER TABLE customers
DROP COLUMN fax_number;

-- 3. Change data type from INT to BIGINT
ALTER TABLE orders
ALTER COLUMN order_id SET DATA TYPE BIGINT;

-- 4. Add foreign key constraint
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

-- 5. Rename a table
ALTER TABLE temp_users RENAME TO users_staging;

Expected Output Using SQL ALTER TABLE

The table's schema is updated
New columns appear with defaults, dropped columns are removed, data type changes take effect, constraints are enforced, and renamed objects reflect the new names
If any action violates existing data or dependencies, the statement fails and no changes are committed

Use Cases with SQL ALTER TABLE

Evolve a database schema during feature development.
Add auditing columns (created_at, updated_at).
Enforce new data integrity rules with constraints.
Migrate to wider numeric types when ID ranges grow.
Rename legacy columns for clarity without losing data.

Common Mistakes with SQL ALTER TABLE

Forgetting to add NOT NULL on new mandatory columns, allowing null data.
Changing a column type that silently truncates or fails on incompatible existing data.
Dropping a column still referenced by views or queries.
Adding a foreign key without indexing the referenced column, hurting performance.
Assuming ALTER TABLE is instant on large tables; it can lock or rewrite data.

First Introduced In

SQL-92

Frequently Asked Questions

What actions can ALTER TABLE perform?

ALTER TABLE can add, drop, rename, or modify columns, manage constraints, and rename the table itself, depending on the SQL dialect.

Is ALTER TABLE reversible?

If your database supports transactional DDL, you can wrap ALTER TABLE in a transaction and roll it back. Otherwise, you must issue a compensating ALTER TABLE statement.

Will ALTER TABLE affect existing data?

Yes. Data must conform to the new schema. For example, adding a NOT NULL column without a default will fail if existing rows contain nulls.

How can I avoid downtime during large ALTER TABLE operations?

Strategies include performing changes during low-traffic windows, using online ALTER TABLE features (where supported), or duplicating the table and swapping names.