ALTER

Hook: ALTER is the SQL version of remodeling a house: you keep the structure, but change the rooms, wiring, or rules.

Question: What is ALTER in SQL?

Answer: ALTER is a DDL statement, meaning a command that changes the database schema, or structure. Most often you use it with ALTER TABLE to add, remove, rename, or modify columns and constraints. It does not change row values directly like UPDATE; it changes the definition of the table itself.

Interview-Ready Answer: I use ALTER when I need to change an existing database object without recreating it. In practice, that usually means ALTER TABLE to add a column, rename a column, change a data type, or add a constraint. A key detail is that some ALTER operations are just metadata changes and are very fast, while others may lock or rewrite a large table, so I always think about production impact too.

What ALTER really means

ALTER belongs to DDL, which stands for Data Definition Language. That means it changes the shape of the database, not the rows themselves. The shape is called the schema (the structure of tables, columns, indexes, and constraints). Databases keep this structure in internal metadata and a catalog (the database’s own record of objects and rules).

Under the hood: what happens when you run ALTER

1. The database parses the statement and identifies the object, such as a table, column, index, or constraint.
2. It checks permissions and confirms the object exists.
3. It takes a lock. A lock is a guard that prevents conflicting changes while the schema is being updated.
4. It validates the new definition against existing data when needed. For example, making a column NOT NULL forces the database to verify that no row contains NULL.
5. It updates system catalog entries. For small changes, this may be almost instant. For bigger changes, the engine may rewrite table storage or rebuild dependent objects.
6. It releases the lock, and the new schema becomes visible to other sessions.

That mental model matters in interviews: ALTER is often not about touching every row. Sometimes it is just changing the blueprint. Other times the engine must physically reshape the table, which is why some ALTERs are cheap and some are expensive.

Common ALTER forms and how they differ

Inside ALTER TABLE, the most common actions are:

• ADD COLUMN — add a new field.
• ALTER COLUMN — change type, default, nullability, or constraints.
• RENAME COLUMN or RENAME TABLE — change names without changing data.
• DROP COLUMN — remove a column definition.
• ADD CONSTRAINT / DROP CONSTRAINT — add rules like PRIMARY KEY, FOREIGN KEY, or CHECK.

When and why to use ALTER

1. Use it for schema evolution. When the app grows, you may need new columns, stronger constraints, or renamed fields.
2. Use it to improve data quality. For example, adding a CHECK constraint stops bad values at the door.
3. Use it carefully in production. Some ALTERs are metadata-only and finish quickly; others need table scans, row validation, or a full rewrite.
4. Prefer safe rollout patterns. A common production pattern is: add nullable column, backfill, then set NOT NULL. That avoids breaking existing rows.

Performance and version notes

Performance is the big interview trap. A change that looks tiny in SQL can be huge on a large table.

• Adding a column with no default is usually cheap.
• In PostgreSQL 11 and later, adding a column with a constant default is often metadata-only, so it can be very fast. Older PostgreSQL versions rewrote the whole table.
• Changing a column type may require a table scan or rewrite, especially if the old and new types are not naturally compatible.
• Some systems hold stronger locks than others. In PostgreSQL, many ALTER TABLE operations are transactional, but they still may block concurrent writes. In other databases, some DDL may auto-commit.

Rule of thumb: if the change can be proven safe from existing data, it is usually cheaper. If the engine must inspect or transform every row, it becomes expensive.

Important edge cases

• NOT NULL on a populated column: it fails unless every row already has a value.
• Type changes: if the database cannot infer a safe conversion, you need a conversion rule such as PostgreSQL’s USING clause.
• Dropping columns: the name may disappear immediately, but physical storage reclamation can be delayed depending on the engine.
• Dependencies: views, indexes, triggers, and constraints may need to be updated or rebuilt.

Memory shortcut: think schema surgery. ALTER is not writing new patient data; it is changing the body plan of the table, and that can be quick or delicate depending on the operation.

Real-world story

Imagine a checkout service for an e-commerce site. Product wants a new order_status field and later a stricter rule that every order must have a status. The team uses ALTER TABLE to add the column first, backfills old orders, and only then adds NOT NULL and a CHECK constraint.

What goes wrong when someone skips that safety plan? The deploy adds NOT NULL directly on a table with millions of existing rows. The database must validate the whole table, takes a heavy lock, and the app starts timing out because checkout requests wait behind the schema change. In logs, you might see lock waits like waiting for AccessExclusiveLock, slower API responses, and a spike in abandoned carts. The bug is not the SQL syntax itself; it is misunderstanding the operational cost of the ALTER.

Follow-up & Tricky Questions:

• How is ALTER different from UPDATE? ALTER changes the table definition, while UPDATE changes the data stored in rows. If you need a new rule or column, use ALTER; if you need to change values, use UPDATE.
• Does ALTER TABLE always lock the table? Yes, but the lock strength and duration vary by database and by operation. Small metadata-only changes may be brief, while validation or rewrites can block reads or writes longer.
• Can ALTER be rolled back? In PostgreSQL, many DDL statements are transactional, so a rollback can undo them. In other systems, DDL may auto-commit, so you cannot assume rollback support everywhere.
• What happens to indexes and constraints when a column changes? Some changes automatically update dependencies, but others force rebuilds or fail if the dependency is incompatible. A type change or column drop is especially likely to affect dependent objects.
• How do you safely add NOT NULL in production? First add the column as nullable, backfill existing rows, verify no nulls remain, and then add NOT NULL. That avoids failing on old data and reduces lock risk.
• Is ALTER TABLE ... ADD COLUMN ... DEFAULT ... always expensive? No. In PostgreSQL 11+, a constant default is often fast and metadata-only. But older versions, volatile defaults, or other databases may still rewrite the table.
• Does DROP COLUMN immediately free disk space? Not always. The column may disappear from the schema right away, but physical space can be reclaimed later by the engine’s storage cleanup process or a rewrite.

Common Mistakes:

• Thinking ALTER changes row data. Correction: it changes schema; use UPDATE for row values.
• Adding NOT NULL before backfilling existing rows. Correction: clean the data first, then tighten the constraint.
• Ignoring lock and rewrite cost on large tables. Correction: test on a clone, check the query plan or migration notes, and schedule carefully.
• Assuming every database supports the same ALTER syntax. Correction: syntax and behavior vary by engine, so always verify the target dialect.

Memory Hook: Create builds the house, ALTER renovates it, UPDATE changes the furniture, DROP bulldozes it.

Cheat Sheet:

• ALTER = change an existing database object.
• Most common form: ALTER TABLE.
• It can add, rename, modify, or drop columns and constraints.
• Some ALTERs are metadata-only; others scan or rewrite data.
• Production-safe pattern: add nullable column, backfill, then constrain.
• Dialect matters: PostgreSQL, MySQL, SQL Server, and Oracle differ.

Practice Tasks:

• Create a temp table, then use ALTER TABLE to add a column and rename another column.
• Insert sample rows, backfill a new column, then add NOT NULL and a CHECK constraint.
• Try an invalid insert after the constraint is added and observe the failure message.