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:
- Rename a table.
- Add a new column to a table.
- 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 TABLEonly 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
);VALUES(‘HP ZBook 17 G3 Mobile Workstation’,‘ZBook’,‘SN-2015’);
Second, use the ALTER TABLE RENAME TO statement to change the devices table to equipment table as follows:
ALTER TABLE devices
RENAME TO equipment;
Third, query data from the equipment table to verify the RENAME operation.
SELECT
name,
model,
serial
FROM
equipment;
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
UNIQUEorPRIMARY KEYÂ constraint. - If the new column has a
NOT NULLconstraint, you must specify a default value for the column other than aNULLvalue. - The new column cannot have a default of
CURRENT_TIMESTAMP,CURRENT_DATE, andCURRENT_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;
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;BEGIN TRANSACTION;
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 TABLE table;
ALTER TABLE new_table RENAME TO table;
COMMIT;
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
);UserId INTEGER,
PlaylistId INTEGER,
FOREIGN KEY(UserId) REFERENCES users(UserId),
FOREIGN KEY(PlaylistId) REFERENCES playlists(PlaylistId)
);
VALUES(‘John’,‘Doe’,‘john.doe@example.com’,‘408-234-3456’);
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 TABLEstatement to modify the structure of an existing table. - Use
ALTER TABLE table_name RENAME TO new_namestatement to rename a table. - Use
ALTER TABLE table_name ADD COLUMN column_definitionstatement to add a column to a table. - Use
ALTER TABLE table_name RENAME COLUMN current_name TO new_nameto rename a column.