In SQL, the data type used to store a universally unique identifier (UUID) is UUID. A UUID is a 128-bit number that is generated in such a way that it is guaranteed to be unique across all devices and time. It is commonly used as a primary key in database tables to uniquely identify records.
UUID Data Type
The UUID data type was introduced in SQL:2008 as part of the standard, and it has since been supported by many database management systems, including PostgreSQL, MySQL, Oracle, and Microsoft SQL Server.
To define a column with the UUID data type in SQL, you can use the following syntax:
CREATE TABLE table_name ( id UUID PRIMARY KEY, column1 datatype, column2 datatype, .. );
In this example, the id column is defined as a primary key of type UUID. You can replace table_name, column1, column2, and so on with your own table and column names.
Generating UUID Values
To generate UUID values for inserting into the table, you can use various methods depending on your database system.
- PostgreSQL: In PostgreSQL, you can use the uuid_generate_v4() function to generate version 4 UUIDs. For example:
INSERT INTO table_name (id, column1) VALUES (uuid_generate_v4(), 'value1');
INSERT INTO table_name (id, column1) VALUES (UUID(), 'value1');
INSERT INTO table_name (id, column1) VALUES (sys_guid(), 'value1');
INSERT INTO table_name (id, column1) VALUES (NEWID(), 'value1');
Working with UUID Values
Once you have inserted UUID values into the table, you can perform various operations on them just like any other data type.
For example, to retrieve records with a specific UUID value, you can use the WHERE clause in your SQL query:
SELECT * FROM table_name WHERE id = 'your_uuid_value';
You can also use the UUID data type in joins and other operations that involve multiple tables.
The UUID data type in SQL provides a reliable and standardized way to store universally unique identifiers. It allows for efficient and accurate identification of records in database tables. By using proper syntax and functions specific to your database system, you can easily generate and work with UUID values.
If you’re working with a different database system than those mentioned here, consult the documentation for that system to determine the appropriate way to work with UUIDs.