MySQL Alter Table

Key Operations

ALTER TABLE users 
ADD email VARCHAR(100);

ALTER TABLE users 
DROP COLUMN age;

ALTER TABLE users 
MODIFY name VARCHAR(150);

ALTER TABLE users 
RENAME COLUMN old_name TO new_name;

🔹 ADD Column

Adding columns allows you to extend your table structure without recreating it. New columns are added to the end of the table by default, but you can specify their position using FIRST or AFTER keywords. Existing data remains intact when adding columns.

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

-- Add column with constraints
ALTER TABLE customers 
ADD email VARCHAR(100) NOT NULL;

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

-- Add column at the beginning
ALTER TABLE customers 
ADD id INT FIRST;

🔹 DROP Column

Dropping a column permanently removes it and all its data from the table. This operation cannot be undone, so ensure you have backups before dropping columns. Be cautious when dropping columns in production databases as this will delete all data stored in that column.

-- Drop a single column
ALTER TABLE customers 
DROP COLUMN age;

-- Drop multiple columns
ALTER TABLE customers 
DROP COLUMN phone,
DROP COLUMN fax;

-- Safe drop with IF EXISTS (MySQL 8.0.29+)
ALTER TABLE customers 
DROP COLUMN IF EXISTS old_field;

🔹 MODIFY Column

MODIFY changes a column's data type, size, or attributes while keeping the same column name. This is useful for expanding field sizes, changing data types, or adding/removing constraints. Be careful when reducing column sizes or changing types as data may be truncated or lost.

-- Change column data type
ALTER TABLE products 
MODIFY price DECIMAL(12, 2);

-- Increase VARCHAR size
ALTER TABLE users 
MODIFY name VARCHAR(200);

-- Add NOT NULL constraint
ALTER TABLE users 
MODIFY email VARCHAR(100) NOT NULL;

-- Change to allow NULL
ALTER TABLE users 
MODIFY phone VARCHAR(20) NULL;

🔹 CHANGE Column (Rename and Modify)

CHANGE allows you to rename a column and modify its definition simultaneously. Unlike MODIFY, you must specify both the old and new column names. This is useful when you need to rename a column while also changing its data type or constraints in a single operation.

-- Rename column and change type
ALTER TABLE users 
CHANGE old_name full_name VARCHAR(150);

-- Rename without changing type
ALTER TABLE users 
CHANGE phone phone_number VARCHAR(20);

-- Rename and add constraint
ALTER TABLE products 
CHANGE price product_price DECIMAL(10, 2) NOT NULL;

🔹 RENAME Column (MySQL 8.0+)

The RENAME COLUMN syntax provides a simpler way to rename columns without specifying the data type. This is cleaner and less error-prone than CHANGE when you only want to rename a column without modifying its definition. Available in MySQL 8.0 and later versions.

-- Simple column rename (MySQL 8.0+)
ALTER TABLE users 
RENAME COLUMN email TO email_address;

-- Rename multiple columns
ALTER TABLE users 
RENAME COLUMN fname TO first_name,
RENAME COLUMN lname TO last_name;

🔹 RENAME Table

Renaming a table changes its name while preserving all data, structure, and indexes. This is useful for reorganizing your database schema or following new naming conventions. All foreign key relationships and triggers remain intact after renaming, but you'll need to update application code that references the old name.

-- Rename a table
ALTER TABLE old_customers 
RENAME TO customers;

-- Alternative syntax
RENAME TABLE old_products TO products;

-- Rename multiple tables at once
RENAME TABLE 
    old_orders TO orders,
    old_items TO order_items;

🔹 ADD and DROP Constraints

Constraints enforce data integrity rules on your tables. You can add primary keys, foreign keys, unique constraints, and default values using ALTER TABLE. Constraints help maintain data quality by preventing invalid data entry and establishing relationships between tables for referential integrity.

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

-- Add foreign key
ALTER TABLE orders 
ADD FOREIGN KEY (customer_id) 
REFERENCES customers(customer_id);

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

-- Add default value
ALTER TABLE products 
ALTER COLUMN status SET DEFAULT 'active';

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

🔹 Complete Example

This comprehensive example demonstrates multiple ALTER TABLE operations on a single table. It shows how to add columns, modify existing ones, add constraints, and rename columns in a logical sequence. This represents a typical table evolution as application requirements change over time.

-- Start with existing table structure
DESCRIBE employees;

-- Add new columns
ALTER TABLE employees 
ADD department VARCHAR(50),
ADD hire_date DATE,
ADD salary DECIMAL(10, 2);

-- Modify existing column
ALTER TABLE employees 
MODIFY email VARCHAR(150) NOT NULL;

-- Rename column
ALTER TABLE employees 
RENAME COLUMN phone TO phone_number;

-- Add constraints
ALTER TABLE employees 
ADD UNIQUE (email),
ALTER COLUMN salary SET DEFAULT 0.00;

-- View updated structure
DESCRIBE employees;