Sqlite - Primary Key

1 - About

Relational Data Modeling - Primary Key Constraint

3 - Management

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

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


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap