ALTER TABLE

Overview

The ALTER TABLE statement is used to modify an existing table's structure. It allows you to add, delete, or modify columns and constraints without recreating the table.

ALTER TABLE is essential for database evolution, allowing you to adapt table structures as requirements change.

Basic Syntax

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Adding Columns

Example 1: Add a Single Column

ALTER TABLE Customers
ADD phone VARCHAR(20);

Example 2: Add Column with Default Value

ALTER TABLE Customers
ADD status VARCHAR(20) DEFAULT 'Active';

Example 3: Add Multiple Columns

ALTER TABLE Customers
ADD phone VARCHAR(20),
ADD address VARCHAR(200),
ADD city VARCHAR(50);

Example 4: Add Column at Specific Position

ALTER TABLE Customers
ADD phone VARCHAR(20) AFTER email;

Dropping Columns

Example 1: Drop a Column

ALTER TABLE Customers
DROP COLUMN phone;

Example 2: Drop Multiple Columns

ALTER TABLE Customers
DROP COLUMN phone,
DROP COLUMN address;

Modifying Columns

Example 1: Change Data Type

-- MySQL
ALTER TABLE Customers
MODIFY COLUMN phone VARCHAR(30);

-- SQL Server
ALTER TABLE Customers
ALTER COLUMN phone VARCHAR(30);

-- PostgreSQL
ALTER TABLE Customers
ALTER COLUMN phone TYPE VARCHAR(30);

Example 2: Change Column Name

-- MySQL
ALTER TABLE Customers
CHANGE COLUMN phone contact_phone VARCHAR(20);

-- SQL Server
EXEC sp_rename 'Customers.phone', 'contact_phone', 'COLUMN';

-- PostgreSQL
ALTER TABLE Customers
RENAME COLUMN phone TO contact_phone;

Adding Constraints

Example 1: Add Primary Key

ALTER TABLE Customers
ADD PRIMARY KEY (customer_id);

Example 2: Add Foreign Key

ALTER TABLE Orders
ADD FOREIGN KEY (customer_id) REFERENCES Customers(customer_id);

Example 3: Add NOT NULL Constraint

ALTER TABLE Customers
MODIFY COLUMN email VARCHAR(100) NOT NULL;

Example 4: Add UNIQUE Constraint

ALTER TABLE Customers
ADD UNIQUE (email);

Example 5: Add CHECK Constraint

ALTER TABLE Products
ADD CHECK (price > 0);

Dropping Constraints

Example 1: Drop Primary Key

ALTER TABLE Customers
DROP PRIMARY KEY;

Example 2: Drop Foreign Key

ALTER TABLE Orders
DROP FOREIGN KEY fk_customer;

Example 3: Drop Unique Constraint

ALTER TABLE Customers
DROP INDEX email;

Renaming Tables

You can rename tables using ALTER TABLE or RENAME (syntax varies by database):

-- MySQL
ALTER TABLE Customers RENAME TO Clients;

-- SQL Server
EXEC sp_rename 'Customers', 'Clients';

-- PostgreSQL
ALTER TABLE Customers RENAME TO Clients;

Common Use Cases

• Adding new columns as requirements evolve
• Removing obsolete columns
• Changing data types to accommodate new data
• Adding constraints for data integrity
• Modifying column properties (NOT NULL, DEFAULT, etc.)

Important Considerations

• Data compatibility: Ensure existing data fits new column types
• Dependencies: Check for views, procedures, or other objects using the table
• Performance: Large tables may take time to alter
• Locks: ALTER TABLE may lock the table during operation
• Backup: Always backup before major changes

Best Practices

• Test first: Test ALTER statements in development
• Backup data: Always backup before altering
• Plan changes: Document all alterations
• Check dependencies: Ensure no objects depend on changed columns
• Verify data: Check data after modifications
• Use transactions: When possible, use transactions for safety

Common Errors

• Column doesn't exist: Verify column name before dropping
• Data type mismatch: Existing data must fit new type
• Constraint violation: Cannot drop column with dependencies
• Foreign key exists: Drop foreign keys before dropping columns

Next Steps

Learn about removing tables with DROP TABLE, or explore constraints in detail in Constraints.