SQLite – Auto-Increment / Auto Generate GUID

Recently was asked if it’s possible to create an auto-incrementing GUID in SQLite.

Here is one approach:

In Firefox there is an add-on called SQLite Manager (also available at https://code.google.com/p/sqlite-manager/)

1. Open Firefox, in the “Tools” menu select “SQLite Manager”
2. With SQLite Manager open click on the “Database” menu and select “New Database”
3. Type in the database name and click the “OK” button
4. Select the folder you want the database to go in and then click the “Select Folder” button
5. Click on the “Execute SQL” tab
6. Paste the code below in the “Enter SQL” window and click on the “Run SQL” button.

   CREATE TABLE tblUsers (
   
   	UserAccountID CHAR(36) PRIMARY KEY,
   	firstname VARCHAR(100),
   	lastname VARCHAR(100)
   	);
   

7. Clear the “Enter SQL” window and then paste the code below and click on the “Run SQL” button.

   CREATE TRIGGER AutoGenerateGUID
   AFTER INSERT ON tblUsers
   FOR EACH ROW
   WHEN (NEW.UserAccountID IS NULL)
   BEGIN
      UPDATE tblUsers SET UserAccountID = (select hex( randomblob(4)) || '-' || hex( randomblob(2))
                || '-' || '4' || substr( hex( randomblob(2)), 2) || '-'
                || substr('AB89', 1 + (abs(random()) % 4) , 1)  ||
                substr(hex(randomblob(2)), 2) || '-' || hex(randomblob(6)) ) WHERE rowid = NEW.rowid;
   END;
   

8. Clear the “Enter SQL” window and then paste the code below and click on the “Run SQL” button. Now when you do an insert like below it will generate a GUID automatically

   INSERT INTO tblUsers (
   	UserAccountID,
   	firstname,
   	lastname
   	)
   VALUES (
   	NULL,
   	"David",
   	"Kittell"
   	)
   
   

Resulting INSERT looks like this:
UserAccountID: E2D37368-7E05-40DC-9BA3-04B2156CA598
firstname: David
lastname: Kittell