SQLITE fill value with unique random table

Question

I want to create a table with a field that is unique and limited to a certain value. Lets say that the limit is 100, the table is full, I remove a random row, and when I create a new row it has the value that was freed before. It doesn't need to be the fastest thing in the world (the limit is quite small), I just want to implement it in a DB. Any ideas?

Answer 1

This solution does everything in a trigger, so you can just use a normal INSERT.

For the table itself, we use an autoincrementing ID column:

CREATE TABLE MyTable(ID INTEGER PRIMARY KEY, Name);

We need another table to store an ID temporarily:

CREATE TABLE moriturus(ID INTEGER PRIMARY KEY);

And the trigger:

CREATE TRIGGER MyTable_DeleteAndReorder
AFTER INSERT ON MyTable
FOR EACH ROW
WHEN (SELECT COUNT(*) FROM MyTable) > 100
BEGIN
    -- first, select a random record to be deleted, and save its ID
    DELETE FROM moriturus;
    INSERT INTO moriturus
      SELECT ID FROM MyTable
      WHERE ID <> NEW.ID
      ORDER BY random()
      LIMIT 1;

    -- then actually delete it
    DELETE FROM MyTable
      WHERE ID = (SELECT ID
                  FROM moriturus);

    -- then change the just inserted record to have that ID
    UPDATE MyTable
      SET ID = (SELECT ID
                FROM moriturus)
      WHERE ID = NEW.ID;
END;