Is there UID datatype in SQLITE if Yes then how to generate value for that

sqlite uid
Pranita Patil picture Pranita Patil · Apr 11, 2012 · Viewed 35.3k times · Source

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?

Answer

Mike Sherrill 'Cat Recall' picture Mike Sherrill 'Cat Recall' · Apr 11, 2012

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.

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

Read More
How to get a list of column names on Sqlite3 database?

I want to migrate my iPhone app to a new database version. Since I don't have some version saved, I need to check if certain column names exist. This Stackoverflow entry suggests doing the select SELECT sql FROM sqlite_master …

Read More