0

I've got a situation where I need to generate a unique id for use across multiple tables, something like tables A, B, C each having a uniqueId field. For business reasons, the id's have to be unique across all tables, but because of the multiple tables, I can't use an auto-increment column.

Is there a way to make a sequence that can be shared like this?

(I know I could just make the uniqueId column TEXT and store a GUID in there, and that would be unique, but I may have a LOT of these, and I'd rather use a 4 byte integer than a 32 byte GUID for this. Is there a more compact way to generate a non-conflicting identifier in sqlite?)

Joshua Frank
  • 13,120
  • 11
  • 46
  • 95
  • 1
    What about using an autoincrement column in each table, and then creating the id by autoinc * 3 in A, autoinc * 3 + 1 in B, and autoinc * 3 + 2 in C? – choroba Sep 23 '21 at 20:43
  • That is very clever, but I was using the three tables as a simple way to explain what I'm trying to do. My actual use case is a bit more complex and I don't think this would work. I'm actually trying to use only one table, but mark certain rows with an id to indicate that they are grouped with each other, and the group id must be unique. I could make a Group table and use IT'S id, I guess, but that seems cumbersome to me. – Joshua Frank Sep 23 '21 at 21:00
  • You should edit your question and explain what you want clearly. The requirement described in your question has nothing to do with what you wrote in the comment. – forpas Sep 24 '21 at 15:37
  • @forpas: How so? I really do want a sequence that isn't associated with an auto-increment column. My example was for illustration, and I clarified with a better description of my scenario, but the desired feature is the same. – Joshua Frank Sep 24 '21 at 16:45
  • From the question: "...tables A, B, C each having a uniqueId field. For business reasons, the id's have to be unique across all tables" and from the comment: "I'm actually trying to use only one table, but mark certain rows with an id...". Where is the similarity? – forpas Sep 24 '21 at 17:00
  • I'm just giving a simple example of why you might need that, and in the comment I gave another one, which is actually a bit closer to my actual scenario. Both could be solved with a sequence that wasn't associated with a specific column. I can update the question, I suppose. – Joshua Frank Sep 24 '21 at 17:09

2 Answers2

1

Traditionally you'd use a sequence; just an auto-incrementing counter. Unfortunately, SQLite doesn't support sequences.

Use a Universally Unique Identifier, a UUID. UUIDv4 is just a 128 bit random number. Generate it in your program and insert it; preferably insert it as a 128 bit value, not a as string.

Schwern
  • 153,029
  • 25
  • 195
  • 336
  • 16 bytes is better than 32, so that's a definite improvement. But it's still a lot of size. Is there an algorithm for a smaller Not-So-Universally-Unique id that would still be good enough for collision avoidance on a single machine? – Joshua Frank Sep 23 '21 at 21:02
  • @JoshuaFrank: One of the products I maintain uses 64-bit "locally" unique identifiers: 38 bits of timestamp (in 1/64 seconds since the Unix epoch), 16 bits for a thread ID, and 10 bits for a counter. You can adjust the bit counts as needed for your purposes. – dan04 Sep 23 '21 at 21:30
  • @JoshuaFrank 1 million UUIDs costs you 16 megabytes of disk. You could try and skimp, but a UUID is simple, cheap, and effective. 4 vs 8 vs 16 bytes for an ID is not where your storage costs will come from. Unless your circumstances severely restrict your disk space while also needing to store millions of records? – Schwern Sep 23 '21 at 22:07
  • @JoshuaFrank You can [fake a sequence](https://stackoverflow.com/questions/4484984/how-can-i-use-sequences-in-sqlite), but it's more work (and time and money and potential for errors) to save what is normally an inconsequential amount of disk space. – Schwern Sep 23 '21 at 22:10
0

Create another table with just an autoinc column (and maybe one other column, if SQLite won't let you have just one?), and triggers for inserts on the other tables that:

  1. First inserts a row in this "fake-sequence" table

  2. Then fetches the last inserted row's id from that table

  3. And finally inserts that "fake-sequence-table"-generated value into the global-id columns of the other tables.

Should work -- if SQLite has triggers.

CRConrad
  • 11
  • 3