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.

SQLite ALTER TABLE – Renaming a table

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

ALTER TABLE existing_table
RENAME TO new_table;

There are some important points you should know before you rename a table:

• The ALTER TABLE only renames a table within a database. It cannot be used to move the table between attached databases.
• The database objects such as indexes and triggers associated with the table are attached to 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, you can 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, you can query data from the equipment table to verify the RENAME operation.

SELECT
	name,
	model,
	serial
FROM
	equipment;

Try It

SQLite ALTER TABLE – Adding a new column to a table

Another action you can perform with the SQLite ALTER TABLE statement is to add a new column to an existing table. In this case, SQLite appends the new column at the end of the existing columns list.

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

ALTER TABLE table
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 of NULL.

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

ALTER TABLE equipment 
ADD COLUMN location text;

Try It

SQLite ALTER TABLE – Other actions

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

The following illustrates the steps above:

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

-- start a transaction
BEGIN TRANSACTION;

-- Here you can drop column or rename 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;

In this tutorial, you have learned how to use the SQLite ALTER TABLE statement to rename a table and to add a new column to a table.