ALTER Tables

The ALTER TABLE statement is used to modify the structure of existing tables. This chapter introduces how to use ALTER TABLE to add, drop, or modify columns and constraints.

Basic Syntax

ALTER TABLE table_name
ADD | DROP | MODIFY | CHANGE column_definition;

Add Columns

-- Add single column
ALTER TABLE users
ADD phone VARCHAR(20);

-- Add multiple columns
ALTER TABLE users
ADD phone VARCHAR(20),
ADD address VARCHAR(200);

-- Add column at specific position
ALTER TABLE users
ADD age INT AFTER name;

Drop Columns

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

-- Drop multiple columns
ALTER TABLE users
DROP COLUMN phone,
DROP COLUMN address;

Modify Columns

MODIFY - Change Column Definition

-- Change column type
ALTER TABLE users
MODIFY age BIGINT;

-- Modify column and add constraint
ALTER TABLE users
MODIFY email VARCHAR(100) NOT NULL UNIQUE;

CHANGE - Rename and Modify Column

-- Rename column and change type
ALTER TABLE users
CHANGE old_name new_name VARCHAR(100);

Rename Table

-- Rename table
ALTER TABLE users
RENAME TO customers;

-- Or use RENAME TABLE
RENAME TABLE users TO customers;

Add Constraints

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

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

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

Drop Constraints

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

-- Drop foreign key
ALTER TABLE orders
DROP FOREIGN KEY fk_user_id;

-- Drop index
ALTER TABLE users
DROP INDEX idx_email;

Modify Table Options

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

-- Change character set
ALTER TABLE users
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Practical Examples

Add Timestamp Fields

ALTER TABLE users
ADD created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Best Practices

1. Backup data before modification
2. Execute during off-peak hours
3. Use online DDL for large tables
4. Test in development environment first
5. Use transactions (if supported)

Summary

• ADD: Add columns or constraints
• DROP: Remove columns or constraints
• MODIFY: Change column definition
• CHANGE: Rename and modify column
• RENAME: Rename table

Next Step: Learn DROP Tables