Updated March 8, 2023
Definition of SQL UUID
UUID (Universally Unique Identifiers) is a very popular data type in most programming languages. As the name suggests, UUIDs are unique values practically in the entire universe. That is, they are unique not only to the database server but among different servers as well. UUID is a string of five hexadecimal digits consisting of 128 bits as defined in compliance with RFC 4122. The UUID value can be a combination of server time, clock sequence, MAC address of the main network etc., or it can be completely random or pseudo-random depending upon the UUID version. In this topic, we are going to learn about SQL UUID.
The most popular usage of the UUID data type is as primary keys. They are more secure than conventional auto-increment integer type primary keys as they are unique and difficult to guess and replicate. Also, they are globally unique and hence do not create problems when migrations are performed across databases. There will not be any clashes. However, UUIDs have certain disadvantages. For example, we can look at the order of insertion from auto-increment type primary keys, but it’s not possible with UUIDs. Also, UUIDs are 128 bits that take a lot more compared to conventional primary keys. Another disadvantage is debugging with UUIDs can be a problem because of their size and complexity.
In this post, we will be discussing everything about UUIDs, from how to create them to how to use them in the database tables. Let’s begin with understanding the syntax used for writing a UUID generate function.
Syntax and parameters of SQL UUID
A generic UUID value looks something as follows :
AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE
The basic syntax for writing a UUID function in SQL is as follows :
SELECT UUID_generate_version4();OR
SELECT UUID_generate_version1();The syntax of this function might differ from one database server to another, but the underlying principle remains the same. This particular syntax corresponds to postgreSQL. However, in other databases it might be UUID_GENERATE(), NEWID() or UUID().
The basic syntax for using a UUID Data type in a database table is as follows :
CREATE TABLE tablename (
Column_name1 UUID CONSTRAINT,
column_name2 data type CONSTRAINT,
.
.
.
);The parameters used in the above-mentioned syntax are similar to the CREATE TABLE statement. The only difference is the usage of UUID instead of any other data type.
Examples of SQL UUID
In order to use UUID values, we must load UUID generating functions such as uuid_generate_v4() in our database servers, as it doesn’t come loaded in database servers by default. Here is a CREATE EXTENSION statement to perform this task.
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";Example #1
SQL query to illustrate the generation of UUID value of version 1 type.
SELECT UUID_generate_v1();Output:
Example #2
SQL query to illustrate the generation of UUID value of version 4 type.
SELECT UUID_generate_v4();Output:
Example #3
SQL query to generate UUID without using a UUID generate function.
In the above-mentioned queries, we created a system-generated UUID. We can also create a custom UUID string by using a combination of clock_timestamp() and random() functions, as shown below.
SELECT uuid_in (md5(clock_timestamp()::text || random()::text)::cstring);Output:
Example #4
SQL queries to illustrate the usage of UUID values as primary keys?
Next, in order to illustrate some practical usage of the UUID data type, let us create a dummy table called “user_details”. This table contains a user id of UUID type in version 4. The CREATE TABLE statement of this table is as follows :
CREATE TABLE user_details (
user_id UUID DEFAULT uuid_generate_v4(),
user_name VARCHAR(225) NOT NULL,
city VARCHAR(50),
PRIMARY KEY (user_id)
);The user_details table has been successfully created. The UUID value is generated by default using the uuid_generate_v4() function every time a new record is inserted into the table.
Having created the table, let us insert a few records in it. We can use the following INSERT statement to insert records.
INSERT INTO user_details(
user_name, city)
VALUES ('Mohit Kumar','New Delhi'),
('Rahul Raj','Mumbai'),
('Samuel Maxson','New york'),
('Ariel Summer', 'Santa Monica'),
('Harmonie Grainger', 'London');5 Records have been successfully inserted. We do not need to insert any values for the user_id field as it will be generated by default, and the UUID_generate_v4() function will ensure that a unique value is inserted for each record.
Let’s check if the user_id column has been populated in the desired manner.
SELECT * FROM user_details;Output:
The results fetched using the SELECT statement shows that user_ids consisting of 5 hexadecimal values have been successfully generated. All of them are unique and, of course, difficult to memorise and replicate. Ergo, UUIDs are popularly used as primary keys.
Conclusion
In this post, we learned about the UUID data type in SQL. It is a unique set of 5 hexadecimal values. UUIDs are used for creating primary keys to uniquely identify a record. It is not only unique within the database server but is unique globally. Since it is either randomly or pseudo-randomly generated, it is hard to replicate. Hence, it provides much-needed security and uniqueness.
Recommended Articles
We hope that this EDUCBA information on “SQL UUID” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
