SQLite UNIQUE Constraint: Single and Multi-Column, NULLs and Existing Tables

Ninad Pathak

October 7, 2026

A UNIQUE constraint makes SQLite refuse a second row with the same value, like a duplicate email address. The catch is that SQLite can’t add one with ALTER TABLE. In this article I’ll walk you through setting one up when you create a table, and adding one to a table that already has data.

TLDR

Add UNIQUE after a column, or UNIQUE (col1, col2) at the end of CREATE TABLE for a combination of columns.
A duplicate fails with UNIQUE constraint failed: table.column.
NULLs don’t count as duplicates, so a UNIQUE column can hold many NULLs. Add NOT NULL if that’s not what you want.

To add uniqueness to an existing table, remove the duplicates, then run CREATE UNIQUE INDEX. ALTER TABLE … ADD CONSTRAINT is a syntax error in SQLite.
Handle duplicates on insert with INSERT OR IGNORE or ON CONFLICT … DO UPDATE.

What is a UNIQUE constraint in SQLite?

A UNIQUE constraint says no two rows may have the same value in a column, or the same combination of values in a group of columns. SQLite’s documentation: “For each UNIQUE constraint on the table, each row must contain a unique combination of values in the columns identified by the UNIQUE constraint” (SQLite CREATE TABLE).

UNIQUE vs PRIMARY KEY

	`UNIQUE`	`PRIMARY KEY`
How many per table	Any number	One
NULLs	Allowed, and each NULL is distinct	Should be NOT NULL, but SQLite allows NULL except in `INTEGER PRIMARY KEY`, `STRICT` and `WITHOUT ROWID` tables
How SQLite enforces it	A unique index	A unique index, or the rowid for `INTEGER PRIMARY KEY`

The NULL rule for primary keys is an SQLite quirk. The documentation says PRIMARY KEY should imply NOT NULL, but “due to a bug in some early versions, this is not the case in SQLite”. See SQLite primary keys.

SQLite UNIQUE constraint syntax

-- Column constraint: one column
CREATE TABLE table_name (
  column_name TYPE UNIQUE
);

-- Table constraint: a combination of columns, optionally named
CREATE TABLE table_name (
  column_1 TYPE,
  column_2 TYPE,
  CONSTRAINT constraint_name UNIQUE (column_1, column_2)
);

-- Existing table: a unique index does the same job
CREATE UNIQUE INDEX index_name ON table_name (column_1, column_2);

The documentation calls a UNIQUE column and a separate CREATE UNIQUE INDEX on it “logically equivalent”.

How to add a UNIQUE constraint

To show you each step, I will use a small app database with users and warehouse inventory.

Step 1: Make one column unique

Add UNIQUE after the column type. The second ada@example.com fails:

sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, name TEXT);
sqlite> INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada'), ('grace@example.com', 'Grace');
sqlite> INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada again');
Error: stepping, UNIQUE constraint failed: users.email (19)

Step 2: Make a combination of columns unique

A table constraint lists several columns. Product 10 can be in warehouse 1 and warehouse 2, but only once in each:

sqlite> CREATE TABLE inventory (id INTEGER PRIMARY KEY, product_id INTEGER, warehouse_id INTEGER, quantity INTEGER, CONSTRAINT uq_product_warehouse UNIQUE (product_id, warehouse_id));
sqlite> INSERT INTO inventory (product_id, warehouse_id, quantity) VALUES (10, 1, 5), (10, 2, 7), (20, 1, 3);
sqlite> INSERT INTO inventory (product_id, warehouse_id, quantity) VALUES (10, 1, 8);
Error: stepping, UNIQUE constraint failed: inventory.product_id, inventory.warehouse_id (19)

Step 3: Check the index SQLite created

SQLite enforces each UNIQUE constraint with an automatic index named sqlite_autoindex_<table>_<n>. PRAGMA index_list shows it with unique set to 1 and origin u:

sqlite> SELECT name, tbl_name FROM sqlite_schema WHERE type = 'index';
name                          tbl_name
----------------------------  ---------
sqlite_autoindex_users_1      users
sqlite_autoindex_inventory_1  inventory
sqlite> PRAGMA index_list(inventory);
seq  name                          unique  origin  partial
---  ----------------------------  ------  ------  -------
0    sqlite_autoindex_inventory_1  1       u       0

Examples

Each example below runs in the same database as the steps above.

Allow many NULLs in a UNIQUE column

“For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs.” Two contacts without a phone number are fine. A repeated real number isn’t:

sqlite> CREATE TABLE contacts (id INTEGER PRIMARY KEY, name TEXT, phone TEXT UNIQUE);
sqlite> INSERT INTO contacts (name, phone) VALUES ('Ada', NULL), ('Grace', NULL), ('Linus', '555-0100');
sqlite> SELECT * FROM contacts;
id  name   phone
--  -----  --------
1   Ada    NULL
2   Grace  NULL
3   Linus  555-0100
sqlite> INSERT INTO contacts (name, phone) VALUES ('Alan', '555-0100');
Error: stepping, UNIQUE constraint failed: contacts.phone (19)

Add a UNIQUE constraint to an existing table

SQLite’s ALTER TABLE can’t add constraints. Here the products table already has a duplicate SKU, so the unique index fails too:

sqlite> CREATE TABLE products (id INTEGER PRIMARY KEY, sku TEXT, name TEXT);
sqlite> INSERT INTO products (sku, name) VALUES ('A-1', 'Pen'), ('B-2', 'Notebook'), ('A-1', 'Blue pen');
sqlite> ALTER TABLE products ADD CONSTRAINT uq_sku UNIQUE (sku);
Error: in prepare, near "CONSTRAINT": syntax error
  ALTER TABLE products ADD CONSTRAINT uq_sku UNIQUE (sku);
             error here ---^
sqlite> CREATE UNIQUE INDEX uq_products_sku ON products(sku);
Error: stepping, UNIQUE constraint failed: products.sku (19)

Find the duplicates, keep one row per SKU, then create the index. From then on it rejects duplicates like a UNIQUE constraint would:

sqlite> SELECT sku, count(*) AS copies FROM products GROUP BY sku HAVING count(*) > 1;
sku  copies
---  ------
A-1  2
sqlite> DELETE FROM products WHERE id NOT IN (SELECT min(id) FROM products GROUP BY sku);
sqlite> CREATE UNIQUE INDEX uq_products_sku ON products(sku);
sqlite> INSERT INTO products (sku, name) VALUES ('B-2', 'Another notebook');
Error: stepping, UNIQUE constraint failed: products.sku (19)

Decide which duplicate to keep before you delete. This example keeps the lowest id.

Remove a UNIQUE constraint

A uniqueness rule you added with CREATE UNIQUE INDEX comes off with DROP INDEX:

sqlite> DROP INDEX uq_products_sku;
sqlite> INSERT INTO products (sku, name) VALUES ('B-2', 'Another notebook'); SELECT * FROM products;
id  sku  name
--  ---  ----------------
1   A-1  Pen
2   B-2  Notebook
3   B-2  Another notebook

A UNIQUE written inside CREATE TABLE can’t be dropped this way:

sqlite> DROP INDEX sqlite_autoindex_users_1;
Error: in prepare, index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped

To remove it, rebuild the table without it. Create the new table and copy the rows across. Then drop the old table and rename the new one. See SQLite CREATE TABLE.

Skip or update duplicates on insert

INSERT OR IGNORE skips rows that would break the constraint and keeps the rest:

sqlite> INSERT OR IGNORE INTO users (email, name) VALUES ('ada@example.com', 'Ignored'), ('linus@example.com', 'Linus'); SELECT * FROM users;
id  email              name
--  -----------------  -----
1   ada@example.com    Ada
2   grace@example.com  Grace
3   linus@example.com  Linus

ON CONFLICT (column) DO UPDATE updates the existing row instead. More in SQLite upsert:

sqlite> INSERT INTO users (email, name) VALUES ('grace@example.com', 'Grace Hopper') ON CONFLICT(email) DO UPDATE SET name = excluded.name; SELECT * FROM users;
id  email              name
--  -----------------  ------------
1   ada@example.com    Ada
2   grace@example.com  Grace Hopper
3   linus@example.com  Linus

Make uniqueness case-insensitive

By default 'Ada' and 'ADA' are different values. COLLATE NOCASE on the column makes the constraint treat them as the same:

sqlite> CREATE TABLE accounts (id INTEGER PRIMARY KEY, username TEXT UNIQUE COLLATE NOCASE);
sqlite> INSERT INTO accounts (username) VALUES ('Ada');
sqlite> INSERT INTO accounts (username) VALUES ('ADA');
Error: stepping, UNIQUE constraint failed: accounts.username (19)

Unique only among some rows

A partial unique index applies to the rows matching its WHERE. Here an email must be unique among live members, while deleted members can repeat it (partial indexes):

sqlite> CREATE TABLE members (id INTEGER PRIMARY KEY, email TEXT, deleted INTEGER NOT NULL DEFAULT 0);
sqlite> CREATE UNIQUE INDEX uq_live_email ON members(email) WHERE deleted = 0;
sqlite> INSERT INTO members (email, deleted) VALUES ('ada@example.com', 1), ('ada@example.com', 0);
sqlite> INSERT INTO members (email, deleted) VALUES ('ada@example.com', 0);
Error: stepping, UNIQUE constraint failed: members.email (19)

Edge cases

The quirks below come from SQLite’s history, not from the SQL standard.

NULL in a text primary key. Unlike UNIQUE, a primary key is meant to reject NULL. SQLite still allows it in a TEXT PRIMARY KEY on an ordinary table:

sqlite> CREATE TABLE codes (code TEXT PRIMARY KEY, label TEXT UNIQUE);
sqlite> INSERT INTO codes VALUES (NULL, 'x'), (NULL, 'y'); SELECT code, label, typeof(code) FROM codes;
code  label  typeof(code)
----  -----  ------------
NULL  x      null
NULL  y      null

No expressions in a UNIQUE constraint. The documentation says “the use of expressions in an indexed-column of a UNIQUE table-constraint is not supported”. Use a unique index on the expression instead, as shown in SQLite CREATE INDEX.

Conclusion

Put UNIQUE in CREATE TABLE when you design the table, and add NOT NULL if missing values aren’t allowed. For a table that already exists, clean out the duplicates and use CREATE UNIQUE INDEX, which is the same rule under another name. Pair the constraint with INSERT OR IGNORE or an upsert when duplicates are expected.