Sqlite - Primary Key
Table of Contents
About
Articles Related
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.
- 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;