SQL Database Objects
ALTER TABLE Statement
Modify your table structure without losing data β add columns, drop columns, change types, and manage constraints.
- ADD COLUMN β Introduce new fields to existing tables
- DROP COLUMN β Remove fields you no longer need
- ALTER COLUMN β Change data types, defaults, and constraints Schema evolves β ALTER TABLE lets your database evolve with it.
What Is ALTER TABLE?
DfALTER TABLE
ALTER TABLE is a DDL (Data Definition Language) statement that modifies the structure of an existing table. It allows you to add, drop, rename, or change columns and constraints without recreating the table or losing data.
-- Basic syntax
ALTER TABLE table_name
ADD | DROP | ALTER | RENAME ...
Adding Columns
-- Add a single column
ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);
-- Add multiple columns (PostgreSQL, MySQL)
ALTER TABLE employees
ADD COLUMN middle_name VARCHAR(50),
ADD COLUMN suffix VARCHAR(10),
ADD COLUMN hire_date DATE NOT NULL DEFAULT CURRENT_DATE;
-- Add column with a constraint
ALTER TABLE employees
ADD COLUMN age INTEGER CHECK (age >= 18);
-- SQL Server: Add column (no COLUMN keyword)
ALTER TABLE employees
ADD phone VARCHAR(20);
When adding a NOT NULL column to an existing table, always provide a DEFAULT value. Otherwise, the statement fails because existing rows would have NULL in the new column.
Dropping Columns
-- Drop a single column
ALTER TABLE employees
DROP COLUMN suffix;
-- Drop multiple columns
ALTER TABLE employees
DROP COLUMN middle_name,
DROP COLUMN suffix;
-- Drop column only if it exists (PostgreSQL)
ALTER TABLE employees
DROP COLUMN IF EXISTS suffix;
-- SQL Server: Drop column only if it exists
IF EXISTS (SELECT * FROM sys.columns WHERE name = 'suffix' AND object_id = OBJECT_ID('employees'))
ALTER TABLE employees
DROP COLUMN suffix;
-- Drop column with CASCADE (removes dependent objects)
ALTER TABLE employees
DROP COLUMN employee_id CASCADE;
Dropping a column that is referenced by a FOREIGN KEY or VIEW will fail. Drop the dependent objects first, or use CASCADE to remove them automatically.
Renaming Columns
-- PostgreSQL and MySQL
ALTER TABLE employees
RENAME COLUMN phone TO phone_number;
-- SQL Server
EXEC sp_rename 'employees.phone', 'phone_number', 'COLUMN';
-- Oracle
ALTER TABLE employees
RENAME COLUMN phone TO phone_number;
Changing Column Data Types
-- Change data type (PostgreSQL)
ALTER TABLE employees
ALTER COLUMN phone TYPE VARCHAR(30);
-- Change data type (MySQL)
ALTER TABLE employees
MODIFY COLUMN phone VARCHAR(30);
-- SQL Server: Change data type
ALTER TABLE employees
ALTER COLUMN phone VARCHAR(30);
-- Change column type with default (MySQL)
ALTER TABLE employees
MODIFY COLUMN status VARCHAR(20) NOT NULL DEFAULT 'Active';
Adding and Dropping Constraints
Adding Constraints
-- Add NOT NULL constraint
ALTER TABLE employees
ALTER COLUMN email SET NOT NULL;
-- Add UNIQUE constraint
ALTER TABLE employees
ADD CONSTRAINT uq_employees_email UNIQUE (email);
-- Add CHECK constraint
ALTER TABLE employees
ADD CONSTRAINT chk_salary_positive CHECK (salary > 0);
-- Add FOREIGN KEY constraint
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
-- Add PRIMARY KEY (if table doesn't have one)
ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id);
Dropping Constraints
-- Drop a named constraint
ALTER TABLE employees
DROP CONSTRAINT chk_salary_positive;
-- Drop NOT NULL constraint (PostgreSQL)
ALTER TABLE employees
ALTER COLUMN email DROP NOT NULL;
-- Drop PRIMARY KEY (MySQL)
ALTER TABLE employees
DROP PRIMARY KEY;
-- Drop FOREIGN KEY (MySQL β need to drop index too)
ALTER TABLE orders
DROP FOREIGN KEY fk_orders_customer;
Modifying DEFAULT Values
-- Set a new default (PostgreSQL)
ALTER TABLE employees
ALTER COLUMN status SET DEFAULT 'Active';
-- Remove a default (PostgreSQL)
ALTER TABLE employees
ALTER COLUMN status DROP DEFAULT;
-- Set default (SQL Server)
ALTER TABLE employees
ADD CONSTRAINT df_status DEFAULT 'Active' FOR status;
-- Set default (MySQL)
ALTER TABLE employees
ALTER COLUMN status SET DEFAULT 'Active';
Renaming Tables
-- PostgreSQL and MySQL
ALTER TABLE employees
RENAME TO staff;
-- SQL Server
EXEC sp_rename 'employees', 'staff';
-- Oracle
RENAME employees TO staff;
Complete Table Migration Example
-- Start with the original table
-- employees: id, name, dept, salary
-- Step 1: Add missing columns
ALTER TABLE employees
ADD COLUMN email VARCHAR(100),
ADD COLUMN phone VARCHAR(20),
ADD COLUMN hire_date DATE DEFAULT CURRENT_DATE;
-- Step 2: Rename for consistency
ALTER TABLE employees
RENAME COLUMN name TO full_name;
-- Step 3: Add constraints
ALTER TABLE employees
ADD CONSTRAINT uq_emp_email UNIQUE (email),
ALTER COLUMN email SET NOT NULL,
ADD CONSTRAINT chk_emp_salary CHECK (salary > 0);
-- Step 4: Drop obsolete column
ALTER TABLE employees
DROP COLUMN dept;
-- Step 5: Change type for precision
ALTER TABLE employees
ALTER COLUMN salary TYPE DECIMAL(12,2);
ALTER TABLE Operations by Database
| Operation | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|
| ADD COLUMN | β | β | β | β |
| DROP COLUMN | β | β | β | β |
| RENAME COLUMN | β | β (8.0+) | β οΈ sp_rename | β |
| ALTER TYPE | β | β MODIFY | β ALTER | β |
| ADD CONSTRAINT | β | β | β | β |
| DROP CONSTRAINT | β | β | β | β |
| SET DEFAULT | β | β | β (ADD DF) | β |
| DROP DEFAULT | β | β | β | β |
| RENAME TABLE | β | β | β οΈ sp_rename | β |
| ADD NOT NULL | β SET NOT NULL | β MODIFY | β ALTER | β |
| DROP NOT NULL | β DROP NOT NULL | β MODIFY | β ALTER | β |
| IF EXISTS | β | β | β (IF EXISTS) | β |
Performance Considerations
DfSchema Change Performance
ALTER TABLE acquires a table lock on most databases. On large tables, this can block all reads and writes for the duration of the change. Many databases now support online DDL that avoids this.
| Operation | Lock Level | Online DDL? |
|---|---|---|
| ADD COLUMN (nullable, no default) | Metadata only | β Fast on all databases |
| ADD COLUMN (NOT NULL with default) | May rewrite table | β οΈ Varies by database |
| DROP COLUMN | Metadata only (PostgreSQL) | β |
| ALTER TYPE | May rewrite table | β οΈ Varies by database |
| ADD/DROP CONSTRAINT | Depends on constraint type | β οΈ Varies |
On PostgreSQL, adding a nullable column with no default is an instant metadata-only operation. On MySQL 8.0+, many ALTER TABLE operations use instant DDL. Always check your database's documentation for online DDL support.
Common Pitfalls
| Pitfall | Problem | Solution |
|---|---|---|
| Adding NOT NULL without DEFAULT | Statement fails on existing data | Always provide a DEFAULT or make nullable first |
| Dropping a referenced column | Foreign key violation | Drop dependent constraints first |
| Changing type on large table | Full table rewrite, long lock | Use online DDL or a migration tool |
| Renaming a column used in views | Views break | Update views or use DROP/CREATE pattern |
| Missing IF EXISTS | Statement fails if object doesn't exist | Use IF EXISTS / IF NOT EXISTS clauses |
Best Practices
| Practice | Why |
|---|---|
| Back up before major schema changes | Rollback safety for destructive operations |
| Use IF EXISTS / IF NOT EXISTS | Avoid errors when objects may not exist |
| Name all constraints explicitly | Readable error messages and easier maintenance |
| Test on a copy first | Verify the migration works before touching production |
| Use migration scripts | Version control your schema changes |
| Add nullable columns first, then set NOT NULL | Avoids rewrite on large tables |
| Check online DDL support | Minimize lock time on production tables |
Key Takeaways
- ALTER TABLE modifies existing table structure β add, drop, rename, and change columns and constraints
- Always provide a DEFAULT when adding NOT NULL columns to tables with existing data
- Renaming columns or tables breaks views, procedures, and application code β update all dependents
- Schema changes can lock tables β use online DDL when available on large tables
- Use migration scripts and version control for all schema changes in production