0

For a sqlite table I want the primary key to be alphanumeric, not integer. If I have a 4-place NCHAR(4) column as the primary key, that means 36^4 or (10 numbers + 26 letters) ^ 4 => 1.6 million possible unique keys. Good enough! I've found the sqlite function combo

hex(randomblob(2))

and it will give me four random hex values, but I have no idea what my range is. I assume if it's hex I'm only getting 16 possible values per place, but is that 16 ^ 2 for 256 possible random values, or 16 ^ 4 since it's four places, or something entirely different? I'd really prefer to generate a four-place random alphanumeric key. Also, how do I get these values in the actual added records? With the simple integer key there's AUTOINCREMENT in the table create. I'm guessing it couldn't be done within CREATE TABLE.... Would this require a separate trigger, or would it be done in an INSERT statement at the time of record creation?

147pm
  • 2,137
  • 18
  • 28
  • Why would you want an alphanumeric key? A completely numeric key, in an appropriate base, is 'alpahnumeric'. Try base 36 or literally `base64`. – 9000 Mar 27 '14 at 01:48
  • I would like my primary key to be a fixed size, and each value random, not some auto-incremented integer. How would you even do a primary key column as an INT and auto-increment in a different base in sqlite? Since user-defined functions in sqlite sql aren't possible, I'm guessing I'll have to generate the value during INSERT, right? – 147pm Mar 27 '14 at 02:24
  • "insert into tbl1 (col1) values(hex(randomblob(3)));" does work and gives entries in col1 such as CD6633, 4D5D27, etc. Maybe this is all I can expect in this particular parallel universe. . . . – 147pm Mar 27 '14 at 04:33
  • `randomblob(3)` is sort of fine, but once in a while it will give you a number you already had. (I don't know why would you need to apply `hex` and thus waste half the bits in key field.) What would work is a block cipher applied to an autoincrement counter, some `aes256(counter xor 9084902834)`. But SQLite does not provide it; you have to calculate it in your app or [create an external function](http://stackoverflow.com/questions/3179021). – 9000 Mar 27 '14 at 04:49
  • SQLite does not have fixed-size column values. Why do you think you need them? – CL. Mar 27 '14 at 08:05
  • You might find this [answer](http://stackoverflow.com/a/18954383/2649012) useful. – Phantômaxx Mar 27 '14 at 10:15

0 Answers0