Regarding SQLite "next_val()", the following works with or without "NOT NULL":
CREATE TABLE t(rowid INTEGER PRIMARY KEY NOT NULL); INSERT INTO t VALUES (NULL),(NULL); SELECT * FROM t; --rowid --1 --2 DELETE FROM t WHERE rowid=1; INSERT INTO t VALUES (NULL); SELECT * FROM t; --rowid --2 --3 But these do not work at all: CREATE TABLE t(rowid INT PRIMARY KEY); INSERT INTO t VALUES (NULL),(NULL); SELECT * FROM t; --rowid -- -- CREATE TABLE t(rowid INT PRIMARY KEY NOT NULL); INSERT INTO t VALUES (NULL),(NULL); --Error: NOT NULL constraint failed: t.rowid CREATE TABLE t(rowid INTEGER PRIMARY KEY) WITHOUT ROWID; INSERT INTO t VALUES (NULL),(NULL); --Error: NOT NULL constraint failed: t.rowid Peter On Tue, Mar 20, 2018 at 9:44 AM, Chris Locke <sql...@chrisjlocke.co.uk> wrote: > > some people seem to think that an int primary key can be auto > incrementing, it can't > > But it works in the same way .... sort of. Its auto incrementing, with the > caveat that if the last row is deleted, the previous number will be used > again. Depending on the database schema, this may or may not cause issues. > > > Thanks, > Chris > > > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
