Is is possible to use GUID as primary Keys in SQLITE Database?If Possible which datatype can be used?
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
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 byte
BLOBvalue). To insert, use:
INSERT INTO mytable (uuid) VALUES (X'01020304050607080910111213141516');
Read it as 16-byte
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.