Can we use Guid as a Primary Key in Sqlite Database
Solution 1
SQLite itself does not support GUID
as internal type.
Except that, it does! (sort of). Remember, in SQLite any string can be used as type name, and that includes GUID
or UUID
(read more about SQLite datatypes).
According to those rules, GUID
type has affinity NONE
, which is the same as for BLOB
fields. With this in mind, you can create column of GUID
type, and use following rules to access it:
-
Store it as string like
X'01020304050607080910111213141516'
(X notation is used to represent 16 byteBLOB
value). To insert, use:INSERT INTO mytable (uuid) VALUES (X'01020304050607080910111213141516');
-
Read it as 16-byte
BLOB
.quote(uuid)
can be used to format output using X notation:SELECT quote(uuid) FROM mytable
Such column can be also used as primary key. Unfortunately, there is no AUTOINCREMENT functionality like it exists for integer primary keys - you will have to handle it yourself. You can use something as simple as randomblob(16)
for that, but it is not quite UUID
as defined by standard.
Confusingly, you can also store text representation of UUID in the same field (SQLite won't stop you from doing that), but it will take at least 2x more space: BLOB is 16 bytes, UUID as text is at least 32 bytes.
Solution 2
sqlite3
does not have a native UUID 128-bit format, per se.
However, GUIDs can be used as keys in SQLite as either a TEXT
or a binary BLOB
representation.
Based on the performance numbers posted in answer to a similar question, both binary and string UUIDs can be efficient in SQLite for Create and Query when indexed.
see table in: https://stackoverflow.com/a/11337522/3103448
SQLite can genarate either BLOB or TEXT 128-bit randoms numbers with randomblob(16)
and hex(X)
For example: lower(hex(randomblob(16)))
With similar index perfomance, a significant trade-off becomes whether a human readable string is preferred to the smaller binary data size.
Note: SQLite Release 3.31.0 on 2020-01-22 added the uuid.c extension module implementing functions for processing RFC-4122 UUIDs.
uuid() // generate a version 4 UUID as a string
uuid_str(X) // convert a UUID X into a well-formed UUID string
uuid_blob(X) // convert a UUID X into a 16-byte blob
Otherwise for RFC 4122 UUID (random) Type 4 compliance do the following:
- Generate 16 random bytes (=128 bits)
-
Adjust certain bits according to RFC 4122 section 4.4 as follows:
a. set the four most significant bits of the 7th byte to 0100'B, so the high nibble is "4" b. set the two most significant bits of the 9th byte to 10'B, so the high nibble will be one of "8", "9", "A", or "B"
Related videos on Youtube
user2134137
Updated on July 09, 2022Comments
-
user2134137 almost 2 years
Is is possible to use GUID as primary Keys in SQLITE Database?If Possible which datatype can be used?
-
user2134137 over 10 yearsOk.Thanks.ya that can be take care .since in my case GUIDS will come from server.Can you explain me the storing and retrieval part once again with example if you don't mind?
-
mvp over 10 yearsOk, added recipes for storing and retrieval.
-
user2134137 over 10 yearsThanks .i tried this and its working..but my requirement is i will get GUID from server and if i create an row on client(android) i need to send that row along with the UUID which i created on the client side and that will be inserted in Backend Server
-
mvp over 10 yearsIf you need locally generated GUID, use UUID class. When you have GUID (generated locally or received from server) in form
{04030201-0605-0807-0910-111213141516}
, you will need to format it asX'01020304050607080910111213141516'
before inserting to SQLite. -
savagepanda over 5 yearsMy own experiences show huge perf benefit from switching from string over to a BLOB representation.
-
StephenS about 4 yearsrandomblob(16) generates malformed UUID values. See RFC 4122 for how to generate them correctly, or just use the library functions in whatever language your app is written in.