SQLite - ALTER COLUMN

SQLite does not support ALTER COLUMN keyword 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

Example:

Consider a database table called Employee with the following structure:

cid	name	type	notnull	pk
0	EmpID	INT	1	1
1	Name	VARCHAR(255)	1	0
2	City	VARCHAR(100)	0	0
3	Age	INT	0	0
4	Salary	DECIMAL(18,2)	0	0

In the above example, the datatype of Age column is INT. 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(100),
  Age DOUBLE,
  Salary DECIMAL(18,2)
);

INSERT INTO Employee SELECT * FROM Employee_old;

COMMIT;

PRAGMA foreign_keys=on;

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

❮ SQLite Keywords