SQL ALTER TABLE Statement

Learn how to use SQL ALTER TABLE to add, remove, and change columns, understand dialect differences, and apply schema changes safely.

ALTER TABLE is the SQL statement used to modify the definition of an existing database table. You can use it to add a column, remove a column, or change a column's definition, including its data type.

These are changes to the table's schema. A schema is the structural definition of database objects, including tables, columns, data types, and constraints. ALTER TABLE does not normally change row values directly. Use UPDATE, INSERT, or DELETE to change, add, or remove rows.

What ALTER TABLE Changes

A table contains rows and columns. A column is a named field that stores one kind of value for each row. A data type defines the kind of value a column can store, such as INTEGER, VARCHAR, DATE, or DECIMAL.

ALTER TABLE can change structural details such as:

Which columns exist.
The data type or properties of a column.
Constraints, defaults, and other supported table components.

For background on creating tables, see SQL CREATE TABLE. Constraints such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK are rules enforced by the database; review SQL constraints before changing constrained columns.

General ALTER TABLE Structure

The general pattern is:

ALTER TABLE table_name alteration_clause;

ALTER TABLE identifies the schema-change statement.
table_name is the existing table to change.
alteration_clause describes the operation, such as ADD, DROP COLUMN, or a dialect-specific column modification clause.
column_name identifies a column when the operation affects one column.
data_type specifies what kind of values the column can store.

A single ALTER TABLE statement may support multiple clauses in some database systems, but combining changes is not portable. Separate statements can make testing and troubleshooting easier.

Common ALTER TABLE Operations

Goal | Typical syntax | Effect | Key caution

Add a column | ALTER TABLE table_name ADD column_name data_type; | Creates a new column | Existing rows may contain NULL or a default value

Drop a column | ALTER TABLE table_name DROP COLUMN column_name; | Removes the column and its stored values | Check dependencies and back up important data first

Change a column type | Dialect-specific, such as MODIFY COLUMN or ALTER COLUMN ... TYPE | Redefines how values are stored or validated | Conversion can fail, truncate data, or lose precision

Adding a Column with ADD

Use ADD to create a new column in an existing table. The new column requires a name and a data type.

ALTER TABLE customers ADD email VARCHAR(255);

This adds an email column that can store character strings up to 255 characters in systems that use this form.

You can commonly specify additional properties:

ALTER TABLE orders
ADD status VARCHAR(20) NOT NULL DEFAULT 'pending';

NOT NULL requires every row to have a value. DEFAULT supplies a value when an inserted row does not provide one. A default value is automatically assigned in that situation.

When a column is added to a table that already contains rows, the database must determine the new column's value for those rows:

If the column permits NULL and has no default, existing rows commonly receive NULL.
If a default is defined, existing rows may receive that default, depending on the database engine and its implementation.
If the column is NOT NULL without a usable default, the operation may fail because existing rows have no valid value.

Constraints may also be added with the column. For example, a unique email address might be declared with a UNIQUE constraint, subject to the rules of the database system.

Adding a Required Column Safely

On a populated table, a staged approach is often safer:

Add the column as nullable, or add it with a suitable default.
Populate or verify values for existing rows.
Check that every row satisfies the intended rule.
Apply NOT NULL or another constraint using the syntax supported by the database engine.

Dropping a Column with DROP COLUMN

Use DROP COLUMN to remove a column:

ALTER TABLE employees DROP COLUMN middle_name;

Dropping a column removes both its definition and its stored values. This is different from merely hiding a column in a query. The removed data may not be recoverable unless it exists in a backup or another copy.

Before dropping a column, check:

Foreign keys and other constraints.
Indexes that use the column.
Views, triggers, stored procedures, and routines.
Reports, queries, and application code.
Data exports, integrations, and scheduled jobs.
Backups and the procedure for restoring the data if the change is wrong.

Some database products or older versions limit column removal, require extra steps, or rebuild the table. Check the documentation for the exact engine and version.

Changing a Column Data Type

Changing a column's data type changes its definition. The syntax is not portable.

MySQL-Style MODIFY COLUMN

MySQL commonly uses MODIFY COLUMN:

ALTER TABLE products
MODIFY COLUMN product_code VARCHAR(50);

This example increases the permitted length of product_code. When redefining a column, include any properties that must remain, such as NOT NULL, a default, or other supported attributes. Omitting a property can change the column's behavior in some systems.

PostgreSQL-Style ALTER COLUMN

PostgreSQL commonly uses ALTER COLUMN together with TYPE:

ALTER TABLE products
ALTER COLUMN product_code TYPE VARCHAR(50);

Other database systems also use ALTER COLUMN, but the complete syntax can differ. SQL Server, for example, commonly uses:

ALTER TABLE products
ALTER COLUMN product_code VARCHAR(50);

Oracle commonly uses a MODIFY clause, while SQLite has more limited ALTER TABLE capabilities, although newer SQLite versions support some additional operations, including dropping a column. These examples are patterns, not interchangeable commands.

Data Compatibility and Conversion Risks

Before changing a type, verify that every existing value can be represented by the target type. Potential problems include:

Conversion failures: text such as 'unknown' cannot be converted to an integer.
Truncation: a value may be longer than the new character limit.
Loss of precision: converting a high-precision decimal to a smaller scale can discard fractional digits.
Range overflow: a number may not fit into the target numeric type.
Format problems: date or time strings may not match the target format.
Constraint conflicts: converted values may violate NOT NULL, UNIQUE, CHECK, or key constraints.

Clean or transform incompatible data before the alteration, and test the conversion against representative data.

ALTER TABLE Syntax by Database System

Database system | Add column form | Change column form | Drop column support or notes

MySQL | ALTER TABLE t ADD c data_type; | Commonly MODIFY COLUMN c data_type; CHANGE COLUMN can also rename and redefine a column | Supports DROP COLUMN; exact behavior can depend on version and table features

PostgreSQL | ALTER TABLE t ADD COLUMN c data_type; | ALTER TABLE t ALTER COLUMN c TYPE data_type; | Supports DROP COLUMN; dependencies may require deliberate handling

SQL Server | ALTER TABLE t ADD c data_type; | ALTER TABLE t ALTER COLUMN c data_type; | Supports DROP COLUMN; constraints and indexes can affect removal

Oracle | Commonly ALTER TABLE t ADD (c data_type) | Commonly ALTER TABLE t MODIFY (c data_type) | Supports column removal, with dependency and version considerations

SQLite | ALTER TABLE t ADD COLUMN c data_type; | Direct type alteration is limited; a table-rebuild migration is often required | Modern versions support DROP COLUMN, but capabilities and restrictions should be checked

The common distinction is that MySQL-style systems often use MODIFY COLUMN, while PostgreSQL and SQL Server commonly use an ALTER COLUMN form. Oracle and SQLite have their own restrictions and syntax. Consult the documentation for the current database engine before running production changes.

Safe Schema-Change Workflow

1. Inspect the Existing Definition

First confirm the table's columns, types, defaults, indexes, and constraints. Use a database-specific inspection command, for example:

DESCRIBE table_name;

DESCRIBE is common in MySQL environments. Other systems may provide catalog commands or queries against information_schema. Do not assume that a table has the definition you expect.

2. Find Dependencies

Search application code, views, routines, reports, indexes, and constraints for references to a column being changed or removed. A schema change can succeed while still breaking code that expects the old column or type.

3. Test in Development or Staging

Run the change against a realistic copy of the table. Include representative data, large rows, unusual values, and relevant indexes and constraints. Test both the ALTER TABLE command and the application behavior afterward.

4. Back Up Important Data

Take an appropriate backup or create another recovery point before destructive changes such as dropping a column or converting a type. Confirm that the backup can actually be restored.

5. Consider Transactions, Locks, and Downtime

Use a transaction when the database engine supports transactional DDL and when it is appropriate for the operation. DDL transaction behavior differs between systems. Some alterations lock a table, rebuild it, or require substantial disk space. On large or busy tables, this can block reads or writes and cause downtime.

Measure the operation on representative data, schedule disruptive work appropriately, and use engine-supported online migration features when available.

6. Apply and Validate

After the change, inspect the table definition again. Confirm that:

The intended column exists, is absent, or has the intended type.
Defaults and constraints have the expected behavior.
Existing data was preserved or converted correctly.
Indexes, views, queries, and application functions still work.
New inserts and updates obey the new definition.

Troubleshooting ALTER TABLE

Syntax Error for MODIFY COLUMN

Likely cause: The database uses ALTER COLUMN, TYPE, or another dialect-specific form.

Resolution: Identify the database engine and version, then use its documented syntax. Do not replace keywords by guesswork.

Type Change Fails During Conversion

Likely cause: Existing values do not fit the target type, length, scale, range, or format.

Resolution: Identify incompatible rows, clean or transform the data, choose a compatible target type, and retry in a test environment.

Adding a NOT NULL Column Fails

Likely cause: Existing rows need a value for the new required column.

Resolution: Add a suitable default, add the column as nullable and populate it before enforcing NOT NULL, or use a staged migration.

Dropping a Column Fails

Likely cause: The column is referenced by a constraint, index, view, stored routine, query, or application code.

Resolution: Locate the dependencies and deliberately update or remove them before dropping the column.

ALTER TABLE Is Slow or Blocks Activity

Likely cause: The database may lock the table or rebuild it while applying the change.

Resolution: Test on representative data, schedule the change appropriately, use supported online migration options when available, and prepare a rollback plan.

Exam-Relevant Notes

ALTER TABLE changes an existing table's definition; it is not the normal statement for changing row values.
ADD creates a column and requires a column name and data type.
DROP COLUMN removes the column definition and its stored values.
MODIFY COLUMN is common in MySQL-style syntax.
ALTER COLUMN and TYPE are common parts of type-change syntax in other systems, including PostgreSQL.
Adding NOT NULL to a populated table requires valid values for existing rows.
Type changes can fail or lose data when existing values are incompatible.
ALTER TABLE syntax and capabilities vary by database product and version.