Skip to content

Instantly share code, notes, and snippets.

@texhex
Last active September 28, 2023 04:13
Show Gist options
  • Save texhex/84d103c782c56a815d5f to your computer and use it in GitHub Desktop.
Save texhex/84d103c782c56a815d5f to your computer and use it in GitHub Desktop.
/*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");
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment