How to Rename Column in MariaDB

In database management, maintaining an efficient and well-organized schema is important. One common task is to rename columns which can enhance clarity, attach to naming conventions or accommodate structural changes.

In this article, we'll learn about various methods along with examples such as ALTER TABLE statement with CHANGE COLUMN or RENAME COLUMN and INFORMATION_SCHEMA.COLUMNS system table which helps in renaming the columns easily.

How to Rename Column Name?

Renaming columns in a database table is a routine operation performed during database maintenance or schema evolution. While the process may look simple, it is important to understand the various methods available to ensure a smooth transition without compromising data integrity.

Below are the methods that help in Renaming the Column name in MariaDB are as follow:

1. Using ALTER TABLE with Change Column
   
2. Using ALTER TABLE with Rename Column (MariaDB 10.5.0 and later)
   
3. Using INFORMATION_SCHEMA.Columns
   
4. Using a Temporary Table

Let's set up an environment to Rename Column Name

To understand How to Rename Column Names in MariaDB we need a table structure on which we will perform various operations and queries. Here we will consider a table called employees which contains emp_id, emp_name, emp_salary, and emp_department as Columns.

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    emp_salary DECIMAL(10, 2),
    emp_department VARCHAR(50)
);

Output:

1. Using ALTER TABLE with Change Column

The ALTER TABLE statement in MariaDB allows us to modify an existing table's structure including renaming columns. The syntax for renaming a column using CHANGE COLUMN is as follows:

ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name column_definition;

Let's rename the column emp_department to department using below method.

-- Change the column 'emp_department' to 'department' with VARCHAR(50) datatype in the 'employees' table
ALTER TABLE employees CHANGE COLUMN emp_department department VARCHAR(50);

-- Describe the structure of the 'employees' table
DESCRIBE employees;

Output:

Explanation: As we can saw in the output the column emp_department changed to department using the above method.

2. Using ALTER TABLE with Rename Column

We can also change the column name with the help of ALTER TABLE with Rename Column Statement. Let's see the below example.

Syntax:

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

Let's rename the column emp_department to department using this method.

-- Rename the 'emp_salary' column to 'salary' in the 'employees' table
ALTER TABLE employees RENAME COLUMN emp_salary TO salary;

-- Describe the structure of the 'employees' table
DESCRIBE employees;

Output:

Explanation: This output confirms that the column emp_salary has been successfully renamed to salary in the employees table.

3. Using INFORMATION_SCHEMA.COLUMNS

Another method involves updating the column name directly in the INFORMATION_SCHEMA.COLUMNS system table.

-- Update the column name in the INFORMATION_SCHEMA.COLUMNS table
UPDATE INFORMATION_SCHEMA.COLUMNS 
SET COLUMN_NAME = 'name' 
WHERE TABLE_NAME = 'employees' AND COLUMN_NAME = 'emp_name';

-- Describe the structure of the 'employees' table
DESCRIBE employees;

Output:

Explanation: The emp_name column has been successfully renamed to name.

4. Using a Temporary Table

Creating a temporary table and transferring data with the desired column name is another approach.

CREATE TABLE temp_employees AS 
SELECT emp_id, emp_name, emp_salary, emp_department AS department FROM employees;

--delete the employees table
DROP TABLE employees;

--rename the table named temp_employees to employees 
ALTER TABLE temp_employees RENAME TO employees;

DESCRIBE employees;

Output:

Explanation: In the above Query, We have creates a new table temp_employees by selecting data from the employees table with the emp_department column renamed to department. It then drops the original employees table and renames temp_employees to employees, effectively replacing the original table. The DESCRIBE employees statement at the end displays the structure of the new employees table.

Conclusion

Renaming columns in MariaDB can be achieved through various methods each method offering its own advantages and considerations. Whether you prefer the simplicity of the ALTER TABLE statement or the direct update using INFORMATION_SCHEMA.COLUMNS or the flexibility of temporary tables, it is important to choose the method that best fits your workflow while ensuring data integrity and minimal loss to your database operations.