MySQL ALTER TABLE Statement

MySQL ALTER TABLE Statement

The MySQL ALTER TABLE statement is used to modify an existing table. This statement is essential for adding, deleting, or modifying columns and constraints in a table.

Syntax

ALTER TABLE table_name
    ADD column_name datatype [constraints],
    MODIFY column_name datatype [constraints],
    DROP COLUMN column_name;

The ALTER TABLE statement has the following components:

  • table_name: The name of the table to be altered.
  • ADD column_name datatype [constraints]: Adds a new column to the table with the specified data type and optional constraints.
  • MODIFY column_name datatype [constraints]: Modifies an existing column's data type and optional constraints.
  • DROP COLUMN column_name: Deletes a column from the table.

Example MySQL ALTER TABLE Statement

Let's look at an example of the MySQL ALTER TABLE statement and how to use it:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

This query creates a table named employees with columns for id, first_name, last_name, and email.

MySQL CREATE TABLE

Step 3: Adding a Column

Next, add a new column to the table:

ALTER TABLE employees
ADD date_of_birth DATE;

This query adds a new column named date_of_birth to the employees table.

MySQL ADD COLUMN

Step 4: Modifying a Column

Modify an existing column's data type:

ALTER TABLE employees
MODIFY email VARCHAR(150);

This query changes the data type of the email column in the employees table to VARCHAR(150).

MySQL MODIFY COLUMN

Step 5: Dropping a Column

Drop an existing column from the table:

ALTER TABLE employees
DROP COLUMN date_of_birth;

This query deletes the date_of_birth column from the employees table.

MySQL DROP COLUMN

Conclusion

The MySQL ALTER TABLE statement is a powerful tool for modifying the structure of existing tables. Understanding how to use the ALTER TABLE statement and its various options is essential for effective database management in MySQL.