SQLite ALTER TABLE ADD COLUMN: Syntax, Multiple Columns and Rules (Tested)

Adding a column to an SQLite table that already holds data doesn’t mean rebuilding it: ALTER TABLE … ADD COLUMN adds it in place. SQLite does limit what the new column can be, and it adds one column per statement. In this article I’ll walk you through using it, including how to add several columns and the rules SQLite enforces.

TLDR

• Add a column with ALTER TABLE table_name ADD COLUMN column_name type;. Existing rows get NULL, or the column’s default.
• SQLite adds one column per statement. To add several, run one ALTER TABLE per column, inside a transaction.
• The new column always goes at the end. There’s no AFTER or FIRST.
• The new column can’t be PRIMARY KEY or UNIQUE, and a NOT NULL column needs a non-NULL default.
• There’s no ADD COLUMN IF NOT EXISTS. Check pragma_table_info first.

What is ALTER TABLE ADD COLUMN?

ALTER TABLE … ADD COLUMN adds a new column to an existing table without touching the rows already in it. SQLite’s documentation says “the new column is always appended to the end of the list of existing columns” (ALTER TABLE).

For a plain column, SQLite only edits the table’s CREATE TABLE text in the schema, so “such commands will run as quickly on a table with 10 million rows as on a table with 1 row”. A new column with a CHECK constraint is the exception, because SQLite has to test it against every existing row.

SQLite ALTER TABLE ADD COLUMN syntax

ALTER TABLE table_name ADD [COLUMN] column_name [type] [column_constraint ...];

The word COLUMN is optional. The column definition takes the same form as in CREATE TABLE, with these restrictions from the SQLite docs:

Not allowed on the new column	What you get
PRIMARY KEY	Cannot add a PRIMARY KEY column
UNIQUE	Cannot add a UNIQUE column
NOT NULL without a non-NULL default	Cannot add a NOT NULL column with default value NULL
A default of CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME or an expression in parentheses	Cannot add a column with non-constant default
GENERATED ALWAYS … STORED (VIRTUAL is fine)	cannot add a STORED column
A REFERENCES clause with a non-NULL default, while foreign keys are on	Cannot add a REFERENCES column with non-NULL default value

Each of these failed in the test run: the STORED column under Examples, the others under edge cases below.

How to add a column to an existing table

To show you how this works, I will use a small sales table that already has three rows:

sqlite> CREATE TABLE sales (id INTEGER PRIMARY KEY, product TEXT NOT NULL, quantity INTEGER NOT NULL, price REAL NOT NULL);
sqlite> INSERT INTO sales (product, quantity, price) VALUES ('Widget', 10, 19.99), ('Gadget', 5, 29.99), ('Widget', 8, 19.99);

Step 1: Add the column

sqlite> ALTER TABLE sales ADD COLUMN customer TEXT;
sqlite> SELECT * FROM sales;
id  product  quantity  price  customer
--  -------  --------  -----  --------
1   Widget   10        19.99
2   Gadget   5         29.99
3   Widget   8         19.99

The existing rows now have a customer column, set to NULL.

Step 2: Check the new table structure

pragma_table_info lists every column, and customer is now last. See SQLite PRAGMA for more on pragmas:

sqlite> SELECT cid, name, type, "notnull", dflt_value, pk FROM pragma_table_info('sales');
cid  name      type     notnull  dflt_value  pk
---  --------  -------  -------  ----------  --
0    id        INTEGER  0                    1
1    product   TEXT     1                    0
2    quantity  INTEGER  1                    0
3    price     REAL     1                    0
4    customer  TEXT     0                    0

Step 3: Fill in values for the existing rows

An UPDATE sets the new column on the rows that were already there:

sqlite> UPDATE sales SET customer = 'Acme' WHERE product = 'Widget';
sqlite> UPDATE sales SET customer = 'Globex' WHERE product = 'Gadget';
sqlite> SELECT id, product, customer FROM sales;
id  product  customer
--  -------  --------
1   Widget   Acme
2   Gadget   Globex
3   Widget   Acme

Examples

These examples keep working on the same sales table.

Add a column with a default value

With a DEFAULT, every existing row gets that value straight away. This is also how you add a NOT NULL column:

sqlite> ALTER TABLE sales ADD COLUMN status TEXT NOT NULL DEFAULT 'paid';
sqlite> SELECT id, product, status FROM sales;
id  product  status
--  -------  ------
1   Widget   paid
2   Gadget   paid
3   Widget   paid

Add multiple columns

SQLite’s ALTER TABLE takes one ADD COLUMN per statement. A comma-separated list, as in MySQL, is a syntax error:

sqlite> ALTER TABLE sales ADD COLUMN region TEXT, ADD COLUMN discount REAL;
Error: in prepare, near ",": syntax error
  ALTER TABLE sales ADD COLUMN region TEXT, ADD COLUMN discount REAL;
                            error here ---^

Run one statement per column instead. Wrapping them in a transaction means either all the columns are added or none are:

sqlite> BEGIN; ALTER TABLE sales ADD COLUMN region TEXT; ALTER TABLE sales ADD COLUMN discount REAL NOT NULL DEFAULT 0; COMMIT;
sqlite> SELECT name FROM pragma_table_info('sales');
name
--------
id
product
quantity
price
customer
status
region
discount

Add a column only if it doesn’t exist

Adding a column that already exists fails, and SQLite has no IF NOT EXISTS for columns:

sqlite> ALTER TABLE sales ADD COLUMN customer TEXT;
Error: in prepare, duplicate column name: customer
sqlite> ALTER TABLE sales ADD COLUMN IF NOT EXISTS customer TEXT;
Error: in prepare, near "EXISTS": syntax error
  ALTER TABLE sales ADD COLUMN IF NOT EXISTS customer TEXT;
                        error here ---^

Check pragma_table_info first. It returns 1 when the column is there and 0 when it isn’t:

sqlite> SELECT count(*) AS has_customer FROM pragma_table_info('sales') WHERE name = 'customer';
has_customer
------------
1

In application code, run that check before the ALTER TABLE. Here’s a small Python helper:

import sqlite3

def add_column_if_missing(con, table, column, definition):
    names = [row[1] for row in con.execute(f'PRAGMA table_info("{table}")')]
    if column in names:
        return False
    con.execute(f'ALTER TABLE "{table}" ADD COLUMN "{column}" {definition}')
    return True

con = sqlite3.connect("t.db")
print(add_column_if_missing(con, "sales", "coupon", "TEXT"))
print(add_column_if_missing(con, "sales", "coupon", "TEXT"))
con.close()

$ python3 add_column_if_missing.py
True
False

The first call adds the column and returns True. The second finds it and does nothing.

Add a computed column

A VIRTUAL generated column is allowed, and SQLite computes it whenever you read it:

sqlite> ALTER TABLE sales ADD COLUMN total REAL GENERATED ALWAYS AS (quantity * price) VIRTUAL;
sqlite> SELECT id, quantity, price, total FROM sales;
id  quantity  price  total
--  --------  -----  ------
1   10        19.99  199.9
2   5         29.99  149.95
3   8         19.99  159.92
sqlite> ALTER TABLE sales ADD COLUMN total2 REAL GENERATED ALWAYS AS (quantity * price) STORED;
Error: stepping, cannot add a STORED column

Edge cases

Each case below except the last failed in the test run, and each has a workaround. The column-position rule comes from the docs.

NOT NULL without a default

sqlite> ALTER TABLE sales ADD COLUMN note TEXT NOT NULL;
Error: stepping, Cannot add a NOT NULL column with default value NULL

Give the column a default, as in the status example above.

A CURRENT_TIMESTAMP or expression default

sqlite> ALTER TABLE sales ADD COLUMN created_at TEXT DEFAULT CURRENT_TIMESTAMP;
Error: stepping, Cannot add a column with non-constant default
sqlite> ALTER TABLE sales ADD COLUMN batch INTEGER DEFAULT (1 + 1);
Error: stepping, Cannot add a column with non-constant default

Add the column without the default, then fill the existing rows yourself:

sqlite> ALTER TABLE sales ADD COLUMN created_at TEXT;
sqlite> UPDATE sales SET created_at = '2026-10-07 09:00:00' WHERE created_at IS NULL;
sqlite> SELECT id, created_at FROM sales;
id  created_at
--  -------------------
1   2026-10-07 09:00:00
2   2026-10-07 09:00:00
3   2026-10-07 09:00:00

New rows won’t get a timestamp automatically this way. To get a real DEFAULT CURRENT_TIMESTAMP, rebuild the table with the steps in the SQLite docs.

PRIMARY KEY and UNIQUE columns

sqlite> ALTER TABLE sales ADD COLUMN code INTEGER PRIMARY KEY;
Error: in prepare, Cannot add a PRIMARY KEY column
sqlite> ALTER TABLE sales ADD COLUMN sku TEXT UNIQUE;
Error: in prepare, Cannot add a UNIQUE column

Add the column, then a unique index on it. The index enforces the same rule:

sqlite> ALTER TABLE sales ADD COLUMN sku TEXT;
sqlite> CREATE UNIQUE INDEX idx_sales_sku ON sales(sku);
sqlite> UPDATE sales SET sku = 'W-1' WHERE id = 1; UPDATE sales SET sku = 'W-1' WHERE id = 3;
Error: stepping, UNIQUE constraint failed: sales.sku (19)

CHECK constraints are tested against existing rows

Since SQLite 3.37.0, a new CHECK constraint has to hold for the rows already in the table. The first column below passes, because its value is NULL. The second fails, because its default of 0 breaks its own check:

sqlite> ALTER TABLE sales ADD COLUMN rating INTEGER CHECK (rating BETWEEN 1 AND 5);
sqlite> ALTER TABLE sales ADD COLUMN score INTEGER NOT NULL DEFAULT 0 CHECK (score > 0);
Error: stepping, CHECK constraint failed

A foreign key column with a default

With foreign keys on, a new REFERENCES column must default to NULL:

sqlite> CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
sqlite> PRAGMA foreign_keys = ON; ALTER TABLE sales ADD COLUMN customer_id INTEGER DEFAULT 1 REFERENCES customers(id);
Error: stepping, Cannot add a REFERENCES column with non-NULL default value

Adding a column in a specific position

There’s no AFTER column or FIRST. The new column always goes last. If the order matters, rebuild the table: create a new one with the columns in your order and copy the rows across. Then drop the old table and rename the new one. The SQLite docs list the full steps under making other kinds of table schema changes. The same rebuild is how you delete a column in older SQLite versions.

Here’s the final table after all the examples:

sqlite> .schema sales
CREATE TABLE sales (id INTEGER PRIMARY KEY, product TEXT NOT NULL, quantity INTEGER NOT NULL, price REAL NOT NULL, customer TEXT, status TEXT NOT NULL DEFAULT 'paid', region TEXT, discount REAL NOT NULL DEFAULT 0, "coupon" TEXT, total REAL GENERATED ALWAYS AS (quantity * price) VIRTUAL, created_at TEXT, sku TEXT, rating INTEGER CHECK (rating BETWEEN 1 AND 5));
CREATE UNIQUE INDEX idx_sales_sku ON sales(sku);

Conclusion

Use ALTER TABLE … ADD COLUMN for each new column, and wrap several of them in a transaction. Give a NOT NULL column a constant default. For UNIQUE, add a unique index after the column. For a timestamp default or a different column order, rebuild the table.

Further reading

• SQLite CREATE TABLE
• SQLite delete column
• SQLite PRAGMA
• SQLite UPDATE
• SQLite CREATE INDEX
• SQLite docs: ALTER TABLE

Tested with SQLite 3.51.0 and Python 3.9.6 on 7 October 2026.