[SQLite] How to re-use the lowest "free" ID for INSERTS

[Zvoni]

Hi folks,
on a german forum there was a question how to automagically re-use the lowest "free" ID (ID being an INTEGER PRIMARY KEY) in SQLite.
"free" ID because that record was deleted.

Now, i'm aware that there will be a lot of people here claiming "why would you do/need that?"
And they would be right.

But: This is about the challenge it posed, and which i accepted.

Solution: We need a BEFORE INSERT Trigger.
Adjust Table, Column- and Triggername to suit

CREATE TRIGGER trg_tbl_test2_before_insert BEFORE INSERT ON tbl_test2
FOR EACH ROW
/* If no Value is passed for ID in an INSERT, SQLite assigns -1 to it */
/* We want to skip the Code if there is an explicit Value passed */
WHEN New.ID=-1
BEGIN  
    INSERT INTO tbl_test2
    WITH
        /* retrieve the next "theoretical" ID */
        MaxID AS (SELECT Max(ID)+1 As NewMax FROM tbl_test2),

        /* recursive CTE, which creates all ID's between 1 and MaxID.NewMax */
        CTE(ID) AS (SELECT 1 AS ID
                    UNION ALL
                    SELECT ID+1 FROM CTE INNER JOIN MaxID ON 1=1
                    WHERE ID+1<=MaxID.NewMax),

        /* Here we retrieve the lowest ID, which doesn't exist in the table. LEFT JOIN with check IS NULL in WHERE-Clause */
        CT AS (SELECT Min(CTE.ID) As NewID FROM CTE LEFT JOIN tbl_test2 AS T ON CTE.ID=T.ID  WHERE T.ID IS NULL)    
  
    /* Here we replace the value for New.ID */
    /* Important: All columns of the table (exception Column ID) have to be passed in Format "New.ColumnName" */
    /* irrespective of those columns being passed in the INSERT or not */
    SELECT NewID, New.SomeText FROM CT; 
 
    /* Stop execution of Trigger! IMPORTANT! */  
    SELECT RAISE(IGNORE);
END;

Insert a bunch of records, delete some records, and insert again (without passing an ID)

Have fun!