Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can we use Guid as a Primary Key in Sqlite Database

Is is possible to use GUID as primary Keys in SQLITE Database?If Possible which datatype can be used?

like image 518
user2134137 Avatar asked Sep 23 '13 07:09

user2134137


People also ask

Can a GUID be a primary key?

GUIDs can be considered as global primary keys. Local primary keys are used to uniquely identify records within a table. On the other hand, GUIDs can be used to uniquely identify records across tables, databases, and servers.

Should I use int or GUID as primary key?

int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is unique. In which case using sql server guid would be better than and int and why? From what I've seen, int has no flaws except by the number limit, which in many cases are irrelevant.

Should I use GUID as id?

The guid can be used as needed to globally uniquely identify the row and id can be used for queries, sorting and human identification of the row. The id identifies the row in this table. The GUID (at least in theory) identifies this row anywhere in the known universe.

How do I create a primary key in SQLite?

Syntax. The syntax to add a primary key to a table in SQLite is: PRAGMA foreign_keys=off; BEGIN TRANSACTION; ALTER TABLE table_name RENAME TO old_table; CREATE TABLE table_name ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], ...


2 Answers

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 byte BLOB 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.

like image 189
mvp Avatar answered Sep 21 '22 13:09

mvp


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:

  1. Generate 16 random bytes (=128 bits)
  2. 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"

like image 35
l --marc l Avatar answered Sep 20 '22 13:09

l --marc l