Sqlite - Primary Key

Relational Data Modeling - Primary Key Constraint

In most cases, PRIMARY KEY constraints are implemented by creating a unique index in the database.

The exceptions are:

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

Hence, the following schemas are logically equivalent:

• B is a primary key

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

• B is Unique (and has no name)

CREATE TABLE t1(a, b UNIQUE);

• With a index name (CREATE primary key doesnot exist)

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

You can't modify the primary key via an alter statement, you need to:

• create a temporary table (with the structure that you want)
• insert all data in it
• delete the old table
• rename the temporary table to the new one

Example where a name was taken as identifier and that a ID will take over.

• Creation of the new table with the target schema (via the Create statement)

create table THINGS_TMP
(
    ID INTEGER PRIMARY KEY,
    NAME TEXT UNIQUE,
    DESCRIPTION TEXT
);

• Copy all data into the temporary table

insert into THINGS_TMP (NAME, DESCRIPTION) from THINGS;

• Rename the temporary table

drop table THINGS;
alter table THINGS_TMP rename to THINGS;