SQLite, RowID, INDEX and AUTOINCREMENT

/*Only internal rowid*/
CREATE TABLE names_none (name TEXT);

INSERT INTO [names_none] VALUES ("JAMES");
INSERT INTO [names_none] VALUES ("DAVID");
INSERT INTO [names_none] VALUES ("ROBERT");
INSERT INTO [names_none] VALUES ("MICHAEL");
INSERT INTO [names_none] VALUES ("WILLIAM");

/* Normal primary key, this is an alias to rowid - see https://www.sqlite.org/lang_createtable.html#rowid */
CREATE TABLE names_primarykey (ID INTEGER PRIMARY KEY, name TEXT);

INSERT INTO [names_primarykey] VALUES (NULL, "JAMES");
INSERT INTO [names_primarykey] VALUES (NULL, "DAVID");
INSERT INTO [names_primarykey] VALUES (NULL, "ROBERT");
INSERT INTO [names_primarykey] VALUES (NULL, "MICHAEL");
INSERT INTO [names_primarykey] VALUES (NULL, "WILLIAM");


/* Primary key with AUTOINCREMENT, also an alias to rowid */
/* https://www.sqlite.org/autoinc.html */
CREATE TABLE names_autoincrement  (IDA INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);

INSERT INTO [names_autoincrement] VALUES (NULL, "JAMES");
INSERT INTO [names_autoincrement] VALUES (NULL, "DAVID");
INSERT INTO [names_autoincrement] VALUES (NULL, "ROBERT");
INSERT INTO [names_autoincrement] VALUES (NULL, "MICHAEL");
INSERT INTO [names_autoincrement] VALUES (NULL, "WILLIAM");

/* Display all tables */
SELECT ROWID as RowID, * FROM [names_none];
SELECT ROWID as RowID, * FROM [names_primarykey];
SELECT ROWID as RowID, * FROM [names_autoincrement];

/* All tables will have the same rowid or ID - James 1, DAVID 2, MICHAEL 3 */


/* Delete the first and the third entry from each table */
delete from [names_none] where name="JAMES" or name="ROBERT";
delete from [names_primarykey] where name="JAMES" or name="ROBERT";
delete from [names_autoincrement] where name="JAMES" or name="ROBERT";


/* All tables will STILL have the same rowid or ID/IDA - DAVID 2, MICHAEL 4 */


/* NOW COMPACT THE DATABSE -> VACUUM; */
/* Or in DB Browser for SQlite: File > Close Database, Open it again, File > Compact */

/* This will now show DAVID as RowID 1, MICHAEL as RowID 2 and WILLIAM as 3 ! */
/* That's because there is no primary key on these tables which tells SQLite it can reorganize RowID */
SELECT ROWID as RowID, * FROM [names_none];

/* No change here, DAVID is still RowID and ID 2 */
SELECT ROWID as RowID, * FROM [names_primarykey];

/* No change here either */
SELECT ROWID as RowID, * FROM [names_autoincrement];





/* Insert an entry into all tables with ID 9223372036854775807 which is the largest possible integer supported by SQLite */
INSERT INTO [names_none](ROWID, name) VALUES (9223372036854775807,"XENA");
INSERT INTO [names_primarykey] VALUES (9223372036854775807, "XENA");
INSERT INTO [names_autoincrement] VALUES (9223372036854775807, "XENA");



/* This command works, SQLite has choosen a random ID below 92233.... */
INSERT INTO [names_none] VALUES ("ZULU");

/* Here, SQLite uses the same procedure so this insert also works */
INSERT INTO [names_primarykey] VALUES (NULL, "ZULU");

/* But here, because of the AUTOINCREMENT keyword for the index, the command fails */
/* Error: database or disk is full: INSERT INTO [names_autoincrement] VALUES (NULL, "ZULU"); */
/* That's because AUTOINCREMENT for SQLite means "monotonically increasing" so it will not search for "free" IDs below 92233...*/
/* Full details: https://www.sqlite.org/autoinc.html */
INSERT INTO [names_autoincrement] VALUES (NULL, "ZULU");