How to Change a Column Name in SQL?

The ALTER TABLE statement in SQL is a powerful command used to modify the structure of an existing table without affecting its data. It enables changes like adding, dropping, renaming or altering columns in the table. Among these operations, altering a column with the CHANGE or RENAME command is commonly used to modify column names.

In this article, we will learn how to use the ALTER TABLE command with CHANGE and RENAME to modify column names or attributes with the help of examples and output in detail.

How to Change a Column Name in SQL?

To change the column name in SQL we will understand the below method with the help of examples as defined below:

1. Using ALTER TABLE with the RENAME command
2. Using ALTER TABLE with the CHANGE command

Let's set up an environment:

To understand How to change a column name in SQL we need a collection and some documents on which we will perform various operations and queries. Here we will consider a collection called products which contains information like name, category, price, and description of the products in various documents.

1. Using ALTER TABLE with the RENAME Command

The RENAME keyword is part of the ALTER TABLE statement and is used to rename a column while keeping its datatype and constraints.

Query:

ALTER TABLE employees RENAME COLUMN first_name TO fname;

Ouptut:

Explanation:

• The RENAME command only changes the column name.
• The datatype, constraints, and data of the column remain unaffected.

2. Using ALTER TABLE with the CHANGE Command

The CHANGE keyword allows us to rename a column and modify its datatype or constraints in a single operation. This is useful when we want to adjust both the name and structure of a column.

Query:

ALTER TABLE employees CHANGE last_name lname VARCHAR(50);

Output:

Explanation:

• The CHANGE command renames the column and modifies its datatype.
• In this example, last_name was renamed to lname and its datatype was updated to VARCHAR(50).

Conclusion

Overall, The ALTER TABLE statement with CHANGE and RENAME commands provides flexibility for managing database schema changes. Use RENAME for simple column renaming while preserving the column structure. Use CHANGE command when we need to rename a column and alter its datatype or constraints simultaneously. Understanding and using these commands effectively ensures smooth database schema management and adaptation to evolving requirements.