SQL UUID/GUID Tutorial 🎯

Welcome to our deep dive into the world of SQL UUIDs (Universally Unique Identifiers) and GUIDs (Globally Unique Identifiers)! 📝

In this tutorial, we'll explore:

What are UUIDs and GUIDs?
Why use UUIDs and GUIDs?
How to generate UUIDs and GUIDs in SQL?
Practical examples using UUIDs and GUIDs 💡
[Quiz] Test your understanding ✅

What are UUIDs and GUIDs?

UUIDs and GUIDs are special types of identifiers used to uniquely identify records within a database. They are typically used when primary keys based on auto-incrementing integers are not suitable, or when multiple databases need to communicate with each other.

Both UUIDs and GUIDs are 128-bit values, represented as a string of 32 hexadecimal digits (0-9 and A-F), separated by hyphens. For example:

550e8400-e29b-11d4-a716-446655440000

Why use UUIDs and GUIDs?

Uniqueness: UUIDs and GUIDs ensure that each record has a unique identifier, reducing the risk of duplicate keys.
Portability: UUIDs and GUIDs are not tied to a specific database or system, making them ideal for data exchange between different systems.
Scalability: UUIDs and GUIDs provide a virtually endless supply of unique identifiers, making them suitable for large-scale applications.

How to generate UUIDs and GUIDs in SQL?

SQL does not have built-in support for generating UUIDs and GUIDs. However, most databases provide functions to generate them:

MySQL

To generate a UUID in MySQL, use the UUID() function:

sql

CREATE TABLE uuid_example (id UUID PRIMARY KEY);
INSERT INTO uuid_example (id) VALUES (UUID());

PostgreSQL

To generate a UUID in PostgreSQL, use the gen_random_uuid() function:

sql

CREATE TABLE uuid_example (id uuid PRIMARY KEY);
INSERT INTO uuid_example (id) VALUES (gen_random_uuid());

Practical examples using UUIDs and GUIDs 💡

In this section, we'll create a simple table with UUIDs and GUIDs, and then perform some common database operations.

Example - MySQL

sql

CREATE TABLE uuid_example (
  id UUID PRIMARY KEY,
  name VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO uuid_example (id, name) VALUES (UUID(), 'John Doe');

-- Query UUID records
SELECT * FROM uuid_example;

Example - PostgreSQL

sql

CREATE TABLE uuid_example (
  id uuid PRIMARY KEY,
  name VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO uuid_example (id, name) VALUES (gen_random_uuid(), 'John Doe');

-- Query UUID records
SELECT * FROM uuid_example;

[Quiz] Test your understanding ✅

With this, you've learned the basics of UUIDs and GUIDs in SQL! 🎉 Keep exploring and experimenting to master these powerful identifiers. Happy coding! 🤘