SQLite | Tables | Rename a Table and Add or Remove Columns

Tags:

This article explains how to rename an existing table and add or remove columns.

Renaming a Table

Use ALTER TABLE to rename an existing table. The syntax is as follows.

ALTER TABLE table_name RENAME TO new_table_name;

For example, rename table_old to table_new as follows.

alter table table_old rename to table_new;

Use .tables to list the current tables. This example contains customer and customer2.

sqlite> .table
customer   customer2
sqlite>

Rename customer2 to user.

alter table customer2 rename to user;

sqlite> 
sqlite> alter table customer2 rename to user;
sqlite>

Run .tables again to confirm that the table list now contains customer and user.

sqlite> .table
customer  user    
sqlite>

Adding a Column

Use ALTER TABLE to add a column to an existing table. The syntax is as follows.

ALTER TABLE table_name ADD COLUMN column_name [data_type];

The SQLite version used in this example supports adding columns but not DROP COLUMN.

The new column is appended to the table and must satisfy these conditions.

  1. It cannot have a PRIMARY KEY or UNIQUE constraint.
  2. Its DEFAULT cannot be CURRENT_TIME, CURRENT_DATE, or CURRENT_TIMESTAMP.
  3. A NOT NULL column must have a non-NULL default.

For example, add new_column to mytable as follows.

alter table mytable add column new_column;

Add a TEXT column named address to the existing user table. First inspect its current schema with .schema.

.schema user

sqlite> .schema user
CREATE TABLE IF NOT EXISTS "user" (id integer, name text);
sqlite>

Add the address column to user.

alter table user add column address text;

sqlite> 
sqlite> alter table user add column address text;
sqlite>

Run .schema again to confirm that address was added.

.schema user

sqlite> .schema user
CREATE TABLE IF NOT EXISTS "user" (id integer, name text, address text);
sqlite>

Removing a Column

Older SQLite versions do not support DROP COLUMN. The documented workaround uses DROP TABLE and ALTER TABLE RENAME.

The workaround creates a replacement table and copies the desired data into it.

First inspect the current user schema.

sqlite> .schema user
CREATE TABLE IF NOT EXISTS "user" (id integer, name text, address text);
sqlite>

Create user2, then copy data from user into it.

sqlite> create table user2 (id integer, name text);
sqlite> 
sqlite> insert into user2 (id, name) select id, name from user;

Drop the original user table and rename user2 to user.

sqlite> drop table user;
sqlite> 
sqlite> alter table user2 rename to user;

Run .schema again to confirm that the address column is gone.

sqlite> .schema user
CREATE TABLE IF NOT EXISTS "user" (id integer, name text);