SQLite: primary key constraint

Creating primary keys

create table tq84_pk (
  col_1 text primary key,
  col_2 text
);

insert into tq84_pk values ('foo', 'bar');
insert into tq84_pk values ('baz',  null);

select
  rowid,
  col_1,
  col_2
from
  tq84_pk;

drop table tq84_pk;

create table tq84_table (
  num  integer,
  lang text   ,
  word text   ,
  --
  constraint tq84_table_pk primary key (num, lang)
);

insert into tq84_table values(1, 'en', 'one' );
insert into tq84_table values(1, 'de', 'eins');
insert into tq84_table values(2, 'en', 'two' );
insert into tq84_table values(2, 'fr', 'deux');

NULL values

create table T (
  p  text primary key,
  v  text
);

insert into T values ('one'  , 'first' );
insert into T values ('two'  , 'second');
insert into T values ('three', 'third' );
insert into T values ( null  , 'null'  );

.mode  column
.width 5 10

select * from T;
--
-- one    first
-- two    second
-- three  third
--        null

Autoincrementing the primary key

create table tq84_pk_autoincr (
  id     integer primary key, -- autoincrement,
  val_1,
  val_2
);

--
-- Explicitely setting the value of the primary key
-- to null causes the primary key (that is: the rowid)
-- to be auto-incremented.
--
insert into tq84_pk_autoincr values(null, 'foo', 'bar');
insert into tq84_pk_autoincr values(null, 'baz', 'qux');

--
-- Same thing if the primary key column is ommited in
-- the insert statement.
--
insert into tq84_pk_autoincr(val_1, val_2) values ('one'  , 'two' );
insert into tq84_pk_autoincr(val_1, val_2) values ('three', 'four');

--
-- If the primary key is set to a value, it resets the
-- auto increment value:
--
insert into tq84_pk_autoincr values (  41, 'hello' , 'world');
insert into tq84_pk_autoincr values (null, 'twenty', 'two');

.header on
.mode   column
.width 2 7 7
select * from tq84_pk_autoincr;
-- id  val_1    val_2  
-- --  -------  -------
-- 1   foo      bar    
-- 2   baz      qux    
-- 3   one      two    
-- 4   three    four   
-- 41  hello    world  
-- 42  twenty   two    

ON CONFLICT clauses

create table tab_on_conflict_abort    (id int primary key unique on conflict abort   , txt text);
create table tab_on_conflict_fail     (id int primary key unique on conflict fail    , txt text);
create table tab_on_conflict_ignore   (id int primary key unique on conflict ignore  , txt text);
create table tab_on_conflict_replace  (id int primary key unique on conflict replace , txt text);
create table tab_on_conflict_rollback (id int primary key unique on conflict rollback, txt text);

create table tab_stage (id int, txt text);

begin transaction;


insert into tab_stage values (5, 'five'        );
insert into tab_stage values (9, 'nine'        );
insert into tab_stage values (6, 'six'         );
insert into tab_stage values (4, 'four'        );
insert into tab_stage values (7, 'seven'       );
insert into tab_stage values (2, 'two'         );
insert into tab_stage values (6, 'SIX'         ) /* uh oh */;
insert into tab_stage values (1, 'one'         );
insert into tab_stage values (3, 'three'       );

commit;
begin transaction;

insert into tab_stage values (42, 'fourty-two' );
insert into tab_stage values (99, 'ninety-nine');



.print on conflict abort
.print -----------------
insert   into tab_on_conflict_abort     select * from tab_stage;
--
--       This insert statement signals an error. Because of the
--       on conflict abort clause in the primary key, no data
--       is loaded into the table at all.
--
select * from tab_on_conflict_abort;


.print on conflict fail
.print ----------------
insert   into tab_on_conflict_fail      select * from tab_stage;
--
--       This insert statement also signals an error. Because of 
--       the on conflict fail clause, the data is loaded up to (but
--       excluding) the record that caused the failure.
--
select * from tab_on_conflict_fail;
--
--  5|five
--  9|nine
--  6|six
--  4|four
--  7|seven
--  2|two


.print on conflict ignore
.print ------------------
insert   into tab_on_conflict_ignore    select * from tab_stage;
--
--       Just ignore records that would cause errors. So the
--       first record with id=6 is loaded, the second one is discared:
--
select * from tab_on_conflict_ignore;
-- 
-- 5|five
-- 9|nine
-- 6|six
-- 4|four
-- 7|seven
-- 2|two
-- 1|one
-- 3|three
-- 42|fourty-two
-- 99|ninety-nine


.print on conflict replace
.print -------------------
insert   into tab_on_conflict_replace   select * from tab_stage;
--
--       The on conflict replace clause also does not signal
--       any error. Unlike »on conflict ignore«, it overwrites
--       records with the new value, so id=6 will have txt=SIX.
--      
select * from tab_on_conflict_replace;
-- 
-- 5|five
-- 9|nine
-- 4|four
-- 7|seven
-- 2|two
-- 6|SIX
-- 1|one
-- 3|three
-- 42|fourty-two
-- 99|ninety-nine


insert   into tab_on_conflict_rollback  select * from tab_stage;
--
--       The  on conflict rollback clause signales an error AND
--       rolls back the current transaction if a duplicate
--       key is to be inserted. Hence, the inserted table will
--       remain empty.
--
select * from tab_on_conflict_rollback;

--
--       Since on conflict rollback caused a rollback,
--       the transaction was rolled back to the last commit.
--       So the records with id=42 and id=99 are not in
--       tab_stage anymore:
--
select * from tab_stage;

rowid / interger primary keys

Adding primary keys to an existing table

See also