SQLite Rename Column Step-by-step

Summary: in this tutorial, you will learn step by step how to rename a column of a table in SQLite.

Different from other database systems, SQLite does not directly support the ALTER TABLE RENAME COLUMN statement that allows you to rename an existing column of a table.

To work around this, you follow these steps:

First, start a transaction.
Second, create a new table whose structure is the same as the original one except for the column that you want to rename.
Third, copy data from the original table to the new table.
Fourth, drop the original table.
Fifth, rename the new table to the original table.
Finally, commit the transaction.

SQLite rename column example

The following statement creates a new table called Locations in the database:

CREATE TABLE Locations(
	LocationId INTEGER PRIMARY KEY,
	Address TEXT NOT NULL,
	District TEXT NOT NULL,
	City TEXT NOT NULL,
	Country TEXT NOT NULL
);

CREATE TABLE Locations(

LocationId INTEGER PRIMARY KEY,

Address TEXT NOT NULL,

District TEXT NOT NULL,

City TEXT NOT NULL,

Country TEXT NOT NULL

);

And this INSERT statement inserts a new row into the Locations table:

INSERT INTO Locations(Address,City,State,Country)
VALUES('3960 North 1st Street','San Jose','CA','USA');

1 2	INSERT INTO Locations(Address,City,State,Country) VALUES('3960 North 1st Street','San Jose','CA','USA');

Suppose, you want to the change the column Address to Street.

First, start a new transaction:

BEGIN TRANSACTION;

1	BEGIN TRANSACTION;

Second, create a new table called LocationsTemp with the same structure as the Locations table except for the Address column:

CREATE TABLE LocationsTemp(
	LocationId INTEGER PRIMARY KEY,
	Street TEXT NOT NULL,
	City TEXT NOT NULL,
	State TEXT NOT NULL,
	Country TEXT NOT NULL
);

CREATE TABLE LocationsTemp(

LocationId INTEGER PRIMARY KEY,

Street TEXT NOT NULL,

City TEXT NOT NULL,

State TEXT NOT NULL,

Country TEXT NOT NULL

);

Third, copy data from the table Locations to LocationsTemp:

INSERT INTO LocationsTemp(Street,City,State,Country)
SELECT Address,City,State,Country
FROM Locations;

INSERT INTO LocationsTemp(Street,City,State,Country)

SELECT Address,City,State,Country

FROM Locations;

Fourth, drop the Locations table:

DROP TABLE Locations;

1	DROP TABLE Locations;

Fifth, rename the table LocationsTemp to Locations:

ALTER TABLE LocationsTemp 
RENAME TO Locations;

1 2	ALTER TABLE LocationsTemp RENAME TO Locations;

Finally, commit the transaction:

COMMIT;

COMMIT;

If you query the Locations table, you will see that the Address column has been renamed to Street:

SELECT * FROM Locations;

1	SELECT * FROM Locations;

Here is the output:

In this tutorial, you have learned step by step how to rename a column in a table in the SQLite database.

Was this tutorial helpful ?
Yes No

SQLite Rename Column

SQLite rename column example

About SQLite Tutorial

Looking for a tutorial…

Recent Tutorials

Site Links