SQLite Primary Key: INTEGER, Text and Composite Keys (Tested Examples)

Ninad Pathak

October 7, 2026

A TEXT PRIMARY KEY column in SQLite will store a NULL, and then a second one. Standard SQL wouldn’t allow that, but SQLite keeps the quirk for backwards compatibility. You can close it off with NOT NULL, and in this article I’ll walk you through how SQLite primary keys work, from INTEGER PRIMARY KEY to composite keys.

TLDR

A primary key is the column, or set of columns, whose values identify each row. A table can have only one.
id INTEGER PRIMARY KEY makes id an alias for the rowid. It fills itself in and never takes NULL.

Any other primary key column accepts NULL. Add NOT NULL to stop that.
For a composite key, put PRIMARY KEY (col1, col2) after the columns.
ALTER TABLE can’t add a primary key. Rebuild the table instead.

What is a primary key in SQLite?

A primary key is a constraint that makes each row’s key value, or combination of values, unique. SQLite’s docs say a table can have only one primary key, and that “each row in a table with a primary key must have a unique combination of values in its primary key columns” (SQLite CREATE TABLE).

SQLite adds one exception to the SQL standard. “Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or a STRICT table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column” (same page).

SQLite primary key syntax

Key	Syntax
Auto-numbered integer	`id INTEGER PRIMARY KEY`
Text or other single column	`code TEXT PRIMARY KEY NOT NULL`
Composite (multiple columns)	`PRIMARY KEY (student_id, course_id)` after the column list
Named constraint	`CONSTRAINT pk_users PRIMARY KEY (id)`

How to create a primary key in SQLite

To show you how each kind behaves, I will create a few small tables in one database and try to break their keys.

Step 1: Create a table with an INTEGER PRIMARY KEY

Leave id out of the INSERT, and SQLite numbers the rows:

sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
sqlite> INSERT INTO users (name) VALUES ('Ada'), ('Linus'); SELECT * FROM users;
id  name
--  -----
1   Ada
2   Linus

Step 2: Watch it reject a duplicate

sqlite> INSERT INTO users (id, name) VALUES (1, 'Grace');
Error: stepping, UNIQUE constraint failed: users.id (19)

Step 3: Check what INTEGER PRIMARY KEY really is

It’s another name for the table’s built-in rowid. Even SELECT rowid comes back labelled id:

sqlite> SELECT rowid, id, name FROM users;
id  id  name
--  --  -----
1   1   Ada
2   2   Linus

Because of that, inserting NULL doesn’t store NULL. SQLite assigns the next number:

sqlite> INSERT INTO users (id, name) VALUES (NULL, 'Grace'); SELECT * FROM users;
id  name
--  -----
1   Ada
2   Linus
3   Grace

Examples

These run in the same database, one after another.

Text (string) primary key

A TEXT PRIMARY KEY rejects duplicates:

sqlite> CREATE TABLE countries (code TEXT PRIMARY KEY, name TEXT NOT NULL);
sqlite> INSERT INTO countries VALUES ('IN', 'India'), ('FR', 'France');
sqlite> INSERT INTO countries VALUES ('IN', 'Indonesia');
Error: stepping, UNIQUE constraint failed: countries.code (19)

It does accept NULL, more than once, because NULLs count as distinct from each other:

sqlite> INSERT INTO countries VALUES (NULL, 'Nowhere'), (NULL, 'Elsewhere'); SELECT rowid, code, name FROM countries;
rowid  code  name
-----  ----  ---------
1      IN    India
2      FR    France
3            Nowhere
4            Elsewhere

Add NOT NULL to close that gap:

sqlite> CREATE TABLE currencies (code TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL);
sqlite> INSERT INTO currencies VALUES (NULL, 'Mystery');
Error: stepping, NOT NULL constraint failed: currencies.code (19)

Composite primary key (multiple columns)

A student can take many courses, but only enrol in each course once. The pair is the key:

sqlite> CREATE TABLE enrollments (student_id INTEGER NOT NULL, course_id INTEGER NOT NULL, enrolled_on TEXT NOT NULL, PRIMARY KEY (student_id, course_id));
sqlite> INSERT INTO enrollments VALUES (1, 101, '2026-09-01'), (1, 102, '2026-09-01'), (2, 101, '2026-09-02');
sqlite> INSERT INTO enrollments VALUES (1, 101, '2026-09-03');
Error: stepping, UNIQUE constraint failed: enrollments.student_id, enrollments.course_id (19)

pragma_table_info() shows each column’s position in the key (0 means it’s not part of it):

sqlite> SELECT name, type, pk FROM pragma_table_info('enrollments');
name         type     pk
-----------  -------  --
student_id   INTEGER  1
course_id    INTEGER  2
enrolled_on  TEXT     0

More than one primary key

A table can’t have two primary keys. Use one composite key, or UNIQUE for the other column:

sqlite> CREATE TABLE bad (a INTEGER PRIMARY KEY, b TEXT PRIMARY KEY);
Error: in prepare, table "bad" has more than one primary key

The index behind a primary key

An INTEGER PRIMARY KEY needs no index, because the table is already stored in rowid order. In an ordinary table, other primary keys get an automatic index named sqlite_autoindex_…:

sqlite> SELECT name, tbl_name FROM sqlite_schema WHERE type = 'index';
name                            tbl_name
------------------------------  -----------
sqlite_autoindex_countries_1    countries
sqlite_autoindex_currencies_1   currencies
sqlite_autoindex_enrollments_1  enrollments

INT PRIMARY KEY isn’t the same as INTEGER PRIMARY KEY

Only the exact type name INTEGER makes the rowid alias. With INT, it’s an ordinary column that SQLite doesn’t fill in, so it stays NULL:

sqlite> CREATE TABLE t_int (id INT PRIMARY KEY, v TEXT); INSERT INTO t_int (v) VALUES ('a'), ('b'); SELECT rowid, id, v FROM t_int;
rowid  id  v
-----  --  -
1          a
2          b

WITHOUT ROWID tables

In a WITHOUT ROWID table, the primary key is the storage key, and NULL is rejected:

sqlite> CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT) WITHOUT ROWID;
sqlite> INSERT INTO settings VALUES (NULL, 'x');
Error: stepping, NOT NULL constraint failed: settings.key (19)

Add a primary key to an existing table

ALTER TABLE can’t do it:

sqlite> CREATE TABLE contacts (email TEXT, name TEXT); INSERT INTO contacts VALUES ('ada@example.com', 'Ada'), ('linus@example.com', 'Linus');
sqlite> ALTER TABLE contacts ADD PRIMARY KEY (email);
Error: in prepare, near "PRIMARY": syntax error
  ALTER TABLE contacts ADD PRIMARY KEY (email);
             error here ---^

Rebuild the table instead: create a new one with the key, copy the rows, drop the old one and rename. This script does it in one transaction:

-- Rebuild a table to add a primary key (SQLite can't add one with ALTER TABLE).
PRAGMA foreign_keys = OFF;
BEGIN;
CREATE TABLE contacts_new (
  email TEXT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL
);
INSERT INTO contacts_new (email, name) SELECT email, name FROM contacts;
DROP TABLE contacts;
ALTER TABLE contacts_new RENAME TO contacts;
COMMIT;
PRAGMA foreign_keys = ON;

sqlite> .read add_pk.sql
sqlite> SELECT name, type, pk FROM pragma_table_info('contacts');
name   type  pk
-----  ----  --
email  TEXT  1
name   TEXT  0
sqlite> INSERT INTO contacts VALUES ('ada@example.com', 'Ada again');
Error: stepping, UNIQUE constraint failed: contacts.email (19)

The same rebuild also changes or drops a primary key. See ALTER TABLE ADD COLUMN for what ALTER TABLE can do.

Primary key vs UNIQUE

A table can have many UNIQUE constraints but only one primary key. UNIQUE columns allow NULLs, and two NULLs don’t clash:

sqlite> CREATE TABLE accounts (id INTEGER PRIMARY KEY, email TEXT UNIQUE, username TEXT UNIQUE);
sqlite> INSERT INTO accounts (email, username) VALUES (NULL, 'ada'), (NULL, 'linus'); SELECT * FROM accounts;
id  email  username
--  -----  --------
1          ada
2          linus

See SQLite UNIQUE constraint.

Edge cases

AUTOINCREMENT isn’t needed for auto-numbering: INTEGER PRIMARY KEY already does it. AUTOINCREMENT only stops IDs from deleted rows being reused. See SQLite AUTOINCREMENT.
STRICT tables reject NULL in primary key columns too (STRICT tables).

Conclusion

Use id INTEGER PRIMARY KEY for a plain numeric ID. For a text or composite key, add NOT NULL to every key column. To add a key to a table that already exists, rebuild the table.