Skip to main content

ALTER TABLE

Learning Focus

Use this lesson to understand ALTER TABLE with practical syntax and examples.

Concept Overview​

What is ALTER TABLE?​

ALTER TABLE is a Data Definition Language (DDL) command in MySQL that allows users to modify the structure of an existing table without losing its data.

Why is It Important?​

In real-world scenarios, database requirements evolve-new data needs to be stored, old data structures become obsolete, or optimizations are required. The ALTER TABLE command provides flexibility and control for adapting your database schema to meet these changing needs.

Where It Fits in Database Operations​

  • Part of schema evolution
  • Belongs to DDL (like CREATE, DROP)
  • Complements DML (like SELECT, INSERT, UPDATE)
  • Often used during development, updates, and maintenance phases

Basic Syntax & Rules​

Basic Syntax​

ALTER TABLE table_name
[ADD column_definition]
[DROP COLUMN column_name]
[MODIFY COLUMN column_definition]
[CHANGE COLUMN old_name new_name column_definition]
[RENAME TO new_table_name];

Key Parameters​

ClausePurpose
ADDAdds a new column
DROP COLUMNDeletes a column from the table
MODIFY COLUMNChanges a column's data type or definition
CHANGE COLUMNRenames and modifies a column simultaneously
RENAME TORenames the table itself

Limitations & Considerations​

  • You can only alter one table at a time.
  • Altering large tables can be slow and may lock the table temporarily.
  • Some alterations may cause data loss if done incorrectly (e.g., narrowing a data type).

Step-by-Step Examples​

Example 1: Add a New Column​

ALTER TABLE employees
ADD birth_date DATE;

Purpose: Adds a birth_date column to the employees table.

Expected Result:

idnamebirth_date
1AliceNULL
2BobNULL

Example 2: Modify Column Data Type​

ALTER TABLE products
MODIFY COLUMN price DECIMAL(10, 2);

Purpose: Ensures that price has up to 10 digits, 2 decimal places.

Example 3: Rename a Column​

ALTER TABLE customers
CHANGE COLUMN phone_number contact_number VARCHAR(15);

Purpose: Renames phone_number to contact_number.

Example 4: Drop a Column​

ALTER TABLE orders
DROP COLUMN old_status;

Purpose: Removes the old_status column no longer in use.

Example 5: Rename the Table​

ALTER TABLE sales_data
RENAME TO archived_sales;

Purpose: Changes the table name to archived_sales.

Practical Use Cases​

1. E-commerce Product Updates​

Scenario: New feature requires tracking product weight.

ALTER TABLE products ADD weight DECIMAL(5,2);

2. CRM System Enhancements​

Scenario: Rename tel to mobile_number for clarity.

ALTER TABLE clients CHANGE COLUMN tel mobile_number VARCHAR(20);

3. Financial Reporting​

Scenario: Adjust column precision for currency fields.

ALTER TABLE invoices MODIFY COLUMN total_amount DECIMAL(12, 2);

4. Legacy Field Cleanup​

Scenario: Remove deprecated fax_number field.

ALTER TABLE suppliers DROP COLUMN fax_number;

5. Project Archiving​

Scenario: Archive completed projects to a new table name.

ALTER TABLE projects RENAME TO archived_projects;

Common Mistakes & Troubleshooting​

MistakeCauseSolution
Using MODIFY instead of CHANGE to renameMODIFY can't rename a columnUse CHANGE old_name new_name ...
Forgetting data compatibilityReducing size/type may cause data lossAlways back up and validate
Incorrect data type in ADDSyntax error or invalid typeDouble-check column definition
Locking large tables during changeSystem becomes unresponsivePerform off-hours or use pt-online-schema-change

Tips:​

  • Always backup your database before using ALTER TABLE.
  • Use SHOW CREATE TABLE to inspect the structure before/after changes.
  • Use tools like MySQL Workbench to visually alter tables.

Best Practices​

Performance Considerations​

  • Altering large tables can lock rows or entire tables.
  • Use pt-online-schema-change for high-traffic tables.

When to Use​

  • Adding/removing columns as business needs evolve
  • Adapting to new application requirements

When NOT to Use​

  • During peak usage unless absolutely necessary
  • If data integrity may be compromised without testing

Optimization Tips​

  • Add indexes after structural changes for better performance
  • Validate new schema with test data before production changes

Hands-On Practice​

Sample Setup:​

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

INSERT INTO employees (name) VALUES ('Alice'), ('Bob'), ('Charlie');

Exercise 1: Add a department column​

ALTER TABLE employees ADD department VARCHAR(50);

[OK] Solution Output:

idnamedepartment
1AliceNULL
2BobNULL
3CharlieNULL

Exercise 2: Rename name to full_name​

ALTER TABLE employees CHANGE COLUMN name full_name VARCHAR(50);

Exercise 3: Drop the department column​

ALTER TABLE employees DROP COLUMN department;

Connection to Other Concepts​

Related ConceptDescription
CREATE TABLEInitial table definition; precedes ALTER
INSERT INTOUsed after structure is created/altered
DESCRIBEShows structure post-alter
DROP TABLERemoves the table entirely

Prerequisite: Understand basic SQL syntax, CREATE TABLE, and data types.

Next Topics: Indexing (CREATE INDEX), Table Joins, Views, Stored Procedures

Concept Map​

flowchart LR
    A[Schema Context] --> B[ALTER TABLE]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls​

PitfallConsequencePrevention
Running destructive commands without contextDatabases or tables are dropped unexpectedlyConfirm target with SELECT DATABASE(); and run in a controlled environment first
Skipping backups before schema/data changesRecovery window becomes long or impossibleCreate a backup snapshot and test restore before production changes
Not validating privileges and locksOperations fail midway or block trafficCheck grants, active sessions, and maintenance window constraints

Quick Reference​

ALTER TABLE users ADD COLUMN last_login DATETIME NULL;
ALTER TABLE users MODIFY COLUMN email VARCHAR(320) NOT NULL;
ALTER TABLE users DROP COLUMN old_field;

What's Next​