SQLite Alter Table

In this article you will learn how to modify the structure of a table. SQLite supports a number of ways to modify the structure and contents of tables. The most common cases are those where you have to change the name of a table, add new columns or constraints to a table.

The SQLite ALTER TABLE command is used to add, remove, or modify columns in an existing table.
The ALTER TABLE command can also be used to add and drop various constraints on an existing table.

SQLite ALTER TABLE does not support complex changes in the structure or constraints of a table or its columns. To make complex changes you have to recreate the table, before the creation save the existing data in a temporary table, then delete the old table, then you can create the new table with the desired structure and finally you can copy back the data from the temporary table.

The basic changes that SQLite allows through ALTER TABLE are renaming the table name, ADD COLUMN, RENAME COLUMN or DROP COLUMN.

Syntax

Below we will show the syntax of ALTER TABLE, and how it is used to modify the structure of a table.

1. Rename table name

ALTER TABLE table_name 
RENAME TO new_table_name;

2. Adding a column to a SQLite table

ALTER TABLE table_name 
ADD COLUMN column_name datatype;

3. Adding a NOT NULL constraint to a SQLite table column

ALTER TABLE table_name 
ADD COLUMN column_name datatype NOT NULL DEFAULT default_value;

4. Adding a CHECK constraint to a SQLite table column

ALTER TABLE test1 
ADD COLUMN f TEXT CHECK (LENGTH (f) <= 50);

5. Dropping a SQLite table column

ALTER TABLE table_name 
DROP COLUMN column_name;

6. Change column datatype

SQLite allows you to alter a table and modify the datatype of a column using the ALTER TABLE statement. Here's a general outline of how change column datatype:

-- Step 1: Create a new temporary table with the desired schema
CREATE TABLE temp_table AS
SELECT 
    column1,
    column2,
    CAST(column_name AS new_datatype) AS column_name,
    column4,
    -- ... other columns
FROM your_table;

-- Step 2: Rename the original table
ALTER TABLE your_table RENAME TO old_table;

-- Step 3: Rename the temporary table to the original table name
ALTER TABLE temp_table RENAME TO your_table;

-- Step 4: Optionally, update indexes, constraints, and triggers as needed
-- ...

-- Step 5: Drop the old table if needed
-- DROP TABLE old_table;

SQLite ALTER TABLE command is used to make changes to the existing database tables. It can be used to add new columns, remove existing columns. SQLite ALTER TABLE command is very versatile and it can be used to make a wide variety of changes to SQLite database tables.