The alter command in SQL changes a database table's structure without deleting or recreating it. It adds, removes, or modifies columns, constraints, or indexes after the table is created.
Use ALTER TABLE to adjust schema design during development or maintenance. For example, adding a new column to track user preferences doesn't require rebuilding the entire table. This command runs in most SQL databases, including PostgreSQL, MySQL, and Microsoft SQL Server.
According to the ANSI/ISO SQL standard, ALTER TABLE is part of the Data Definition Language (DDL). It modifies metadata, not row data. Changes take effect immediately in supported systems, though some databases require a commit or transaction rollback if the operation fails.
When you run ALTER TABLE, the database engine updates the system catalog-internal tables storing metadata about schemas, tables, and columns. The physical storage may be updated, depending on the database engine and configuration.
Operations like renaming a column or changing a data type often require scanning existing rows to validate compatibility. If a column contains values incompatible with the new type, the command fails unless a conversion rule is specified.
For example, converting a VARCHAR(50) column to INTEGER requires all values to be numeric. If any value is 'abc', the command returns an error. Some databases allow explicit casting during the alteration, such as using CAST() in the ALTER statement.
ALTER TABLE supports several actions: adding columns, dropping columns, modifying column definitions, renaming tables or columns, and managing constraints.
Here's a basic syntax pattern:
ALTER TABLE table_name
ADD COLUMN column_name data_type [constraints];
To rename a column:
ALTER TABLE employees
RENAME COLUMN hire_date TO start_date;
To change a column's data type:
ALTER TABLE products
ALTER COLUMN price TYPE NUMERIC(10,2);
Each database implements these commands slightly differently. PostgreSQL uses ALTER COLUMN, while MySQL uses MODIFY COLUMN. Always check your database's documentation.
| Action | PostgreSQL Syntax | MySQL Syntax |
|---|---|---|
| Add column | ADD COLUMN col_name TYPE | ADD col_name TYPE |
| Change column type | ALTER COLUMN col_name TYPE new_type | MODIFY col_name new_type |
| Rename column | RENAME COLUMN old_name TO new_name | CHANGE old_name new_name |
Some operations are not reversible. Dropping a column deletes both data and metadata. Always back up tables before altering production schemas.
Suppose you manage a retail database and need to track customer loyalty points. The original customers table has:
You want to add a column for loyalty_points. Run this in PostgreSQL:
ALTER TABLE customers
ADD COLUMN loyalty_points INT DEFAULT 0;
This adds a new integer column with default value 0. All existing rows get 0 in this field. Future inserts without specifying loyalty_points will use the default.
Now, suppose you realize the column should be unsigned. You can't change the type directly in MySQL. Instead, you must drop and recreate the column:
ALTER TABLE customers
DROP COLUMN loyalty_points,
ADD COLUMN loyalty_points INT UNSIGNED DEFAULT 0;
Some databases don't support direct type changes. Always test alterations in a staging environment first.
One frequent error is assuming ALTER TABLE can update existing data. It cannot. To set values in a new column, use UPDATE afterward:
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';
UPDATE orders SET status = 'shipped' WHERE shipped_date IS NOT NULL;
Another mistake is forgetting to handle foreign key constraints. If a column being dropped is referenced by another table, the command fails unless you first remove the constraint.
Also, some databases lock the table during ALTER operations. Long-running changes can block concurrent queries. In high-traffic systems, consider using online DDL tools like pt-online-schema-change for MySQL.
A lesser-known fact: ALTER TABLE does not trigger triggers. Unlike INSERT, UPDATE, or DELETE, DDL statements bypass application-level triggers. If you need to log schema changes, implement a custom audit table and manually record each ALTER.
The alter command in SQL is not a data manipulation tool. It does not insert, update, or delete rows. It changes only structure. Misusing it to move data leads to errors or silent failures.
It's also not the same as CREATE or DROP. CREATE builds a new table. DROP removes it entirely. ALTER modifies an existing one. Confusing these causes accidental data loss.
Finally, ALTER TABLE is not always atomic. In some databases, large schema changes may fail mid-process, leaving the table in an inconsistent state. Always verify the result with DESCRIBE or SHOW CREATE TABLE afterward.
Use ALTER TABLE during application development, migration, or maintenance. It's essential when evolving a database schema over time. For example, adding a new field to a user profile table after a feature launch.
It's also useful in data warehousing when optimizing storage. You might alter a column from TEXT to VARCHAR(255) to reduce space usage. Or add a computed column to avoid recalculating values during queries.
I once altered a 10-million-row table in PostgreSQL to add a timestamp index. The operation took 47 minutes and locked writes during that time. Afterward, query performance improved by 60%. This experience taught me to schedule ALTER operations during off-peak hours and monitor locks.
Other related DDL commands include CREATE TABLE, DROP TABLE, and RENAME TABLE. These form the foundation of schema management. Use them together with ALTER to maintain clean, functional databases.
Always document schema changes. Tools like Flyway or Liquibase track ALTER statements in version-controlled scripts. This prevents drift between environments and ensures consistency across dev, staging, and production.
The alter command in sql is a core tool for maintaining flexible, scalable databases. Master its syntax and limitations to avoid downtime and data issues.