MySQL ADD COLUMN Statement

MySQL ADD COLUMN Statement

The MySQL ADD COLUMN statement is used to add a new column to an existing table. This statement is essential for modifying the structure of a table to accommodate new data requirements.

Syntax

ALTER TABLE table_name
ADD COLUMN column_name datatype [constraints];

The ADD COLUMN statement has the following components:

  • table_name: The name of the table to be altered.
  • column_name: The name of the new column to be added.
  • datatype: The data type of the new column (e.g., INT, VARCHAR(100), DATE).
  • [constraints]: Optional constraints for the new column (e.g., PRIMARY KEY, NOT NULL, UNIQUE).

Example MySQL ADD COLUMN Statement

Let's look at an example of the MySQL ADD COLUMN 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 New Column

Add a new column to the table:

ALTER TABLE employees
ADD COLUMN date_of_birth DATE;

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

To verify that the column has been added, you can describe the table structure:

DESCRIBE employees;

This query provides a detailed description of the employees table structure, including the new column.

MySQL DESCRIBE TABLE

Conclusion

The MySQL ADD COLUMN statement is a powerful tool for modifying the structure of existing tables to accommodate new data requirements. Understanding how to use the ADD COLUMN statement is essential for effective database management in MySQL.