SQLite ALTER TABLE

Summary: in this tutorial, you will learn how to use SQLite ALTER TABLE statement to change the structure of an existing table.

Unlike SQL-standard and other database systems, SQLite supports a very limited functionality of the ALTER TABLE statement.

By using an SQLite ALTER TABLE statement, you can perform two actions:

1. Rename a table.
2. Add a new column to a table.
3. Rename a column (added supported in version 3.20.0)

Using SQLite ALTER TABLE to rename a table

To rename a table, you use the following ALTER TABLE RENAME TO statement:

ALTER TABLE existing_table
RENAME TO new_table;

These are important points you should know before you rename a table:

• The ALTER TABLE only renames a table within a database. You cannot use it to move the table between the attached databases.
• The database objects such as indexes and triggers associated with the table will be associated with the new table.
• If a table is referenced by views or statements in triggers, you must manually change the definition of views and triggers.

Let’s take an example of renaming a table.

First, create a table named devices that has three columns: name, model, serial; and insert a new row into the devices table.

CREATE TABLE devices (
   name TEXT NOT NULL,
   model TEXT NOT NULL,
   Serial INTEGER NOT NULL UNIQUE
);

INSERT INTO devices (name, model, serial)
VALUES('HP ZBook 17 G3 Mobile Workstation','ZBook','SN-2015');

Try It

Second, use the ALTER TABLE RENAME TO statement to change the devices table to equipment table as follows:

ALTER TABLE devices
RENAME TO equipment;

Try It

Third, query data from the equipment table to verify the RENAME operation.

SELECT
	name,
	model,
	serial
FROM
	equipment;

Try It

Using SQLite ALTER TABLE to add a new column to a table

You can use the SQLite ALTER TABLE statement to add a new column to an existing table. In this scenario, SQLite appends the new column at the end of the existing column list.

The following illustrates the syntax of ALTER TABLE ADD COLUMN statement:

ALTER TABLE table_name
ADD COLUMN column_definition;

There are some restrictions on the new column:

• The new column cannot have a UNIQUE or PRIMARY KEY constraint.
• If the new column has a NOT NULL constraint, you must specify a default value for the column other than a NULL value.
• The new column cannot have a default of CURRENT_TIMESTAMP, CURRENT_DATE, and CURRENT_TIME, or an expression.
• If the new column is a foreign key and the foreign key constraint check is enabled, the new column must accept a default value NULL.

For example, you can add a new column named location to the equipment table:

ALTER TABLE equipment 
ADD COLUMN location text;

Try It

Using SQLite ALTER TABLE to rename a column

SQLite added the support for renaming a column using ALTER TABLE RENAME COLUMN statement in version 3.20.0

The following shows the syntax of the ALTER TABLE RENAME COLUMN statement:

ALTER TABLE table_name
RENAME COLUMN current_name TO new_name;

For more information on how to rename a column, check it out the renaming column tutorial.

Using SQLite ALTER TABLE for other actions

If you want to perform other actions e.g., drop a column, you use the following steps:

The following script illustrates the steps above:

-- disable foreign key constraint check
PRAGMA foreign_keys=off;

-- start a transaction
BEGIN TRANSACTION;

-- Here you can drop column
CREATE TABLE IF NOT EXISTS new_table( 
   column_definition,
   ...
);
-- copy data from the table to the new_table
INSERT INTO new_table(column_list)
SELECT column_list
FROM table;

-- drop the table
DROP TABLE table;

-- rename the new_table to the table
ALTER TABLE new_table RENAME TO table; 

-- commit the transaction
COMMIT;

-- enable foreign key constraint check
PRAGMA foreign_keys=on;

SQLite ALTER TABLE DROP COLUMN example

SQLite does not support ALTER TABLE DROP COLUMN statement. To drop a column, you need to use the steps above.

The following script creates two tables users and favorites, and insert data into these tables:

CREATE TABLE users(
	UserId INTEGER PRIMARY KEY,
	FirstName TEXT NOT NULL,
	LastName TEXT NOT NULL,
	Email TEXT NOT NULL,
	Phone TEXT NOT NULL
);

CREATE TABLE favorites(
	UserId INTEGER,
	PlaylistId INTEGER,
	FOREIGN KEY(UserId) REFERENCES users(UserId),
	FOREIGN KEY(PlaylistId) REFERENCES playlists(PlaylistId)
);

INSERT INTO users(FirstName, LastName, Email, Phone)
VALUES('John','Doe','john.doe@example.com','408-234-3456');

INSERT INTO favorites(UserId, PlaylistId)
VALUES(1,1);

The following statement returns data from the users table:

SELECT * FROM users;

And the following statement returns the data from the favorites table:

SELECT * FROM favorites;

Suppose, you want to drop the column phone of the users table.

First, disable the foreign key constraint check:

PRAGMA foreign_keys=off;

Second, start a new transaction:

BEGIN TRANSACTION;

Third, create a new table to hold data of the users table except for the phone column:

CREATE TABLE IF NOT EXISTS persons (
	UserId INTEGER PRIMARY KEY,
	FirstName TEXT NOT NULL,
	LastName TEXT NOT NULL,
	Email TEXT NOT NULL
);

Fourth, copy data from the users to persons table:

INSERT INTO persons(UserId, FirstName, LastName, Email)
SELECT UserId, FirstName, LastName, Email 
FROM users;

Fifth, drop the users table:

DROP TABLE users;

Sixth, rename the persons table to users table:

ALTER TABLE persons RENAME TO users;

Seventh, commit the transaction:

COMMIT;

Eighth, enable the foreign key constraint check:

PRAGMA foreign_keys=on;

Here is the users table after dropping the phone column:

SELECT * FROM users;

Summary

• Use the ALTER TABLE statement to modify the structure of an existing table.
• Use ALTER TABLE table_name RENAME TO new_name statement to rename a table.
• Use ALTER TABLE table_name ADD COLUMN column_definition statement to add a column to a table.
• Use ALTER TABLE table_name RENAME COLUMN current_name TO new_name to rename a column.