MariaDB is an open-source relational database management system that can be used to create databases, and tables and to query the tables. Anyone who knows how to write SQL queries can use any of the databases like MariaDB or Mysql for the execution of SQL queries. In this article, we will learn about How to rename a column name in MariaDB with the help of various examples and so on.
Introduction to Rename Column in MariaDB
We change the file names in our system or mobile so that we can remember them and access them faster and easily. Similarly, when creating tables and columns, it is always better to name the table or columns short and relevant to the data it is storing so that the data can be retrieved easily. So, how to rename the column name after creating the table? In MariaDB, we can rename the table name and column name by using the alter statement.
ALTER command is a data definition language (DDL) statement that can be used for modifying the structure of tables in a database i.e., add, drop, or modify columns in a table. Alter command can also be used for renaming a table by using the ‘RENAME TO’ keyword. For renaming a table or column we need to create a table with some columns and insert records.
Examples of How to Rename a Column Name in MariaDB
To understand How to Rename a Column Name in MariaDB we need a table on which we will perform various queries and operations. In this article, we have an Employee table which is shown below.
|
EmployeeID |
FirstName |
LastName |
Salary |
|---|---|---|---|
|
1 |
John |
Doe |
50000 |
|
2 |
Jane |
Smith |
65000 |
|
3 |
Michael |
Johnson |
40000 |
|
4 |
Emily |
Brown |
55000 |
|
5 |
Chris |
Wilson |
70000 |
Example 1: Using ALTER TABLE RENAME tO Statement
Syntax:
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;Suppose we want to change the column name EmployeeID to EmpID.
Query:
ALTER TABLE Employees RENAME COLUMN EmployeeID TO EmpID;Output:
Alter column rename to
Explanation: In the above Query, We have changed the column of Employees Table from EmployeeID TO EmpID.
Example 2: Using ALTER TABLE CHANGE Statement
The CHANGE keyword can also be used for renaming the column name in a table in MariaDB.
Syntax:
ALTER TABLE table_name CHANGE old_column_name new_column_name new_column_datatype;The main difference between ‘RENAME TO’ and ‘CHANGE’ is, that we cannot directly change the column datatype by using the ‘RENAME’ keyword but the same is possible by using the ‘CHANGE’ keyword, we need to specify the new column name and the required datatype.
Now, We want to change the column name Salary to Emp_sal. The datatype of column Salary is INT. Let’s change the datatype to BIGINT. We can specify the old column datatype or new datatype.
Query using No change in Datatype:
ALTER TABLE Employees CHANGE Salary Emp_sal INT;Query using New Datatype:
ALTER TABLE table_name CHANGE Salary Emp_sal BIGINT;Output:
Alter table change
To view the details about the columns in a table and the corresponding datatypes, ‘DESC table_name’ can be used. this command describes the table.
Describe table
Conclusion
The ‘RENAME TO‘ and ‘CHANGE‘ keywords can be used to rename the column name in MariaDB. By renaming or changing the column names, it improves the quality of the data in the table, which enhances the database organization. Alter command may also be used to add columns, drop columns, and modify the datatype of columns using ‘ADD’, ‘DROP’, and MODIFY’. The ‘Alter’ command and the ‘Update’ command are not the same. The former is a data definition language statement, which is used for changing the structure of the table, and the later is a data manipulation language statement used for manipulating the data or records in the table.