SQLite Rename Column

Renaming a Column becomes necessary when there is a certain change that appears to be visible in the column the name given to the column previously is vague or it doesn’t represent what the column holds exactly. SQLite provides the modified version of the ALTER TABLE command which lets the user rename an already existing column, without affecting any other column name. Traditionally, renaming a certain column was a tedious task as a duplicate table with a similar type of name was needed to create. All the data of the previous table needed to be copied into the new table, and lastly, the old table needed to be dropped. This entire long and tedious task can now be done using the modified version of the ALTER TABLE command.

In this article, we will introduce ourselves to the SQLite ALTER TABLE RENAME COLUMN statement see its usage, and understand how it has helped in reducing the process of renaming a column. We will also see the traditional way of renaming a column which was used before the introduction of the ALTER TABLE RENAME COLUMN statement.

SQLite ALTER TABLE RENAME COLUMN Statement

Introducing the ALTER TABLE RENAME COLUMN command in SQLite3 was a game changer. This command made renaming a certain column of a table a one-step process. Traditionally it was a very lengthy and tedious process, which consisted of creating a different table, copying all the data from the old table to the new, dropping the old table, and then changing the name of the new table. This command removed the need for this entire process and allowed the user to rename a column using this single command.

Syntax:

ALTER TABLE

table_name

RENAME COLUMN

old_name

TO

new_name;

Example of SQLite Rename Column

Here, we will first use the ALTER TABLE command and then pass the table name, then we will use the RENAME COLUMN command and then pass the old_name or the Current Name of the column which we want to change, then use the TO command and pass the new name of the column. The semicolon will mark the end of the statement.

For this tutorial, we will be using a table called Students and rename one of its columns. First, write the below code to create the Students table –

CREATE TABLE Students (
studID INTEGER,
FirstName TEXT,
LastName TEXT,
Class INTEGER,
Section TEXT
);

Then, we will populate the Students table with some values using the INSERT INTO commands:

INSERT INTO Students VALUES(10,'Vivek','Singh',7,'B');
INSERT INTO Students VALUES(12,'Manish','Roy',8,'A');
INSERT INTO Students VALUES(15,'Dilip','Mukherjee',10,'A');
INSERT INTO Students VALUES(16,'Souvik','Sen',10,'B');
INSERT INTO Students VALUES(18,'Rohit','Das',10,'A');
INSERT INTO Students VALUES(21,'Mohit','Shetty',9,'A');
INSERT INTO Students VALUES(22,'Raj','Banerjee',9,'B');
INSERT INTO Students VALUES(24,'Biswajit','Das',7,'B');
INSERT INTO Students VALUES(25,'Srijit','Roy',8,'A');
INSERT INTO Students VALUES(27,'Rakesh','Chatterjee',8,'C');

After populating the table let’s check everything is fine or not by fetching all the values from the table using SELECT command.

SELECT * FROM Students;

Output:

As we got our expected output, we will now change one of the names of the columns using the ALTER TABLE RENAME COLUMN command.

Example 1: SQLite ALTER TABLE RENAME COLUMN

We will change the name of the column studID to Student_ID using the ALTER TABLE RENAME COLUMN command. Write the below command in the SQLite3 Command Line Window.

ALTER TABLE Students RENAME COLUMN studID TO Student_ID;

In the above statement, firstly we are using the command ALTER TABLE and then passing the table name Students then we are again using the rest of the command RENAME COLUMN and then passing the existing column name which we want to change, then TO and the new name of that column.

After executing the above command. We will use the SELECT command to see the changes:

SELECT * FROM Students;

Output:

As we can see in the output, the studID column has been renamed into Student_ID using the ALTER TABLE RENAME COLUMN command in just a single line.

Example 2: Renaming a Column Using the Traditional (Old) Method

The Traditional i.e. the Old way, before the introduction of the ALTER TABLE RENAME COLUMN command, to rename a column was a very lengthy and tedious task. We will see that process in a stepwise manner.

Step 1: Considering the table already exists and populated, we will jump right into the main thing. We will create a new table with a name which is close to the name of the existing table and make sure to use the new name of the column whilst making the table, for this example, we will use the name Students_Copy and use Student_ID as the first columns name.

Write the below command to create the table:

CREATE TABLE Students_Copy (
Student_ID INTEGER,
FirstName TEXT,
LastName TEXT,
Class INTEGER,
Section TEXT
);

Output:

Step 2: Now it’s time to populate the new table by fetching all the values from the old table. Write the below command to do the same:

INSERT INTO Students_Copy (Student_ID, FirstName, LastName, Class, Section) 
SELECT Stud_ID, FirstName, LastName, Class, Section FROM Students;

Now, we will use the SELECT command to check everything is copied or not:

SELECT * FROM Students_Copy;

Output:

Step 3: This step is just a checking step, user might skip this one. In this step, we will check that both the Students and the Students_Copy tables are present in our database. For that, we will use the .tables dot-command.

.tables

Output:

This step is just to ensure everything is working fine at this point.

Step 4: Now, we will drop the previous table using the DROP command. This will now ensure that no duplicate table with a little difference in their name and column-name stays in the same database consisting of same values. Write the below command –

DROP TABLE Students;

After that we will again check using the .tables dot-command to check if the table has been dropped successfully or not.

.tables

Output:

Step 5: This is the last step, here we will just rename the entire table i.e. Students_Copy into Students (which was the main table) using the ALTER TABLE command, this will make the real table hold the updated and renamed column.

ALTER TABLE Students_Copy RENAME TO Students;

Now we will use tables command to check everything is fine or not

.tables

Output:

Finally, we are using the Select command to print the contents of the Students table to check if the column name has been updated or not –

SELECT * FROM Students;

Output:

Conclusion

We saw in this article how much work has been decreased by the introduction of the ALTER TABLE RENAME COLUMN, users can rename the column name of an already existing table using the above mentioned command in a single line. Traditionally the users need to carry out a very lengthy process to rename the column of a table, this lengthy process is tiring, time consuming, and a wastage of memory and processing power. The main problem that arises due to the use of the traditional process is that if the table consists of a huge number of data, then the entire process might get stuck or the actual database might face some serious issues.