SQLite3

Looking for elegant approaches for hooking up related tables

• http://stackoverflow.com/questions/13244393/sqlite-insert-or-ignore-and-return-original-rowid

insert or replace

sqlite> CREATE TABLE colors (label text primary key);
sqlite> CREATE UNIQUE INDEX idx_something ON colors (label) ;
sqlite> insert or replace into colors values ('red') ;
sqlite> insert or replace into colors values ('green') ;
sqlite> insert or replace into colors values ('blue') ;
sqlite> select rowid, * from colors ;
rowid       label
----------  ----------
1           red
2           green
3           blue
sqlite> insert or replace into colors values ('red') ;
sqlite> select rowid, * from colors ;
rowid       label
----------  ----------
2           green
3           blue
4           red
sqlite>

insert or replace Keeps incrementing

sqlite> create table B (id integer primary key autoincrement, label text, constraint label_unique unique (label) );
sqlite> insert or replace into B values (NULL,"red");
sqlite> insert or replace into B values (NULL,"red");
sqlite> select * from B ;
id          label
----------  ----------
2           red

sqlite> create table C (id integer primary key, label text, constraint label_unique unique (label) );
sqlite> insert or replace into C values (NULL,"red");
sqlite> insert or replace into C values (NULL,"red");
sqlite> select * from C ;
id          label
----------  ----------
2           red

sqlite> create table D (label text primary key) ;
sqlite> insert or replace into D values ("red");
sqlite> insert or replace into D values ("red");
sqlite> select rowid,* from D ;
rowid       label
----------  ----------
2           red

On conflict replace

• http://www.sqlite.org/lang_conflict.html

sqlite> create table E (label text primary key on conflict replace) ;
sqlite> insert into E values ("red") ;
sqlite> insert into E values ("red") ;
sqlite> select rowid, * from E ;
rowid       label
----------  ----------
2           red

On conflict ignore on the column definition

Succeeds to not increment when pre-existing, but last_insert_rowid doesnt provide the key to the row that you didnt just add:

sqlite> create table F (label text primary key on conflict ignore) ;
sqlite> insert into F values ("red") ;
sqlite> insert into F values ("red") ;
sqlite> insert into F values ("green") ;
sqlite> insert into F values ("blue") ;
sqlite> insert into F values ("red") ;
sqlite> select rowid, * from F ;
rowid       label
----------  ----------
1           red
2           green
3           blue
sqlite>
sqlite> select last_insert_rowid() ;
last_insert_rowid()
-------------------
3

• http://www.sqlite.org/c3ref/last_insert_rowid.html

insert or ignore on the insert

Same effect with insert or ignore

sqlite> create table G (label text primary key) ;
sqlite> insert or ignore into G values ("red") ;
sqlite> insert or ignore into G values ("red") ;
sqlite> insert or ignore into G values ("green") ;
sqlite> insert or ignore into G values ("green") ;
sqlite> insert or ignore into G values ("green") ;
sqlite> insert or ignore into G values ("green") ;
sqlite> insert or ignore into G values ("green") ;
sqlite> insert or ignore into G values ("blue") ;
sqlite> insert or ignore into G values ("red") ;
sqlite> select rowid, * from G ;
rowid       label
----------  ----------
1           red
2           green
3           blue
sqlite> select last_insert_rowid() ;
last_insert_rowid()
-------------------
3