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

All information on this site is shared with the intention to help. Before any source code or program is ran on a production (non-development) system it is suggested you test it and fully understand what it is doing not just what it appears it is doing. I accept no responsibility for any damage you may do with this code.