Sqlite - Primary Key

About

Management

Creation

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); 

Update

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.

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;

Powered by ComboStrap