SQLite Tutorial SQLite Advanced SQLite Database SQLite References

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:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

  • 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:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    2MarryNew York242750
    3JoParis272800
    4KimAmsterdam303100
    5RameshNew Delhi283000
    6HuangBeijing282800
  • 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:

    EmpIDEmployeeNameCityAgeSalary
    1JohnLondon253000
    2MarryNew York242750
    3JoParis272800
    4KimAmsterdam303100
    5RameshNew Delhi283000
    6HuangBeijing282800
  • 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:

    EmpIDEmployeeCityAgeSalaryAddress
    1JohnLondon253000
    2MarryNew York242750
    3JoParis272800
    4KimAmsterdam303100
    5RameshNew Delhi283000
    6HuangBeijing282800
  • 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:

    EmpIDEmployeeCityAge
    1JohnLondon25
    2MarryNew York24
    3JoParis27
    4KimAmsterdam30
    5RameshNew Delhi28
    6HuangBeijing28

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.


❮ SQLite Keywords