SQLite - How to add primary key in SQLite?

During table creation, to specify the primary key:

If there’s a single primary key:

CREATE TABLE table_name (
	col1 data_type1 PRIMARY KEY,
	col2 data_type2,
	col3 data_type3,
);

If you want to specify primary key on multiple columns:

CREATE TABLE table_name (
	col1 data_type1,
	col2 data_type2,
	col3 data_type3,
	PRIMARY KEY (col1, col2),
);

Add a primary key to an existing table

It’s currently not possible. SQLite has a very limited support for altering table. So if you want to perform a complex modification to an existing table, the common approach is to save existing data to a temporary table, drop the old table, create the new table, then copy the data back in from the temporary table.

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup (
	col1,
	col2
);
INSERT INTO t1_backup
SELECT
	col1,
	col2
FROM
	t1;
DROP TABLE t1;
CREATE TABLE t1 (
	col1,
	col2
);
INSERT INTO t1
SELECT
	col1,
	col2
FROM
	t1_backup;
DROP TABLE t1_backup;
COMMIT;

---

Need a good GUI tool for SQLite? Check out TablePlus. It’s native, beautiful, and available for free.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS.