MySQL RENAME COLUMN Statement

MySQL RENAME COLUMN Statement

The MySQL RENAME COLUMN statement is used to rename a column in an existing table. This statement is essential for changing the name of a column without altering its data type or constraints.

Syntax

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name datatype [constraints];

The RENAME COLUMN statement has the following components:

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

Example MySQL RENAME COLUMN Statement

Let's look at an example of the MySQL RENAME 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,
    dob DATE
);

This query creates a table named employees with columns for id, first_name, last_name, email, and dob (date of birth).

MySQL CREATE TABLE

Step 3: Renaming a Column

Rename a column in the table:

ALTER TABLE employees
CHANGE COLUMN dob date_of_birth DATE;

This query renames the column dob to date_of_birth in the employees table.

MySQL RENAME COLUMN

Step 4: Verifying the Column Rename

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

DESCRIBE employees;

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

MySQL DESCRIBE TABLE

Conclusion

The MySQL RENAME COLUMN statement is a powerful tool for renaming columns in existing tables without altering their data types or constraints. Understanding how to use the RENAME COLUMN statement is essential for effective database management in MySQL.