In this article we will show how to add a new column or multiple new columns to an existing table in the SQLite database.
The SQLite ADD COLUMN keyword is used to add a new column to an existing table. The new column is always appended to the end of the existing column list.
When a new column is added to an existing table with a specified NOT NULL constraint, then the column must have a default value other than NULL.
When a new column is added to an existing table with a CHECK constraint or a NOT NULL constraint, the added constraints are tested against all pre-existing rows in the table and ADD COLUMN fails if any constraint fails.
Syntax
Here is the syntax of SQLite ADD COLUMN:
ALTER TABLE table_name ADD COLUMN column_name column-def;
ALTER TABLE represents the start command to start modifying the table structure, then it is followed by the table name. Modifying the table to add a new column always starts with the ALTER TABLE.
ADD COLUMN represents the type of modification that will be performed on the table structure.
column-def represents the rules that apply to new columns, such as date type, constraints, default value.
Example
Add a column to an existing table in SQLite.
CREATE TABLE courses ( ID INTEGER PRIMARY KEY NOT NULL, NAME VARCHAR(100) NOT NULL UNIQUE, PRICE INTEGER NOT NULL ); ALTER TABLE courses ADD COLUMN DISCOUNT INTEGER;
Add multiple columns to an existing table in SQLite.
CREATE TABLE courses ( ID INTEGER PRIMARY KEY NOT NULL, NAME VARCHAR(100) NOT NULL UNIQUE, PRICE INTEGER NOT NULL ); ALTER TABLE courses ADD COLUMN DISCOUNT INTEGER NOT NULL DEFAULT 0; ALTER TABLE courses ADD COLUMN DESCRIPTION TEXT;
SQLite’s syntax does not allow adding multiple columns from a single ALTER TABLE command. So in order to add more columns to an existing table, the command ALTER TABLE ADD COLUMN will be written separately for each column.
