MySQL ALTER Command

Overview

The ALTER TABLE statement is used to modify the structure of an existing table. You can add, delete, or modify columns, indexes, and constraints after a table has been created.

Common ALTER Operations

• Add Columns: Add new columns to tables
• Drop Columns: Remove columns from tables
• Modify Columns: Change column definitions
• Rename Columns: Change column names
• Add Constraints: Add PRIMARY KEY, FOREIGN KEY, etc.
• Drop Constraints: Remove constraints
• Change Options: Modify table options

ALTER TABLE Syntax

Basic Syntax

ALTER TABLE table_name
    [alter_specification [, alter_specification] ...]

alter_specification:
    ADD [COLUMN] column_definition
  | ADD [COLUMN] (column_definition, ...)
  | DROP [COLUMN] column_name
  | CHANGE [COLUMN] old_name new_name definition
  | MODIFY [COLUMN] column_definition
  | RENAME [TO] new_table_name

Adding Columns

Add Single Column

-- Add column at end
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Add column with default value
ALTER TABLE users 
ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- Add column with NOT NULL
ALTER TABLE users 
ADD COLUMN email_verified BOOLEAN DEFAULT FALSE;

-- Add column with auto increment
ALTER TABLE users 
ADD COLUMN account_number INT AUTO_INCREMENT PRIMARY KEY;

Add Multiple Columns

-- Add several columns
ALTER TABLE users 
ADD COLUMN first_name VARCHAR(50),
ADD COLUMN last_name VARCHAR(50),
ADD COLUMN middle_name VARCHAR(50);

-- Add columns with specific options
ALTER TABLE orders
ADD COLUMN tracking_number VARCHAR(50),
ADD COLUMN shipping_date DATE,
ADD COLUMN delivery_date DATE NULL;

Add Column with Position

-- Add as first column
ALTER TABLE users ADD COLUMN id INT FIRST;

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

-- Add at end (default)
ALTER TABLE users ADD COLUMN notes TEXT;

Add Column with Constraint

-- Add with PRIMARY KEY
ALTER TABLE products ADD COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

-- Add with UNIQUE constraint
ALTER TABLE users 
ADD COLUMN email VARCHAR(255) UNIQUE;

-- Add with FOREIGN KEY
ALTER TABLE orders 
ADD COLUMN user_id INT,
ADD FOREIGN KEY (user_id) REFERENCES users(id);

-- Add with CHECK constraint (MySQL 8.0+)
ALTER TABLE employees 
ADD COLUMN age INT CHECK (age >= 18 AND age <= 65);

Dropping Columns

Drop Single Column

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Drop if exists (MySQL 8.0+)
ALTER TABLE users DROP COLUMN IF EXISTS phone;

Drop Multiple Columns

-- Drop several columns
ALTER TABLE users
DROP COLUMN fax,
DROP COLUMN pager,
DROP COLUMN work_phone;

Drop and Add Column

-- Drop and re-add column
ALTER TABLE users
DROP COLUMN old_column,
ADD COLUMN new_column VARCHAR(100);

Modifying Columns

CHANGE vs MODIFY

-- MODIFY: Change definition only
ALTER TABLE users 
MODIFY COLUMN name VARCHAR(100) NOT NULL;

-- CHANGE: Change name and definition
ALTER TABLE users 
CHANGE COLUMN username user_name VARCHAR(100) NOT NULL;

Change Data Type

-- Change VARCHAR length
ALTER TABLE users 
MODIFY COLUMN email VARCHAR(255);

-- Change INT to BIGINT
ALTER TABLE orders 
MODIFY COLUMN total BIGINT;

-- Change TEXT to LONGTEXT
ALTER TABLE articles 
MODIFY COLUMN content LONGTEXT;

-- Change to NOT NULL
ALTER TABLE users 
MODIFY COLUMN username VARCHAR(50) NOT NULL;

Change Default Value

-- Add default value
ALTER TABLE users 
MODIFY COLUMN status VARCHAR(20) DEFAULT 'pending';

-- Change default value
ALTER TABLE users 
MODIFY COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- Remove default value
ALTER TABLE users 
MODIFY COLUMN status VARCHAR(20) DEFAULT NULL;

Add AUTO_INCREMENT

-- Add AUTO_INCREMENT to existing column
ALTER TABLE users 
MODIFY COLUMN id INT AUTO_INCREMENT;

-- Set starting value
ALTER TABLE users AUTO_INCREMENT = 1000;

Renaming Columns

-- Rename single column
ALTER TABLE users 
CHANGE COLUMN username user_name VARCHAR(50);

-- Rename and change type
ALTER TABLE users 
CHANGE COLUMN dob birth_date DATE;

-- Rename with constraint
ALTER TABLE products 
CHANGE COLUMN product_id id INT PRIMARY KEY;

Renaming Table

-- Rename table
ALTER TABLE old_table_name RENAME TO new_table_name;

-- Alternative syntax
RENAME TABLE old_table_name TO new_table_name;

-- Rename multiple tables
RENAME TABLE 
    table1 TO new_table1,
    table2 TO new_table2,
    table3 TO new_table3;

Altering Constraints

Adding Primary Key

-- Add single column primary key
ALTER TABLE users ADD PRIMARY KEY (id);

-- Add composite primary key
ALTER TABLE order_items 
ADD PRIMARY KEY (order_id, product_id);

-- Add primary key with auto increment
ALTER TABLE products 
MODIFY COLUMN id INT AUTO_INCREMENT,
ADD PRIMARY KEY (id);

Dropping Primary Key

-- Drop primary key
ALTER TABLE users DROP PRIMARY KEY;

-- Drop and add new primary key
ALTER TABLE users 
DROP PRIMARY KEY,
ADD PRIMARY KEY (new_id);

Adding Foreign Key

-- Add foreign key
ALTER TABLE orders 
ADD CONSTRAINT fk_orders_user 
FOREIGN KEY (user_id) REFERENCES users(id);

-- Add with ON DELETE
ALTER TABLE orders 
ADD CONSTRAINT fk_orders_user 
FOREIGN KEY (user_id) REFERENCES users(id) 
ON DELETE CASCADE;

-- Add with ON UPDATE
ALTER TABLE order_items 
ADD CONSTRAINT fk_items_product 
FOREIGN KEY (product_id) REFERENCES products(id) 
ON UPDATE CASCADE;

Dropping Foreign Key

-- Drop specific foreign key
ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;

-- Find foreign key names
SHOW CREATE TABLE orders;

-- Drop foreign key by name
ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;

Adding UNIQUE Constraint

-- Add single column unique
ALTER TABLE users ADD UNIQUE (email);

-- Add composite unique
ALTER TABLE user_emails 
ADD UNIQUE (user_id, email_type);

-- Add named unique constraint
ALTER TABLE products 
ADD CONSTRAINT uc_product_code UNIQUE (code);

Dropping UNIQUE Constraint

-- Drop unique index
ALTER TABLE users DROP INDEX email;

-- Drop named unique constraint
ALTER TABLE products DROP INDEX uc_product_code;

Altering Indexes

Adding Indexes

-- Add simple index
ALTER TABLE users ADD INDEX idx_users_email (email);

-- Add composite index
ALTER TABLE orders ADD INDEX idx_orders_user_date (user_id, order_date);

-- Add FULLTEXT index
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (title, content);

-- Add spatial index
ALTER TABLE locations ADD SPATIAL INDEX sp_position (position);

Dropping Indexes

-- Drop index
ALTER TABLE users DROP INDEX idx_users_email;

-- Drop primary key (also removes index)
ALTER TABLE users DROP PRIMARY KEY;

Altering Table Options

Change Engine

-- Change storage engine
ALTER TABLE users ENGINE = InnoDB;
ALTER TABLE logs ENGINE = MyISAM;

-- Convert multiple tables
ALTER TABLE users ENGINE = InnoDB;
ALTER TABLE orders ENGINE = InnoDB;
ALTER TABLE products ENGINE = InnoDB;

Change Character Set

-- Change character set
ALTER TABLE users CHARACTER SET utf8mb4;

-- Change collation
ALTER TABLE users COLLATE utf8mb4_unicode_ci;

-- Change both
ALTER TABLE users 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

Change Auto Increment

-- Set next auto increment value
ALTER TABLE users AUTO_INCREMENT = 10000;

-- Reset auto increment
ALTER TABLE users AUTO_INCREMENT = 1;

Add Table Comment

-- Add or modify table comment
ALTER TABLE users COMMENT = 'User accounts table';

-- Add column comment
ALTER TABLE users 
MODIFY COLUMN name VARCHAR(100) COMMENT 'User full name';

Complex ALTER Operations

Reorder Columns

-- MySQL doesn't have direct REORDER
-- Must use multiple MODIFY statements

ALTER TABLE users 
MODIFY COLUMN id INT FIRST,
MODIFY COLUMN name VARCHAR(100) AFTER id,
MODIFY COLUMN email VARCHAR(255) AFTER name;

Convert to Different Format

-- Convert to compressed row format
ALTER TABLE users ROW_FORMAT = COMPRESSED;

-- Convert to dynamic row format
ALTER TABLE users ROW_FORMAT = DYNAMIC;

-- Convert to fixed row format
ALTER TABLE users ROW_FORMAT = FIXED;

Performance Considerations

ALTER Table Performance

-- ALTER TABLE can be slow on large tables
-- Consider these strategies:

-- 1. Use pt-online-schema-change (external tool)
-- Allows online schema changes

-- 2. Create new table and copy data
CREATE TABLE users_new LIKE users;
-- Add/modify columns
ALTER TABLE users_new ADD COLUMN phone VARCHAR(20);
-- Copy data
INSERT INTO users_new SELECT * FROM users;
-- Swap tables
RENAME TABLE users TO users_old, users_new TO users;
-- Drop old
DROP TABLE users_old;

-- 3. Use ALGORITHM clause (MySQL 5.6+)
ALTER TABLE users 
ADD COLUMN phone VARCHAR(20), 
ALGORITHM = INPLACE;  -- Faster, online operation

-- 4. Use LOCK clause
ALTER TABLE users 
ADD COLUMN phone VARCHAR(20),
ALGORITHM = INPLACE,
LOCK = NONE;  -- No locking, but longer time

ALGORITHM Option

-- ALGORITHM = COPY (default for some changes)
-- Creates copy of table, slow, locks table
ALTER TABLE users 
ADD COLUMN phone VARCHAR(20),
ALGORITHM = COPY;

-- ALGORITHM = INPLACE (faster, minimal locking)
-- Modifies table in place
ALTER TABLE users 
MODIFY COLUMN name VARCHAR(200),
ALGORITHM = INPLACE;

-- ALGORITHM = INSTANT (MySQL 8.0+, fastest)
-- No table copy, no locking
ALTER TABLE users 
ADD COLUMN phone VARCHAR(20),
ALGORITHM = INSTANT;

LOCK Option

-- LOCK = DEFAULT (MySQL decides)
-- LOCK = NONE (allows concurrent DML)
-- LOCK = SHARED (allows concurrent reads)
-- LOCK = EXCLUSIVE (blocks all DML/DDL)

ALTER TABLE users 
ADD COLUMN phone VARCHAR(20),
ALGORITHM = INPLACE,
LOCK = NONE;

Practical Examples

Migration: Add User Status

-- Add status column with default
ALTER TABLE users 
ADD COLUMN status ENUM('active', 'inactive', 'suspended') DEFAULT 'inactive';

-- Update existing users
UPDATE users SET status = 'active' WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Add index for status
ALTER TABLE users ADD INDEX idx_users_status (status);

Migration: Add Soft Delete

-- Add deleted_at column
ALTER TABLE users 
ADD COLUMN deleted_at TIMESTAMP NULL,
ADD INDEX idx_users_deleted (deleted_at);

-- Update application logic to use soft deletes
-- Instead of: DELETE FROM users WHERE id = 1;
-- Use: UPDATE users SET deleted_at = NOW() WHERE id = 1;

Migration: Add Audit Columns

-- Add audit columns
ALTER TABLE orders
ADD COLUMN created_by INT NULL,
ADD COLUMN updated_by INT NULL,
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Add foreign keys for audit
ALTER TABLE orders
ADD CONSTRAINT fk_orders_created 
FOREIGN KEY (created_by) REFERENCES users(id),
ADD CONSTRAINT fk_orders_updated 
FOREIGN KEY (updated_by) REFERENCES users(id);

Migration: Normalize Data

-- Create normalized table
CREATE TABLE user_phones (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    phone VARCHAR(20) NOT NULL,
    is_primary BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Migrate data
INSERT INTO user_phones (user_id, phone, is_primary)
SELECT id, phone, TRUE FROM users WHERE phone IS NOT NULL;

-- Add phone column (optional)
ALTER TABLE users ADD COLUMN primary_phone_id INT NULL;
UPDATE users u 
SET primary_phone_id = (SELECT id FROM user_phones WHERE user_id = u.id AND is_primary = TRUE LIMIT 1);

-- Drop old column
ALTER TABLE users DROP COLUMN phone;

Troubleshooting

Common Errors

-- Error: Duplicate column name
-- ALTER TABLE users ADD COLUMN name VARCHAR(100);
-- Solution: Column already exists

-- Error: Key column doesn't exist
-- ALTER TABLE users ADD PRIMARY KEY (nonexistent);
-- Solution: Check column name

-- Error: Foreign key constraint fails
-- ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);
-- Solution: Ensure referenced column exists and has matching type

-- Error: Cannot delete column referenced by foreign key
-- ALTER TABLE users DROP COLUMN id;
-- Solution: Drop foreign key first
ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;

Checking Table Structure

-- Show table structure
DESCRIBE table_name;
DESC table_name;

-- Show create statement
SHOW CREATE TABLE table_name;

-- Show table information
SHOW TABLE STATUS LIKE 'table_name';

-- Check columns
SELECT * FROM information_schema.COLUMNS 
WHERE TABLE_NAME = 'table_name';

Summary

ALTER TABLE allows you to:

ALTER TABLE：

• Modify Structure: Add, drop, change columns
• Manage Constraints: Add and drop keys
• Change Options: Engine, character set, auto-increment
• Online DDL: Use ALGORITHM and LOCK options

Consider performance impact when modifying large tables and use appropriate ALGORITHM and LOCK options.

---

Previous: Transactions

Next: Indexes