45

I am creating table like this:

CREATE TABLE foobar (id uniqueidentifier, foo text, bar text, PRIMARY  KEY (id))

How to insert or generate value for id field in table foobar?

tshepang
  • 12,111
  • 21
  • 91
  • 136
Pranita Patil
  • 791
  • 1
  • 9
  • 16

5 Answers5

72

You can argue that SQLite doesn't support data types at all. In SQLite3, you can do this, for example.

sqlite> create table test (id wibblewibble primary key);

SQLite will happily create a column with the "data type" wibblewibble. SQLite will also happily create columns with the "data types" uuid, guid, and SuperChicken.

The crucial point for you is probably how to automatically generate a uid. SQLite can't help you much there.

You can leave it entirely up to the client program. If you're programming in python, use the uuid module. In ruby, you have the SecureRandom.uuid function. Other languages have similar features or workarounds.

You can write your own uid-generating function in C. (See Create or Redefine SQL Functions.) I'd call this a relatively extreme approach.

You can store it in either binary or text format.

Other conversations online suggest that there's a widespread misunderstanding about what a UUID is. A UUID is not simply a 128-bit random number. A UUID has structure and rules. See RFC 4122.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • 3
    thanks for your help .I generated UUID for android application like this String sUUiDValue=UUID.randomUUID().toString(); and then inserted into table... – Pranita Patil Apr 19 '12 at 10:06
25

Benjamin Berry's answer isn't right — it produces malformed UUIDs — but it shows an interesting technique using a subselect to generate randomness then selecting substrings from that. Here's something similar that I've confirmed does work:

select substr(u,1,8)||'-'||substr(u,9,4)||'-4'||substr(u,13,3)||
  '-'||v||substr(u,17,3)||'-'||substr(u,21,12) from (
    select lower(hex(randomblob(16))) as u, substr('89ab',abs(random()) % 4 + 1, 1) as v);

Some sample output:

c71122df-18e4-4a78-a446-fbf7b8f2969b
61e75f87-978b-4d9e-b587-bedcc2d23898
30eee0fa-2ff2-4ff5-b8ef-f99378272999
Tim Ruddick
  • 1,375
  • 16
  • 24
  • 2
    For me it created 20 exact same uuids for my migration copy from one table to another (where I changed _id from int to this uuid) – miroslavign Oct 04 '16 at 08:21
  • 2
    Same as @miroslavign, if used within one request to update all the table records for example, generates the exacts same uuid foreach record. – fharreau Apr 25 '17 at 12:57
  • 1
    If you want to create random uuid for each line, I made a small mix.String u = "lower(hex(randomblob(16)))"; String v = "substr('89ab',abs(random()) % 4 + 1, 1)"; rawquery = "update table set guid = substr("+u+",1,8)||'-'||substr("+u+",9,4)||'-4'||substr("+u+",13,3)||'-'||"+v+"||substr("+u+",17,3)||'-'||substr("+u+",21,12)"); – P. Sohm Feb 06 '19 at 09:00
  • That's because random for computers are not really random, if the insert is happening too fast it's using the same random seed to get the "next" random number which means it will be the same number. I suspect (but am not sure) that the random seed is milliseconds, if you can pause between inserts for a millisecond it will produce more "random" type results. – LordWabbit May 31 '20 at 00:10
  • 2
    This has nothing to do with time, seeds, or milliseconds (pseudo-random generators are not seeded on every call), the expression is simply evaluated a single time by SQLite. Nowhere in the query do you request *multiple* values, you just say to use it multiple times, so SQLite uses one value that satisfies your request. Remember, SQL is not imperative, so an update/select is not necessarily a loop. – remram Sep 13 '20 at 02:38
  • 1
    There is one serious flaw in the example. When included in a "set var=formula" in an update or multirow insert the optimization computes the formula once as an optimization. Therefore all the rows get the same value and that might not be the desired effect. I added a "random()*rowid" and now each row is computed separately. There is still a potential for failure of duplicate values even on a 16 digit random number. – Richard Sep 16 '22 at 13:58
  • @LordWabbit, true that "random is not really random", but anyone with the remotest bit of clue stirs the entropy pool a little between pulling hashes out; even if that stirring is completely mechanical (add one!), the hash algorithm makes the result _look_ completely different. You still don't have true randomness unless there's new entropy coming in, and it'll eventually loop back on itself, but even so the output looks good enough to fool humans -- what's shown here is _not_ just an example of PRNG behavior. – Charles Duffy Apr 09 '23 at 16:49
23

Here is something similar which can be used directly as a expression: 

lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))

for example to be passed as default value for column:

sqlite> create table "table" (
  "id" char(36) default (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))), 
  "data" varchar(255), primary key ("id")
);

sqlite> insert into "table" ("data") values ('foo');
sqlite> insert into "table" ("data") values ('bar');
sqlite> select * from "table";
947efcc9-4212-442a-b68c-eb6fbd8a7128|foo
a2c3857b-1eb4-40bd-aed2-6e8d68cc2ab8|bar
Mikael Lepistö
  • 18,909
  • 3
  • 68
  • 70
  • 2
    what's going on here: substr('89ab',abs(random()) % 4 + 1, 1) – scape Apr 05 '18 at 17:07
  • 5
    It selects that next char to put is either '8', '9', 'a', or 'b'. It comes from UUID specs and tells that this is UUID variant 1 https://en.wikipedia.org/wiki/Universally_unique_identifier#Variants – Mikael Lepistö Apr 06 '18 at 08:10
1

needed this for a project

select SUBSTR(UUID, 0, 8)||'-'||SUBSTR(UUID,8,4)||'-'||SUBSTR(UUID,12,4)||'-'||SUBSTR(UUID,16)
from (
select lower(hex(randomblob(16))) AS UUID 
);
Benjamin Berry
  • 95
  • 2
  • 9
    Unfortunately, this produces malformed UUIDs, for example: 284e630-2293-bea6-21b1095ac11fe4f04. It should have 8 chars in the first group, a third group of 4, and 12 chars in the last. Also, the UUID version bits should be 4, not random. – Tim Ruddick Mar 29 '14 at 01:09
0

I created a user-defined function (to generate the default value for a guid/uuid column in my c# application)

var connection = new SqliteConnection("DataSource=:memory:");
connection.CreateFunction("newid", Guid.NewGuid);
Anders
  • 177
  • 2
  • 7