SQLite: ALTER TABLE Statement

This SQLite post explains how to use the SQLite ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table (with syntax and examples).

Description

The SQLite ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The SQLite ALTER TABLE statement is also used to rename a table.

Add column in table

Syntax

The syntax to ADD A COLUMN in a table in SQLite (using the ALTER TABLE statement) is:

ALTER TABLE table_name
  ADD new_column_name column_definition;

table_name

The name of the table to modify.

new_column_name

The name of the new column to add to the table.

column_definition

The datatype and definition of the column (NULL or NOT NULL, etc).

Example

Let's look at an example that shows how to add a column in a SQLite table using the ALTER TABLE statement.

For example:

ALTER TABLE employees
  ADD status VARCHAR;

This SQLite ALTER TABLE example will add a column called status to the employees table. It will be created as a column that allows NULL values.

Modify column in table

You can not use the ALTER TABLE statement to modify a column in SQLite. Instead you will need to rename the table, create a new table, and copy the data into the new table.

Syntax

The syntax to MODIFY A COLUMN in a table in SQLite is:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1 (
( column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

INSERT INTO table1 (column1, column2, ... column_n)
  SELECT column1, column2, ... column_n
  FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

Example

Let's look at an example that shows how to modify a column in a SQLite table.

For example, if we had an employees table that had a column called last_name that was defined as a CHAR datatype:

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name CHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE
);

And we wanted to change the datatype of the last_name field to VARCHAR, we could do the following:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE employees RENAME TO _employees_old;

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE
);

INSERT INTO employees (employee_id, last_name, first_name, hire_date)
  SELECT employee_id, last_name, first_name, hire_date
  FROM _employees_old;

COMMIT;

PRAGMA foreign_keys=on;

This example will rename our existing employees table to _employees_old. Then it will create the new employees table with the last_name field defined as a VARCHAR datatype. Then it will insert all of the data from the _employees_old table into the employees table.

Drop column in table

You can not use the ALTER TABLE statement to drop a column in a table. Instead you will need to rename the table, create a new table, and copy the data into the new table.

Syntax

The syntax to DROP A COLUMN in a table in SQLite is:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1 (
( column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

INSERT INTO table1 (column1, column2, ... column_n)
  SELECT column1, column2, ... column_n
  FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

Example

Let's look at an example that shows how to drop a column in a SQLite table.

For example, if we had an employees table that was defined as follows:

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE
);

And we wanted to drop the column called hire_date, we could do the following:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE employees RENAME TO _employees_old;

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR
);

INSERT INTO employees (employee_id, last_name, first_name)
  SELECT employee_id, last_name, first_name
  FROM _employees_old;

COMMIT;

PRAGMA foreign_keys=on;

This example will rename our existing employees table to _employees_old. Then it will create the new employees table with the hire_date field removed. Then it will insert all of the data (excluding the hire_date field) from the _employees_old table into the employees table.

Rename column in table

You can not use the ALTER TABLE statement to rename a column in SQLite. Instead you will need to rename the table, create a new table, and copy the data into the new table.

Syntax

The syntax to RENAME A COLUMN in a table in SQLite is:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1 (
( column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

INSERT INTO table1 (column1, column2, ... column_n)
  SELECT column1, column2, ... column_n
  FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

Example

Let's look at an example that shows how to rename a column in a SQLite table.

For example, if we had an employees table that was defined as follows:

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE
);

And we wanted to rename the hire_date field to start_date, we could do the following:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE employees RENAME TO _employees_old;

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  start_date DATE
);

INSERT INTO employees (employee_id, last_name, first_name, start_date)
  SELECT employee_id, last_name, first_name, hire_date
  FROM _employees_old;

COMMIT;

PRAGMA foreign_keys=on;

This example will rename our existing employees table to _employees_old. Then it will create the new employees table with the hire_date field called start_date. Then it will insert all of the data from the _employees_old table into the employees table.

Rename table

Syntax

To rename a table, the SQLite ALTER TABLE syntax is:

ALTER TABLE table_name
  RENAME TO new_table_name;

table_name

The table to rename.

new_table_name

The new table name.

Example

Let's look at an example that shows how to rename a table in SQLite using the ALTER TABLE statement.

For example:

ALTER TABLE employees
  RENAME TO staff;

This ALTER TABLE example will rename the employees table to staff.