1. SQL Reference Guide
  2. Table Management
  3. Using the UUID data type

Using the UUID data type

Overview of the UUID data type

A universally unique identifier (UUID) is a 128-bit number used to identify information in computer systems. You can create a UUID and use it to uniquely identify something. In its canonical textual representation, the 16 octets of a UUID are represented as 32 hexadecimal (base-16) digits, displayed in five groups separated by hyphens, in the form 8-4-4-4-12 for a total of 36 characters (32 hexadecimal characters and 4 hyphens). For example, a81bc81b-dead-4e5d-abff-90865d1e13b1.

In Oracle NoSQL, UUID values are represented by the UUID data type. The UUID data type is considered a subtype of the STRING data type, because UUID values are displayed in their canonical textual format and, in general, behave the same as string values in the various SQL operators and expressions. However, in order to save disk space, the UUID value is saved in a compact format on disk. If the UUID value is the primary key, the canonical 36-byte string is converted to a 19-byte string, then is saved on disk. If the UUID value is a non-primary key, the canonical 36-byte string is converted to a 16-byte array, then is saved on disk.

A table column can be declared as having UUID type in a CREATE TABLE statement. The UUID data type is best-suited in situations where you need a globally unique identifier for the records in a table that span multiple regions since identity columns are only guaranteed to be unique within a NoSQL cluster in a region.

Using the UUID data type:

Declare a column with UUID data type. UUID is a subtype of the STRING data type. This UUID column can be defined as GENERATED BY DEFAULT. The system then automatically generates a value for the UUID column if you do not supply a value for it.

Syntax:

uuid_definition := AS UUID [GENERATED BY DEFAULT]

Semantics

Declares the type of a column to be the UUID type. If the GENERATED BY DEFAULT keywords are used, the system generates a value for the UUID column automatically, if the user does not supply one.

UUID Column Characteristics :

  • One table can have multiple columns defined as "STRING AS UUID". However, one table can have only one column defined as "STRING AS UUID GENERATED BY DEFAULT".
  • Since the Identity column is also generated by the system, the Identity column and the UUID GENERATED BY DEFAULT columns are mutually exclusive. That means only one IDENTITY column or one "UUID GENERATED BY DEFAULT" can exist per table.
  • You create a UUID column as part of a CREATE TABLE DDL statement or add a UUID column to an existing table with an ALTER TABLE DDL statement.
  • You can also index UUID columns via secondary indexes.

Example 1: UUID Column without GENERATED BY DEFAULT

CREATE TABLE myTable (id STRING AS UUID,name STRING, PRIMARY KEY (id));
Statement completed successfully

In the above example, the id column has no "GENERATED BY DEFAULT" defined, therefore, whenever you insert a new row, you need to explicitly specify a value for the id column.

INSERT INTO myTable 
        values("a81bc81b-dead-4e5d-abff-90865d1e13b1", "test1");
Statement completed successfully

Input format: The input string must conform to the format specified by RFC 4122. An IllegalArgumentException is thrown if the input string does not conform to the string representation as described at Class UUID.

Output format: The output is a UUID canonical format. This is 32 hexadecimal(base-16) digits, displayed in five groups separated by hyphens, in the form 8-4-4-4-12 for a total of 36 characters (32 hexadecimal characters and 4 hyphens).

The value for a UUID column can also be generated using the random_uuid function, which returns a randomly generated UUID, as a string of 36 characters. See Function to generate a UUID string.

Example 2: UUID Column using GENERATED BY DEFAULT

CREATE TABLE myTable (id STRING AS UUID GENERATED BY DEFAULT, name STRING, PRIMARY KEY (id));
Statement completed successfully

In the above example, the id column has "GENERATED BY DEFAULT" defined, therefore, whenever you insert a new row without specifying the value for the id column, Oracle NoSQL Database automatically generates a value for it.

INSERT INTO myTable VALUES(default,"test1");
Statement completed successfully

Table 5-1 Comparison between Identity Column and UUID column

Identity Column UUID column
Declare a column as Identity to have Oracle NoSQL Cluster automatically assign values to it Declare a column as UUID if you need unique values to be assigned to a NoSQL Cluster column in a multi-region system
An INTEGER, LONG, or NUMBER column in a table can be defined as an Identity column A UUID is a subtype of the STRING data type
An Identity column can be defined either as GENERATED ALWAYS or GENERATED BY DEFAULT A UUID column can be defined as GENERATED BY DEFAULT or you can supply the value of the string while inserting or updating data
Ideal in a single cluster architecture The UUID data type is best suited In situations where you need a globally unique identifier for the records in a table that span multiple regions since identity columns are only guaranteed to be unique within a NoSQL cluster in a region.

Costs less storage space than a corresponding UUID column.

If LONG is the primary key, it costs a maximum of 10 bytes. If LONG is a non-primary key, it costs a maximum of 8 bytes.

Costs more storage space than a corresponding Identity column.

If the UUID value is the primary key, it costs 19-bytes. If the UUID value is a non-primary key, it costs 16-bytes.