SQLite Forum

NEXT VALUE FOR in SQLite
Login
> ~~~~
CREATE TABLE kv(name TEXT PRIMARY KEY, value INTEGER);
CREATE INDEX kv_value ON kv(value);
CREATE TRIGGER kv_autoinc AFTER INSERT ON kv
WHEN new.value IS NULL BEGIN
  UPDATE kv SET value=(SELECT coalesce(max(value),0)+1 FROM kv) 
   WHERE name=new.name;
END;
~~~~

Then when you insert a NULL

>     INSERT INTO kv(name) VALUES('first');

The trigger will automatically add in the next larger value, or 1 if the table
is initially empty.