SQLite ALTER TABLE Keyword

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE keyword 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 keyword 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:

• 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

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.