How to create or add a Primary Key to a Sqlite Table

About

This article shows you how to add, create or modify a SQL primary key constraint

How to

How to create a table with a single-column primary key?

The following schemas are logically equivalent because the primary keys are implemented by creating a unique index in the database.

• The column B define the primary key

CREATE TABLE t1(a, b PRIMARY KEY); -- no name)
CREATE TABLE t1 (  a, b  CONSTRAINT t1b PRIMARY KEY) ;

• The column B is Unique (and has no name)

CREATE TABLE t1(a, b UNIQUE);

• With an index name (CREATE primary key does not exist)

CREATE TABLE t1(a, b);
CREATE UNIQUE INDEX t1b ON t1(b); 

How to create a table with a multiple-column primary key?

Multiple columns are also known as composite

CREATE TABLE EVENTS_QUEUE
(
    NAME      TEXT,     -- Name
    DATA_HASH TEXT,     -- Data Hash
    DATA      TEXT,     -- DataS
    constraint pk_events_queue primary key (NAME, DATA_HASH)
);

How to update/modify the definition of a primary key?

The alter statement is limited within SQLite, you need to create a new table and move the data.

See How to alter an SQLite table?

Tips

How is a primary key implemented?

PRIMARY KEY constraints are implemented by creating a unique index in the database.

The exceptions in Sqlite are:

• INTEGER PRIMARY KEY
• and PRIMARY KEYs on WITHOUT ROWID tables.