SQLite - ALTER TABLE

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite can be used to perform following alterations of an existing table:

Renaming a table
Renaming a column
Adding a column
Dropping a column

Syntax

The syntax for using ALTER TABLE statement in SQLite is given below:

/* RENAME Table */
ALTER TABLE table_name 
RENAME TO table_name_new;

/* RENAME Column */
ALTER TABLE table_name 
RENAME column_name TO column_name_new;

/* ADD Column */
ALTER TABLE table_name
ADD COLUMN column_name datatype;

/* DROP Column */
ALTER TABLE table_name
DROP COLUMN column_name;

Example:

Consider a database table called Employee with the following records:

EmpID	Name	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

Rename the table: The below statement is used to rename the Employee table to Employee_new:
```
ALTER TABLE Employee 
RENAME TO Employee_new;

--see the result
SELECT * FROM Employee_new;
```
This will produce the result as shown below:

EmpID Name City Age Salary

1 John London 25 3000

2 Marry New York 24 2750

3 Jo Paris 27 2800

4 Kim Amsterdam 30 3100

5 Ramesh New Delhi 28 3000

6 Huang Beijing 28 2800
Rename the column: The below statement is used to rename the Name column to EmployeeName:
```
ALTER TABLE Employee 
RENAME Name TO EmployeeName;

--see the result
SELECT * FROM Employee;
```
This will produce the result as shown below:

EmpID EmployeeName City Age Salary

1 John London 25 3000

2 Marry New York 24 2750

3 Jo Paris 27 2800

4 Kim Amsterdam 30 3100

5 Ramesh New Delhi 28 3000

6 Huang Beijing 28 2800
Add a column: To add a column named Address in the Employee table, the statement is given below:
```
ALTER TABLE Employee 
ADD COLUMN Address VARCHAR(255);

--see the result
SELECT * FROM Employee;
```
This will produce the result as shown below:

EmpID Employee City Age Salary Address

1 John London 25 3000

2 Marry New York 24 2750

3 Jo Paris 27 2800

4 Kim Amsterdam 30 3100

5 Ramesh New Delhi 28 3000

6 Huang Beijing 28 2800
Drop a column: To drop the Salary column from the Employee table, the following statement can be used:
```
ALTER TABLE Employee 
DROP COLUMN Salary;

--see the result
SELECT * FROM Employee;
```
This will produce the below mentioned result:

EmpID Employee City Age

1 John London 25

2 Marry New York 24

3 Jo Paris 27

4 Kim Amsterdam 30

5 Ramesh New Delhi 28

6 Huang Beijing 28

EmpID	Employee	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

EmpID	Employee	City	Age
1	John	London	25
2	Marry	New York	24
3	Jo	Paris	27
4	Kim	Amsterdam	30
5	Ramesh	New Delhi	28
6	Huang	Beijing	28

Modify a Column

SQLite does not support ALTER TABLE statement to modify an existing column of a table. However this can be achieved using following steps:

The foreign key constraint should be checked off
Rename the table to some other name
Create a new table with same structure but modified column
Copy the data from renamed table to new table
In the end, turn on the foreign key constraints

In the above example, the datatype of Age column is DECIMAL. To change the datatype of the column to DOUBLE, the following statement can be used:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE Employee RENAME TO Employee_old;

CREATE TABLE Employee (
  EmpID INT,
  Name VARCHAR(255),
  City VARCHAR(255),
  Age DOUBLE,
  Salary DECIMAL(6,2)
);

INSERT INTO Employee SELECT * FROM Employee_old;

COMMIT;

PRAGMA foreign_keys=on;

This will change the datatype of Age column from DECIMAL to DOUBLE. For a complete reference of all the data types available in SQLite database, see Data Types reference.