VMware ESXi and vSphere Cluster Management

SQL ALTER TABLE Statement

Learn how to use SQL ALTER TABLE to add, remove, and modify columns safely across MySQL, PostgreSQL, SQL Server, Oracle, and SQLite.

ALTER TABLE is an SQL statement used to change the definition of an existing table. You can use it to add columns, remove columns, change data types, and alter column properties such as defaults or nullability.

This lesson assumes you understand tables, rows, columns, common data types, CREATE TABLE, and basic constraints.

What ALTER TABLE changes

A table's schema is its structural definition: its tables, columns, data types, constraints, indexes, and related rules. ALTER TABLE changes this structure after the table already exists.

For example, adding an email column changes the table's schema. It does not directly edit one customer's email value.

To change individual row values, use UPDATE:

UPDATE customers
SET email = 'sam@example.com'
WHERE customer_id = 42;

Structural changes can affect existing data, constraints, indexes, views, procedures, application queries, reports, and permissions. Treat an alteration as a database change that may require coordinated application work.

Basic ALTER TABLE syntax

The general structure is:

ALTER TABLE table_name operation;

• table_name is the existing table to change.
• operation describes the requested schema change.
• column_name identifies a column when the operation concerns one column.
• datatype specifies what kind of values the column stores, such as INTEGER, VARCHAR(200), DATE, or DECIMAL(10,2).

The words following the table name determine the operation, such as ADD, DROP COLUMN, MODIFY COLUMN, or ALTER COLUMN. Exact syntax differs between database products.

Common ALTER TABLE operations

Adding a column

Use ADD to create a new column:

ALTER TABLE customers
ADD email VARCHAR(254);

This adds a text column named email. The data type should match the values the application needs to store. For example, use an integer type for counts, a date type for calendar dates, and a decimal type for exact monetary quantities.

Adding a nullable column

Unless a NOT NULL rule is specified, many databases allow the new column to contain NULL. NULL is a marker for an unknown, missing, or inapplicable value; it is not the same as an empty string or zero.

ALTER TABLE customers
ADD email VARCHAR(254) NULL;

Adding a nullable column is often the safer first step for a populated table because existing rows can remain without a value.

Adding a default value

A DEFAULT is a value the database supplies when an insert does not provide a value for that column:

ALTER TABLE customers
ADD status VARCHAR(20) DEFAULT 'active';

Defaults primarily govern inserts made after the schema change. Whether existing rows are physically filled with the default, logically read as having the default, or remain NULL depends on the database engine and exact statement. Check the selected product's documentation and verify the result.

Adding a required column to a populated table

NOT NULL requires every row to have a value. Adding a required column directly can fail because existing rows have no value for it:

ALTER TABLE customers
ADD account_level VARCHAR(20) NOT NULL;

A safer migration commonly uses these stages:

1. Add the column as nullable, or add it with a suitable default where supported.
2. Populate values for existing rows.
3. Check that no rows still contain NULL or invalid values.
4. Apply NOT NULL using the syntax for the database engine.
5. Update application code so new records always provide a valid value.

Dropping a column

Use DROP COLUMN to remove a column:

ALTER TABLE customers
DROP COLUMN legacy_code;

Dropping a column removes its stored values. This can be destructive and may not be reversible without a backup or another copy of the data.

Before removing legacy_code, inspect:

• Indexes and unique rules involving the column.
• Foreign keys, check constraints, generated columns, and triggers.
• Views, stored procedures, functions, reports, and scheduled jobs.
• Application queries, object-relational mappings, exports, and integrations.
• Permissions or policies that mention the column.

Some database systems automatically remove certain dependent objects, while others reject the operation unless dependencies are removed or an explicit cascade is requested. Check the engine's behavior before running the statement.

Changing a column data type or definition

Changing a column can alter its data type, maximum length, precision, scale, default, or nullability. A data conversion is the process of turning values from the old type into the new type.

MODIFY COLUMN syntax

MySQL and some related systems use MODIFY COLUMN:

ALTER TABLE products
MODIFY COLUMN product_name VARCHAR(300);

This increases the maximum length of product_name. Increasing capacity is generally safer than reducing it because existing values are less likely to exceed the new limit.

Some dialects require you to restate properties when modifying a column. If the original column had NOT NULL, a default, or another property, include the required properties according to that product's syntax rather than assuming they will be preserved.

ALTER COLUMN syntax

PostgreSQL uses an ALTER COLUMN ... TYPE form:

ALTER TABLE products
ALTER COLUMN product_name TYPE VARCHAR(300);

SQL Server uses a similar clause without the TYPE keyword:

ALTER TABLE products
ALTER COLUMN product_name VARCHAR(300);

These are not interchangeable. Use the form required by your database system.

Conversion, length, precision, and scale

Before changing a type, verify that existing values fit the new definition:

• Changing text to a shorter VARCHAR length can fail or truncate values that are too long.
• Changing an integer quantity to DECIMAL(10,2) may be appropriate, but check that all values convert correctly.
• Reducing decimal precision or scale can round values, reject them, or lose information.
• Changing a date or numeric column from text requires every stored string to have a valid format.
• Changing nullability fails if existing rows contain NULL.

For example, changing a quantity column to a decimal type may use:

ALTER TABLE order_items
ALTER COLUMN quantity TYPE DECIMAL(10,2);

For a production change, first identify invalid or out-of-range values, test the conversion on representative data, and decide how to clean or migrate values that do not fit.

SQL dialect differences

ALTER TABLE is a common SQL concept, but exact grammar and supported operations vary among MySQL, PostgreSQL, SQL Server, Oracle, and SQLite. Support can also vary by database version.

In particular, MODIFY COLUMN and ALTER COLUMN are alternatives used by different products, not universal SQL replacements. Support for dropping columns, changing constraints, renaming columns, and combining multiple alterations in one statement also varies.

Consult the documentation for the chosen database engine and version before running a production schema change.

Safe schema-change workflow

1. Review the existing definition. Inspect columns, data types, nullability, defaults, indexes, constraints, and generated objects.
2. Search for dependencies. Look through views, procedures, triggers, reports, application code, and integrations that refer to the table or column.
3. Check the data. Find NULL values, oversized strings, invalid formats, and values outside the proposed numeric range.
4. Test in development or staging. Use representative table size and realistic data, not only a small empty table.
5. Plan application changes. Deploy compatible code and schema changes in an order that avoids a period where old queries or new queries fail.
6. Use a migration. Record the change in a version-controlled migration system so environments can be reproduced.
7. Prepare recovery. Use a backup and a rollback or restore plan when the operation and database support it.
8. Plan timing. An alteration may rewrite a table, rebuild indexes, validate every row, or acquire locks. Schedule disruptive work appropriately and investigate online schema-change features where available.
9. Verify afterward. Check the table definition, constraints, row counts, application behavior, and query performance.

Troubleshooting ALTER TABLE errors

NOT NULL alteration fails

Likely cause: Existing rows contain NULL.

Resolution: Populate valid values or otherwise handle the missing values, verify that none remain, and then apply the NOT NULL constraint. Consider a default for future inserts, but do not use a meaningless default merely to hide missing data.

Shortening a text column fails or truncates data

Likely cause: Stored values exceed the proposed maximum length.

Resolution: Find oversized values, clean or migrate them, and test the change. Do not assume that truncation is safe.

DROP COLUMN reports dependent objects

Likely cause: A constraint, index, view, foreign key, generated column, trigger, or application query references the column.

Resolution: Identify dependencies, update or remove them in the correct order, and retry. Preserve any data that must be migrated before deletion.

MODIFY COLUMN produces a syntax error

Likely cause: The database uses a different dialect, such as ALTER COLUMN instead of MODIFY COLUMN.

Resolution: Confirm the database product and version, then use its documented syntax. SQLite may require a table-rebuild migration for changes that other systems perform directly.

ALTER TABLE blocks access or runs for a long time

Likely cause: The database is rewriting the table, validating existing data, rebuilding indexes, waiting for locks, or competing with active transactions.

Resolution: Test with representative data, inspect the operation plan and lock behavior, schedule maintenance appropriately, and use the engine's online or low-lock schema-change options when available.

Exam-relevant notes

• ALTER TABLE changes table structure; UPDATE changes values in existing rows.
• ADD creates a column, while DROP COLUMN removes the column and its stored values.
• NOT NULL requires every row to have a value, so existing data must be compatible before applying it.
• DEFAULT supplies a value when an insert omits a column; treatment of existing rows depends on the database and statement.
• MODIFY COLUMN is common in MySQL, while PostgreSQL, SQL Server, and other systems commonly use forms of ALTER COLUMN.
• Changing a data type can cause conversion errors, truncation, rounding, or data loss.
• Dropping or modifying a column can break dependent database objects and application code.

Summary

Use ALTER TABLE when the definition of an existing table must change. The basic pattern is ALTER TABLE table_name operation, but the operation's syntax depends on the database product. Add columns carefully, remove columns only after checking dependencies and backups, and validate existing data before changing types or imposing constraints.