Rename column name in SQL | Board Infinity

Introduction

Occasionally, we might want to rename our table to something more appropriate. To rename the table, we can use ALTER TABLE for this purpose.

Syntax(Oracle,MySQL,MariaDB):

ALTER TABLE table_name
RENAME TO new_table_name;

Columns can also be given new name with the use of ALTER TABLE.

Syntax(MySQL, Oracle):

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

Syntax(MariaDB):

ALTER TABLE table_name
CHANGE COLUMN old_name TO new_name;

Example

Consider the following table:

ID	NAME	AGE	STATE	EMAIL	ID_NUMBER	COUNTRY
1	Pari	24	Delhi	pari@example.com	NULL	India
2	Mira	28	Kerala	mira@example.com	NULL	India
3	Riya	19	Delhi	riya@example.com	NULL	India

To rename ID column(The first column) to user_id the following syntax can be used:

ALTER TABLE ID
RENAME COLUMN id TO user_id;

Output:

user_id	NAME	AGE	STATE	EMAIL	ID_NUMBER	COUNTRY
1	Pari	24	Delhi	pari@example.com	NULL	India
2	Mira	28	Kerala	mira@example.com	NULL	India
3	Riya	19	Delhi	riya@example.com	NULL	India

write your code here: Coding Playground

When renaming a column in a table, be cautious.

You run the risk of destroying database dependencies when you use ALTER TABLE to rename columns.

Instead of using ALTER TABLE to rename a column, a database refactoring tool will take care of all the dependents and update them with the new column name.

If you have a tiny database you may not need to worry, but it is crucial to keep in mind.

Conclusion

In this article, you have learned how to use ALTER TABLE to add a column and rename a column in a table.