← Back to Chapters

MySQL ALTER TABLE

? MySQL ALTER TABLE

MySQL DDL Table Structure

? Quick Overview

The ALTER TABLE statement in MySQL is used to modify the structure of an existing table. You can add new columns, remove existing ones, or change their definitions without dropping the table.

? Key Concepts

  • ALTER TABLE modifies the structure of an existing table.
  • ADD is used to add new columns to a table.
  • DROP COLUMN removes a column from a table.
  • MODIFY or CHANGE updates the definition of an existing column.
  • You can control the position of a new column using FIRST or AFTER column_name.

? Syntax

Basic syntax to add a new column:

? View Code Example 
-- Add a new column to an existing table
ALTER TABLE table_name
ADD column_name datatype;

Other common ALTER TABLE operations:

? View Code Example 
-- Add a column at a specific position
ALTER TABLE table_name
ADD new_column datatype AFTER existing_column;

-- Change datatype or definition of an existing column
ALTER TABLE table_name
MODIFY column_name new_datatype;

-- Remove a column from the table
ALTER TABLE table_name
DROP COLUMN column_name;

? Example: Add a New Column

Suppose you have an Employees table and you want to store the email address of each employee.

? View Code Example 
-- Add an Email column to the Employees table
ALTER TABLE Employees
ADD Email VARCHAR(255);

? What This Does

  • ALTER TABLE Employees selects the existing Employees table for modification.
  • ADD Email VARCHAR(255) adds a new column named Email with a maximum length of 255 characters.
  • The existing data in the table is preserved; only the structure is changed.
  • If no default value is specified, existing rows will have NULL in the new column (unless you later update them).

✅ Tips & Best Practices

  • Always specify the datatype when using ADD, e.g., VARCHAR(255), INT, DATE.
  • Avoid adding a column with a name that already exists in the table to prevent errors.
  • Use AFTER column_name to control where the new column appears in the table structure.
  • Use DROP COLUMN carefully; once a column is dropped, its data is permanently lost.
  • Test your ALTER TABLE statements on a backup or test database before changing production tables.

? Try It Yourself

  • Create a table named Students and then:
    • Add a column PhoneNumber with type VARCHAR(15).
    • Add a column DateOfBirth with type DATE after the StudentName column.
  • Use ALTER TABLE to:
    • Modify a column from INT to BIGINT.
    • Drop an unnecessary column from any practice table.
  • Open your database client and run:
    ? View Practice Snippet 
    -- Example practice task on your own table
ALTER TABLE YourTableName
ADD CreatedAt DATETIME;

ALTER TABLE YourTableName
MODIFY CreatedAt TIMESTAMP;