Skip to main content

SQL ALTER TABLE for Modifying Tables

Database schemas are never truly finished. Requirements change, features evolve, bugs reveal design flaws, and new business rules demand adjustments. You might need to add an email column that was forgotten during initial design, remove a deprecated field, rename a confusingly named column, change a data type that turned out to be too small, or add a constraint that should have been there from the start.

The SQL ALTER TABLE statement is the command that handles all of these changes. It lets you modify an existing table's structure without destroying the table or losing the data it contains. Understanding how to use ALTER TABLE confidently is essential for every developer, because schema changes are not a one-time event. They happen continuously throughout the life of any application.

This guide covers every major ALTER TABLE operation with practical examples, highlights the syntax differences across databases, and walks through the common mistakes that can lead to data loss or downtime.

The Sample Schema​

All examples start from these tables:

CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    email         VARCHAR(100),
    city          VARCHAR(50)
);

CREATE TABLE products (
    product_id    INT PRIMARY KEY,
    product_name  VARCHAR(100) NOT NULL,
    category      VARCHAR(50),
    price         DECIMAL(10,2) NOT NULL
);

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT NOT NULL,
    order_date    DATE DEFAULT CURRENT_DATE,
    status        VARCHAR(20) DEFAULT 'pending',
    total_amount  DECIMAL(10,2)
);

INSERT INTO customers VALUES (1, 'Alice', 'Martin', 'alice@example.com', 'New York');
INSERT INTO customers VALUES (2, 'Bob',   'Jones',  'bob@example.com',   'Chicago');
INSERT INTO customers VALUES (3, 'Carol', 'Smith',  NULL,                'Denver');

INSERT INTO products VALUES (101, 'Wireless Mouse',     'Electronics', 25.99);
INSERT INTO products VALUES (102, 'USB-C Cable',        'Electronics',  9.99);
INSERT INTO products VALUES (103, 'Notebook A5',        'Stationery',   4.50);

INSERT INTO orders VALUES (1001, 1, '2024-06-15', 'completed',  150.00);
INSERT INTO orders VALUES (1002, 2, '2024-06-18', 'pending',    299.99);
INSERT INTO orders VALUES (1003, 1, '2024-06-20', 'processing', 420.00);

Adding Columns​

The most common ALTER TABLE operation is adding a new column. A new business requirement arrives, and the table needs a field it did not have before.

Basic Syntax​

ALTER TABLE table_name
ADD column_name data_type constraints;

Example: Adding a Single Column​

Add a phone column to the customers table:

ALTER TABLE customers
ADD phone VARCHAR(20);

To verify:

SELECT * FROM customers;
customer_idfirst_namelast_nameemailcityphone
1AliceMartinalice@example.comNew YorkNULL
2BobJonesbob@example.comChicagoNULL
3CarolSmithNULLDenverNULL

The new column exists in every row, populated with NULL because no default was specified.

Adding a Column with a Default Value​

When you need the new column to have a meaningful value in existing rows, specify a DEFAULT:

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

To verify:

SELECT customer_id, first_name, status FROM customers;
customer_idfirst_namestatus
1Aliceactive
2Bobactive
3Carolactive

Every existing row automatically receives the default value 'active'.

warning

Adding a NOT NULL column without a DEFAULT to a table that already contains rows will fail in most databases, because existing rows would have NULL in the new column, violating the constraint immediately.

Fails:

ALTER TABLE customers
ADD loyalty_points INT NOT NULL;
ERROR: column "loyalty_points" of relation "customers" contains null values

Works:

ALTER TABLE customers
ADD loyalty_points INT NOT NULL DEFAULT 0;

Always provide a DEFAULT when adding a NOT NULL column to a populated table.

Adding Multiple Columns​

Some databases allow adding multiple columns in a single statement. The syntax varies:

-- PostgreSQL: multiple ADD in one ALTER TABLE
ALTER TABLE customers
ADD phone      VARCHAR(20),
ADD birth_date DATE,
ADD is_vip     BOOLEAN DEFAULT FALSE;

-- MySQL: multiple ADD in one ALTER TABLE
ALTER TABLE customers
ADD phone      VARCHAR(20),
ADD birth_date DATE,
ADD is_vip     BOOLEAN DEFAULT FALSE;

-- SQL Server: multiple columns in one ADD
ALTER TABLE customers
ADD phone      VARCHAR(20),
    birth_date DATE,
    is_vip     BIT DEFAULT 0;

-- Oracle: parentheses required
ALTER TABLE customers
ADD (
    phone      VARCHAR2(20),
    birth_date DATE,
    is_vip     NUMBER(1) DEFAULT 0
);
tip

Even when your database supports adding multiple columns in one statement, consider whether each column belongs in a separate migration file for better version control tracking. In production environments, each schema change is typically its own migration for clarity and rollback safety.

Controlling Column Position (MySQL)​

By default, new columns are appended at the end of the table. MySQL lets you control placement:

-- Add after a specific column
ALTER TABLE customers
ADD middle_name VARCHAR(50) AFTER first_name;

-- Add as the first column
ALTER TABLE customers
ADD registration_code VARCHAR(20) FIRST;
info

PostgreSQL, SQL Server, and Oracle do not support column positioning in ALTER TABLE. New columns are always appended at the end. The physical position of columns should not matter for your queries (always use explicit column lists instead of relying on SELECT * order), but if column order matters to you visually, the only option in these databases is to recreate the table.

Dropping Columns​

When a column is no longer needed, you can remove it entirely. This is a destructive and irreversible operation: the column and all its data are permanently deleted.

Basic Syntax​

ALTER TABLE table_name
DROP COLUMN column_name;

Example​

Remove the city column from customers:

ALTER TABLE customers
DROP COLUMN city;

To verify:

SELECT * FROM customers;
customer_idfirst_namelast_nameemail
1AliceMartinalice@example.com
2BobJonesbob@example.com
3CarolSmithNULL

The city column and all its data are gone. There is no undo.

Dropping with Dependencies​

If other database objects depend on the column (indexes, constraints, views), the drop may fail:

-- Suppose there's an index on city
CREATE INDEX idx_customers_city ON customers(city);

-- Now try to drop the column
ALTER TABLE customers DROP COLUMN city;
ERROR: cannot drop column city because other objects depend on it

PostgreSQL offers CASCADE to automatically drop dependent objects:

-- PostgreSQL: drop column and all dependent objects
ALTER TABLE customers DROP COLUMN city CASCADE;
danger

CASCADE on a column drop removes the column AND every object that depends on it: indexes, views, constraints, and even columns in other tables if they have foreign keys referencing this column. Always review what will be dropped before using CASCADE.

-- PostgreSQL: see what depends on a column before dropping
SELECT dependent_ns.nspname, dependent_view.relname
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class AS dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_namespace AS dependent_ns ON dependent_view.relnamespace = dependent_ns.oid
WHERE pg_depend.refobjid = 'customers'::regclass;

Dropping Multiple Columns​

-- PostgreSQL / SQL Server
ALTER TABLE customers
DROP COLUMN phone,
DROP COLUMN birth_date;

-- MySQL
ALTER TABLE customers
DROP COLUMN phone,
DROP COLUMN birth_date;

-- Oracle
ALTER TABLE customers
DROP (phone, birth_date);

IF EXISTS Safety​

Some databases support IF EXISTS to avoid errors when a column might not exist:

-- PostgreSQL
ALTER TABLE customers DROP COLUMN IF EXISTS middle_name;
-- No error even if middle_name doesn't exist

-- MySQL (8.0+)
ALTER TABLE customers DROP COLUMN IF EXISTS middle_name;

SQL Server and Oracle do not support IF EXISTS on column drops. Use a conditional check instead:

-- SQL Server
IF EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'customers' AND COLUMN_NAME = 'middle_name'
)
ALTER TABLE customers DROP COLUMN middle_name;

Columns You Cannot Drop​

Certain columns cannot be dropped:

  • The last remaining column in a table (you cannot have a table with zero columns; use DROP TABLE instead).
  • A column that is part of a primary key (drop the primary key constraint first).
  • A column referenced by a foreign key in another table (drop the foreign key first, or use CASCADE).
  • A column used in a computed/generated column expression.
-- Fails: cannot drop a primary key column directly
ALTER TABLE customers DROP COLUMN customer_id;
ERROR: cannot drop column customer_id because other objects depend on it
DETAIL: constraint customers_pkey depends on column customer_id

Fix: Drop the constraint first, then the column:

ALTER TABLE customers DROP CONSTRAINT customers_pkey;
ALTER TABLE customers DROP COLUMN customer_id;

Renaming Columns​

Column names sometimes need to change for clarity, to fix typos, or to align with updated naming conventions.

Syntax by Database​

The syntax for renaming columns differs significantly across databases:

-- PostgreSQL
ALTER TABLE customers
RENAME COLUMN first_name TO given_name;

-- MySQL (8.0+)
ALTER TABLE customers
RENAME COLUMN first_name TO given_name;

-- MySQL (older: requires restating the data type)
ALTER TABLE customers
CHANGE first_name given_name VARCHAR(50) NOT NULL;

-- SQL Server
EXEC sp_rename 'customers.first_name', 'given_name', 'COLUMN';

-- Oracle
ALTER TABLE customers
RENAME COLUMN first_name TO given_name;

Example​

-- PostgreSQL
ALTER TABLE customers
RENAME COLUMN first_name TO given_name;
SELECT * FROM customers;
customer_idgiven_namelast_nameemailcity
1AliceMartinalice@example.comNew York
2BobJonesbob@example.comChicago
3CarolSmithNULLDenver

The column name changed, but all data remains intact.

warning

Renaming a column does not automatically update references to it in:

  • Application code (queries, ORM mappings)
  • Views
  • Stored procedures and functions
  • Triggers
  • Indexes (the index still works but its metadata may show the old name in some databases)

After renaming a column, search your entire codebase for the old name and update every reference. Views and stored procedures that reference the old name will fail at execution time.

-- This view will break after renaming first_name to given_name
CREATE VIEW customer_names AS
SELECT first_name, last_name FROM customers;

-- After the rename:
SELECT * FROM customer_names;
-- ERROR: column "first_name" does not exist

Renaming Tables​

Entire tables can also be renamed.

Syntax by Database​

-- PostgreSQL
ALTER TABLE customers RENAME TO clients;

-- MySQL
ALTER TABLE customers RENAME TO clients;
-- or
RENAME TABLE customers TO clients;

-- SQL Server
EXEC sp_rename 'customers', 'clients';

-- Oracle
ALTER TABLE customers RENAME TO clients;

Example​

ALTER TABLE customers RENAME TO clients;
-- Old name no longer works
SELECT * FROM customers;
-- ERROR: relation "customers" does not exist

-- New name works
SELECT * FROM clients;
customer_idfirst_namelast_nameemailcity
1AliceMartinalice@example.comNew York
2BobJonesbob@example.comChicago
3CarolSmithNULLDenver
danger

Renaming a table has the same ripple effect as renaming a column, but worse. Every query, view, foreign key, trigger, and application reference to the old table name will break. In a production system, renaming a table is a high-risk operation that requires careful coordination.

Safer approach for production: Create a view with the old name that points to the new table. This gives consuming code time to migrate:

ALTER TABLE customers RENAME TO clients;
CREATE VIEW customers AS SELECT * FROM clients;
-- Old code still works via the view while you update references

Modifying Data Types​

Sometimes a column's data type needs to change. A VARCHAR(50) turns out to be too short, an INT should be a BIGINT, or a VARCHAR should become TEXT.

Syntax by Database​

-- PostgreSQL
ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_data_type;

-- MySQL
ALTER TABLE table_name
MODIFY COLUMN column_name new_data_type;

-- SQL Server
ALTER TABLE table_name
ALTER COLUMN column_name new_data_type;

-- Oracle
ALTER TABLE table_name
MODIFY column_name new_data_type;

Example: Widening a VARCHAR​

The email column at VARCHAR(100) is too short for some valid email addresses:

-- PostgreSQL
ALTER TABLE customers
ALTER COLUMN email TYPE VARCHAR(254);

-- MySQL
ALTER TABLE customers
MODIFY COLUMN email VARCHAR(254);

-- SQL Server
ALTER TABLE customers
ALTER COLUMN email VARCHAR(254);

This is a safe, non-destructive change. Making a string column wider never loses data.

Example: Changing INT to BIGINT​

An auto-increment INT primary key is approaching its limit:

-- PostgreSQL
ALTER TABLE orders
ALTER COLUMN order_id TYPE BIGINT;

-- MySQL
ALTER TABLE orders
MODIFY COLUMN order_id BIGINT;

-- SQL Server
ALTER TABLE orders
ALTER COLUMN order_id BIGINT;

Safe vs Risky Type Changes​

Not all type changes are equal. Some are safe (widening), others are risky (narrowing or converting), and some will fail outright:

ChangeRisk LevelNotes
VARCHAR(50) to VARCHAR(100)SafeWidening, no data loss possible
INT to BIGINTSafeWider range, all existing values fit
VARCHAR(100) to VARCHAR(50)RiskyFails if any value exceeds 50 characters
VARCHAR to INTRiskyFails if any value is not a valid integer
DECIMAL(10,2) to DECIMAL(12,2)SafeMore precision, no data loss
DECIMAL(10,2) to DECIMAL(6,2)RiskyFails if values exceed the new precision
INT to BOOLEANRiskyOnly works if all values are 0 or 1
TIMESTAMP to DATERiskyTime component is lost

Handling Type Conversion with USING (PostgreSQL)​

PostgreSQL requires an explicit conversion expression when the types are not directly compatible:

-- Direct conversion fails if data isn't compatible
ALTER TABLE products
ALTER COLUMN price TYPE INT;
ERROR: column "price" cannot be cast automatically to type integer
HINT: You might need to specify "USING price::integer".
-- Provide a conversion expression with USING
ALTER TABLE products
ALTER COLUMN price TYPE INT USING price::INT;

The USING clause tells PostgreSQL how to convert existing values. Without it, PostgreSQL refuses to guess.

More USING examples:

-- Convert a VARCHAR column to INT
ALTER TABLE products
ALTER COLUMN category TYPE INT USING category::INT;
-- Only works if all values in category are valid integers

-- Convert VARCHAR to BOOLEAN
ALTER TABLE products
ALTER COLUMN in_stock TYPE BOOLEAN USING (in_stock = 'true');

-- Round during conversion
ALTER TABLE products
ALTER COLUMN price TYPE INT USING ROUND(price)::INT;
warning

Always verify your data before changing column types. Run a query to find values that would not survive the conversion:

-- Before narrowing VARCHAR(100) to VARCHAR(50), find values that are too long
SELECT customer_id, email, LENGTH(email) AS len
FROM customers
WHERE LENGTH(email) > 50;

-- Before converting VARCHAR to INT, find non-numeric values
SELECT product_id, category
FROM products
WHERE category !~ '^\d+$';   -- PostgreSQL regex for "not all digits"

If any rows would violate the new type, the ALTER fails and no changes are made. Fix the data first, then alter the column.

MySQL: MODIFY vs CHANGE​

MySQL has two commands for modifying columns, and they behave differently:

-- MODIFY: change type and constraints, keep the name
ALTER TABLE customers
MODIFY COLUMN email VARCHAR(254) NOT NULL;

-- CHANGE: change name AND/OR type and constraints
ALTER TABLE customers
CHANGE COLUMN email email_address VARCHAR(254) NOT NULL;

MODIFY preserves the column name. CHANGE lets you rename and retype in one operation but requires you to specify the column name twice (old and new), even if you are not renaming it:

-- CHANGE without renaming (name repeated)
ALTER TABLE customers
CHANGE COLUMN email email VARCHAR(254) NOT NULL;
info

In MySQL, MODIFY and CHANGE require you to restate all column attributes (data type, NOT NULL, DEFAULT, etc.), not just the ones you are changing. If you omit NOT NULL, the column becomes nullable even if it was NOT NULL before.

Accidentally removing NOT NULL:

-- Original: email VARCHAR(100) NOT NULL
ALTER TABLE customers
MODIFY COLUMN email VARCHAR(254);
-- email is now nullable! NOT NULL was silently dropped.

Preserving NOT NULL:

ALTER TABLE customers
MODIFY COLUMN email VARCHAR(254) NOT NULL;

Always check the full column definition before modifying it in MySQL.

Modifying Default Values​

You can add, change, or remove default values on existing columns.

Adding or Changing a Default​

-- PostgreSQL
ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'new';

-- MySQL
ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'new';

-- SQL Server
-- First drop the existing default constraint, then add a new one
ALTER TABLE orders DROP CONSTRAINT DF_orders_status;   -- drop old default
ALTER TABLE orders ADD CONSTRAINT DF_orders_status DEFAULT 'new' FOR status;

-- Oracle
ALTER TABLE orders
MODIFY status DEFAULT 'new';

Removing a Default​

-- PostgreSQL
ALTER TABLE orders
ALTER COLUMN status DROP DEFAULT;

-- MySQL
ALTER TABLE orders
ALTER COLUMN status DROP DEFAULT;

-- SQL Server
ALTER TABLE orders DROP CONSTRAINT DF_orders_status;

-- Oracle
ALTER TABLE orders
MODIFY status DEFAULT NULL;

Adding or Removing NOT NULL​

-- PostgreSQL
ALTER TABLE customers ALTER COLUMN email SET NOT NULL;
ALTER TABLE customers ALTER COLUMN email DROP NOT NULL;

-- MySQL (requires restating the full column definition)
ALTER TABLE customers MODIFY COLUMN email VARCHAR(100) NOT NULL;
ALTER TABLE customers MODIFY COLUMN email VARCHAR(100) NULL;

-- SQL Server
ALTER TABLE customers ALTER COLUMN email VARCHAR(100) NOT NULL;
ALTER TABLE customers ALTER COLUMN email VARCHAR(100) NULL;

-- Oracle
ALTER TABLE customers MODIFY email NOT NULL;
ALTER TABLE customers MODIFY email NULL;
warning

Setting a column to NOT NULL will fail if any existing rows contain NULL in that column:

ALTER TABLE customers ALTER COLUMN email SET NOT NULL;
ERROR: column "email" of relation "customers" contains null values

Fix the data first:

-- Find NULLs
SELECT customer_id FROM customers WHERE email IS NULL;

-- Fill them in
UPDATE customers SET email = 'unknown@placeholder.com' WHERE email IS NULL;

-- Now the ALTER succeeds
ALTER TABLE customers ALTER COLUMN email SET NOT NULL;

Adding and Removing Constraints​

Constraints can be added to or removed from existing tables. This is one of the most common uses of ALTER TABLE in practice, especially during schema migrations.

Adding a Primary Key​

-- Table was created without a primary key
CREATE TABLE tags (
    tag_id   INT,
    tag_name VARCHAR(50)
);

-- Add the primary key later
ALTER TABLE tags
ADD CONSTRAINT tags_pk PRIMARY KEY (tag_id);

Removing a Primary Key​

-- PostgreSQL / SQL Server / Oracle
ALTER TABLE tags DROP CONSTRAINT tags_pk;

-- MySQL
ALTER TABLE tags DROP PRIMARY KEY;

Adding a Foreign Key​

ALTER TABLE orders
ADD CONSTRAINT orders_customer_id_fk
    FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;

Removing a Foreign Key​

-- PostgreSQL / SQL Server / Oracle
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fk;

-- MySQL
ALTER TABLE orders DROP FOREIGN KEY orders_customer_id_fk;

Adding a UNIQUE Constraint​

ALTER TABLE customers
ADD CONSTRAINT customers_email_uq UNIQUE (email);

This will fail if duplicate values already exist:

-- Check for duplicates first
SELECT email, COUNT(*)
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Removing a UNIQUE Constraint​

-- PostgreSQL / SQL Server / Oracle
ALTER TABLE customers DROP CONSTRAINT customers_email_uq;

-- MySQL (unique constraints are implemented as indexes)
ALTER TABLE customers DROP INDEX customers_email_uq;

Adding a CHECK Constraint​

ALTER TABLE products
ADD CONSTRAINT products_price_ck CHECK (price >= 0);

ALTER TABLE orders
ADD CONSTRAINT orders_status_ck
    CHECK (status IN ('pending', 'processing', 'shipped', 'completed', 'cancelled'));

Removing a CHECK Constraint​

-- PostgreSQL / SQL Server / Oracle
ALTER TABLE products DROP CONSTRAINT products_price_ck;

-- MySQL 8.0.16+
ALTER TABLE products DROP CHECK products_price_ck;
tip

Always name your constraints when creating them. This makes them easy to reference when you need to drop or modify them later:

-- Easy to drop later because you know the name
ALTER TABLE orders ADD CONSTRAINT orders_status_ck
    CHECK (status IN ('pending', 'processing', 'shipped'));

ALTER TABLE orders DROP CONSTRAINT orders_status_ck;

-- If unnamed, you must look up the auto-generated name first
SELECT constraint_name
FROM information_schema.table_constraints
WHERE table_name = 'orders' AND constraint_type = 'CHECK';

Replacing a Constraint​

To modify a constraint (e.g., adding a new valid status value), you must drop the old one and create a new one. There is no ALTER CONSTRAINT syntax:

-- Remove the old constraint
ALTER TABLE orders DROP CONSTRAINT orders_status_ck;

-- Add the updated constraint with a new valid value
ALTER TABLE orders ADD CONSTRAINT orders_status_ck
    CHECK (status IN ('pending', 'processing', 'shipped', 'completed', 'cancelled', 'refunded'));

Multiple Changes in One Statement​

Some databases allow combining multiple ALTER TABLE operations into a single statement:

-- PostgreSQL: multiple operations separated by commas
ALTER TABLE customers
    ADD COLUMN phone VARCHAR(20),
    ADD COLUMN birth_date DATE,
    ALTER COLUMN email TYPE VARCHAR(254),
    ALTER COLUMN email SET NOT NULL,
    DROP COLUMN city;

-- MySQL: multiple operations separated by commas
ALTER TABLE customers
    ADD COLUMN phone VARCHAR(20),
    ADD COLUMN birth_date DATE,
    MODIFY COLUMN email VARCHAR(254) NOT NULL,
    DROP COLUMN city;
-- SQL Server: does NOT support combining different operation types
-- Each must be a separate statement
ALTER TABLE customers ADD phone VARCHAR(20);
ALTER TABLE customers ADD birth_date DATE;
ALTER TABLE customers ALTER COLUMN email VARCHAR(254) NOT NULL;
ALTER TABLE customers DROP COLUMN city;
info

Combining operations into a single ALTER TABLE statement is more efficient because the database modifies the table structure once instead of multiple times. On large tables, each ALTER TABLE may require a table rewrite, so combining changes avoids repeating that expensive process.

Real-World Migration Example​

Here is a realistic scenario: you need to evolve the customers table to support a new feature. The migration adds new columns, adjusts existing ones, and adds constraints:

-- Step 1: Verify current state
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'customers'
ORDER BY ordinal_position;

-- Step 2: Apply changes inside a transaction
BEGIN;

-- Add new columns
ALTER TABLE customers
    ADD COLUMN phone VARCHAR(20),
    ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active',
    ADD COLUMN created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

-- Widen email column and make it required
ALTER TABLE customers
    ALTER COLUMN email TYPE VARCHAR(254);

-- Fill in NULL emails before adding NOT NULL
UPDATE customers
SET email = CONCAT('user_', customer_id, '@placeholder.com')
WHERE email IS NULL;

-- Now safe to add NOT NULL
ALTER TABLE customers
    ALTER COLUMN email SET NOT NULL;

-- Add constraints
ALTER TABLE customers
    ADD CONSTRAINT customers_email_uq UNIQUE (email);

ALTER TABLE customers
    ADD CONSTRAINT customers_status_ck
    CHECK (status IN ('active', 'inactive', 'suspended'));

-- Step 3: Verify the result
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'customers'
ORDER BY ordinal_position;

-- Step 4: Commit if everything looks correct
COMMIT;
-- or ROLLBACK if something went wrong
SELECT * FROM customers;
customer_idfirst_namelast_nameemailcityphonestatuscreated_at
1AliceMartinalice@example.comNew YorkNULLactive2024-06-15 10:00:00
2BobJonesbob@example.comChicagoNULLactive2024-06-15 10:00:00
3CarolSmithuser_3@placeholder.comDenverNULLactive2024-06-15 10:00:00
tip

Always run schema migrations inside a transaction (in databases that support transactional DDL like PostgreSQL). If any step fails, the entire migration rolls back and the table remains in its original state. MySQL does not support transactional DDL. Each ALTER TABLE is auto-committed immediately and cannot be rolled back.

Common Mistakes​

Mistake 1: Adding NOT NULL Without a Default to a Populated Table​

This is the most frequent ALTER TABLE error:

Fails:

ALTER TABLE customers ADD COLUMN loyalty_tier VARCHAR(20) NOT NULL;
ERROR: column "loyalty_tier" of relation "customers" contains null values

Existing rows have no value for the new column, which means they would be NULL, violating the NOT NULL constraint.

Correct approach:

-- Option A: Add with a default
ALTER TABLE customers
ADD COLUMN loyalty_tier VARCHAR(20) NOT NULL DEFAULT 'bronze';

-- Option B: Add as nullable, fill data, then set NOT NULL
ALTER TABLE customers ADD COLUMN loyalty_tier VARCHAR(20);
UPDATE customers SET loyalty_tier = 'bronze';
ALTER TABLE customers ALTER COLUMN loyalty_tier SET NOT NULL;

Mistake 2: Narrowing a Column Without Checking Data​

Fails if data exceeds new limit:

-- Current: email VARCHAR(254)
-- Alice's email is 'alice@example.com' (17 characters)
-- But what if another row has an 80-character email?

ALTER TABLE customers
MODIFY COLUMN email VARCHAR(50);
ERROR: value too long for type character varying(50)

Safe approach:

-- Check max length first
SELECT MAX(LENGTH(email)) AS max_len FROM customers;
-- Result: 22. Safe to narrow to 50.

ALTER TABLE customers ALTER COLUMN email TYPE VARCHAR(50);

Mistake 3: Dropping a Column Referenced by Other Objects​

Fails when dependencies exist:

-- A view depends on the city column
CREATE VIEW customer_cities AS
SELECT first_name, city FROM customers;

ALTER TABLE customers DROP COLUMN city;
ERROR: cannot drop column city because other objects depend on it
DETAIL: view customer_cities depends on column city

Fix: Drop the dependent objects first (or use CASCADE in PostgreSQL):

-- Option A: Drop the view, then the column
DROP VIEW customer_cities;
ALTER TABLE customers DROP COLUMN city;

-- Option B: CASCADE (PostgreSQL, drops the view automatically)
ALTER TABLE customers DROP COLUMN city CASCADE;

Mistake 4: Forgetting That MySQL MODIFY Restates Everything​

In MySQL, MODIFY requires the complete column definition, not just the part you want to change. Omitting attributes silently removes them:

Silently removes NOT NULL:

-- Before: email VARCHAR(100) NOT NULL DEFAULT 'none'
ALTER TABLE customers MODIFY COLUMN email VARCHAR(254);
-- After: email VARCHAR(254) NULL (no default!)
-- NOT NULL and DEFAULT were both silently dropped

Preserves all attributes:

ALTER TABLE customers MODIFY COLUMN email VARCHAR(254) NOT NULL DEFAULT 'none';
danger

This is one of the most insidious MySQL behaviors. There is no warning, and no error. The column silently loses its constraints. Always check the full column definition with SHOW CREATE TABLE before and after a MODIFY in MySQL.

-- MySQL: verify the full column definition
SHOW CREATE TABLE customers\G

Mistake 5: Altering a Column That Is Part of an Index​

Changing the data type of an indexed column may require the index to be rebuilt. Most databases handle this automatically, but the operation can be extremely slow on large tables:

-- This column has an index
CREATE INDEX idx_orders_date ON orders(order_date);

-- Changing its type forces an index rebuild
ALTER TABLE orders ALTER COLUMN order_date TYPE TIMESTAMP;
-- May take minutes on large tables

On tables with millions of rows, plan type changes during maintenance windows and consider the impact on indexes.

Mistake 6: Renaming Without Updating Dependencies​

Renaming a column or table without updating all references is a guaranteed way to break your application:

ALTER TABLE customers RENAME COLUMN first_name TO given_name;

Now every query, view, stored procedure, trigger, and application code that references first_name is broken. Before renaming, search for all references:

-- PostgreSQL: find views that reference the column
SELECT viewname, definition
FROM pg_views
WHERE definition LIKE '%first_name%';

-- Check application code
-- grep -r "first_name" ./src/

Performance Impact of ALTER TABLE​

Schema changes on large tables can be slow and lock-intensive. Understanding the performance characteristics helps you plan changes without causing downtime.

Operations That Are Usually Fast (Metadata-Only)​

These operations modify the table's catalog entry without touching row data:

OperationPostgreSQLMySQLSQL Server
Add nullable column without defaultFastFastFast
Drop columnFast (marks invisible)Rewrites tableFast
Rename columnFastFastFast
Add/drop constraint (CHECK, FK)Fast (validation may take time)DependsFast
Change default valueFastFastFast

Operations That Can Be Slow (Table Rewrite)​

These operations may need to read and rewrite every row:

OperationPostgreSQLMySQLSQL Server
Add column with non-null defaultFast (11+)Rewrites tableRewrites table
Change data typeRewrites tableRewrites tableRewrites table
Add NOT NULL to existing columnScans for NULLsRewrites tableScans for NULLs
tip

For large production tables (millions of rows), consider tools designed for online schema changes that avoid locking:

  • PostgreSQL: pg_repack, or native ALTER TABLE (most operations are non-blocking since PostgreSQL 11)
  • MySQL: pt-online-schema-change (Percona), gh-ost (GitHub), or MySQL 8's native online DDL
  • SQL Server: Online index operations, ALTER TABLE ... WITH (ONLINE = ON) (Enterprise edition)

These tools create a shadow copy of the table, apply changes to the copy, replay any concurrent modifications, and then swap the tables atomically.

Database Compatibility Quick Reference​

OperationPostgreSQLMySQLSQL ServerOracle
Add columnADD column_name typeADD column_name typeADD column_name typeADD (column_name type)
Drop columnDROP COLUMN nameDROP COLUMN nameDROP COLUMN nameDROP (name)
Rename columnRENAME COLUMN old TO newRENAME COLUMN old TO new (8.0+)sp_renameRENAME COLUMN old TO new
Rename tableRENAME TO new_nameRENAME TO new_namesp_renameRENAME TO new_name
Change typeALTER COLUMN col TYPE typeMODIFY COLUMN col typeALTER COLUMN col typeMODIFY col type
Set NOT NULLALTER COLUMN col SET NOT NULLMODIFY COLUMN col type NOT NULLALTER COLUMN col type NOT NULLMODIFY col NOT NULL
Drop NOT NULLALTER COLUMN col DROP NOT NULLMODIFY COLUMN col type NULLALTER COLUMN col type NULLMODIFY col NULL
Set defaultALTER COLUMN col SET DEFAULT valALTER COLUMN col SET DEFAULT valNamed constraint requiredMODIFY col DEFAULT val
Drop defaultALTER COLUMN col DROP DEFAULTALTER COLUMN col DROP DEFAULTDrop named constraintMODIFY col DEFAULT NULL
Add constraintADD CONSTRAINT name ...ADD CONSTRAINT name ...ADD CONSTRAINT name ...ADD CONSTRAINT name ...
Drop constraintDROP CONSTRAINT nameDROP CONSTRAINT name or DROP INDEX/CHECK/FOREIGN KEYDROP CONSTRAINT nameDROP CONSTRAINT name
IF EXISTS (column)SupportedSupported (8.0+)Conditional checkNot supported
Multiple operationsComma-separatedComma-separatedSeparate statementsLimited

Summary​

The SQL ALTER TABLE statement is the tool for evolving your database schema after tables have been created. It handles structural changes ranging from simple column additions to complex type conversions and constraint modifications.

Key takeaways:

  • Adding columns is the most common operation. Always provide a DEFAULT when adding a NOT NULL column to a table that already contains data.
  • Dropping columns is permanent and irreversible. Check for dependent objects (views, indexes, foreign keys) before dropping, and consider using IF EXISTS for safety.
  • Renaming columns and tables changes the identifier but does not update any references in views, stored procedures, triggers, or application code. Search for and update all references before or immediately after renaming.
  • Modifying data types is safe when widening (e.g., VARCHAR(50) to VARCHAR(100)) but risky when narrowing or converting between incompatible types. Always check existing data before altering the type. PostgreSQL requires a USING clause for non-trivial conversions.
  • Adding and removing constraints follows a drop-then-add pattern since there is no ALTER CONSTRAINT syntax. Always name your constraints to make them easy to reference later.
  • Syntax varies significantly across databases, especially for column renaming, type modification, and NOT NULL changes. MySQL's MODIFY is particularly dangerous because it requires restating the full column definition and silently drops omitted attributes.
  • Use transactions for schema migrations (where supported) so you can roll back if something goes wrong. Always verify data compatibility before applying changes.
  • Large tables may require special tools or maintenance windows for schema changes that trigger table rewrites. Understand which operations are metadata-only (fast) versus full rewrites (slow) for your specific database.

Schema evolution is an ongoing process. Master ALTER TABLE, and you can confidently adapt your database to any new requirement without starting from scratch.

Table of Contents