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:

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.