How to Link Entities Using UUIDs in Your Application

Moving away from traditional auto-incrementing integers and adopting Universally Unique Identifiers (UUIDs) is a major architectural milestone for any growing application. However, many developers struggle with the next logical step: once you have a UUID primary key, exactly how to link entities using UUIDs across your relational database?

In this engineering guide, we will explore the precise mechanics of establishing foreign key relationships using 128-bit identifiers. We will cover the specific syntax for PostgreSQL and MySQL, analyze the performance implications of joining on large strings versus native UUID types, and establish indexing strategies that keep your queries lightning fast.

1. Understanding UUID Relational Mechanics

In a traditional relational database, linking entities is straightforward. You have a users table with an id column of type INTEGER. When you create an orders table, you simply add a user_id column of type INTEGER and draw a foreign key constraint between them.

When you transition to UUIDs, the fundamental relational mechanics do not change. A foreign key must still exactly match the data type of the primary key it references. The challenge arises because different database engines handle the UUID data type entirely differently.

If you are still debating whether this transition is necessary for your architecture, I highly recommend reviewing our deep dive on UUID vs Integer Primary Keys to ensure you actually need distributed identifiers before you rewrite your schema.

2. PostgreSQL: Native UUID Foreign Keys

PostgreSQL is widely considered the gold standard for UUID handling because it provides a native 128-bit uuid data type. It stores the identifier efficiently in binary format, rather than wasting space as a 36-character text string.

Here is how you link a users entity to an orders entity using UUIDs in PostgreSQL:

-- 1. Enable the pgcrypto extension (if using UUIDv4 generation)
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- 2. Create the parent entity
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 3. Create the child entity with a UUID foreign key
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    
    -- Establish the relational link
    CONSTRAINT fk_user
      FOREIGN KEY(user_id) 
      REFERENCES users(id)
      ON DELETE CASCADE
);

Because both users.id and orders.user_id are defined as the native UUID type, PostgreSQL will seamlessly enforce referential integrity and perform joins efficiently at the binary level.

3. MySQL: Handling UUIDs as Binary Strings

MySQL (prior to version 8.0's specialized functions) did not have a dedicated UUID column type. Developers often made the critical mistake of storing UUIDs as VARCHAR(36). Joining two tables on a 36-character string is devastating for performance, as it requires comparing heavy character sets and inflates index sizes massively.

To correctly link entities in MySQL, you must store the UUID as a BINARY(16) field. This reduces the storage footprint from 36 bytes down to 16 bytes and makes join operations vastly faster.

-- 1. Create the parent entity (MySQL 8.0+)
CREATE TABLE users (
    id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
    email VARCHAR(255) UNIQUE NOT NULL
);

-- 2. Create the child entity
CREATE TABLE orders (
    id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
    user_id BINARY(16) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    
    CONSTRAINT fk_user
      FOREIGN KEY (user_id) 
      REFERENCES users(id)
      ON DELETE CASCADE
);

When you query this data, you must use BIN_TO_UUID(user_id) to format it back into a readable string for your application layer.

4. Indexing Strategies for UUID Joins

Creating the foreign key constraint is only half the battle. If you do not index your foreign keys, every time you query "fetch all orders for this user", the database will perform a full table scan on the orders table.

By default, PostgreSQL and MySQL automatically create an index on the Primary Key. However, they do not automatically create indexes on Foreign Keys. You must do this manually:

-- Manually index the UUID foreign key for fast JOINs
CREATE INDEX idx_orders_user_id ON orders(user_id);

When dealing with millions of rows, the random nature of standard UUIDv4 can cause severe B-Tree index fragmentation. If you are experiencing slow inserts alongside slow joins, you should seriously consider migrating to the time-ordered UUIDv7 standard. We cover this extensively in our guide on How to Choose the Right UUID Type.

5. ORMs and UUID Relationships

If you are using an Object-Relational Mapper (ORM) like Prisma, TypeORM, or Sequelize in Node.js, the ORM abstracts away the complex SQL syntax, but you still must configure the entity relationships correctly.

For example, in Prisma (which natively supports UUIDs), linking entities looks like this:

model User {
  id      String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  email   String   @unique
  orders  Order[]  // Relation field
}

model Order {
  id      String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  amount  Decimal
  
  // Link the entity
  userId  String   @db.Uuid
  user    User     @relation(fields: [userId], references: [id])
}

Notice how Prisma handles the JavaScript layer as standard String types, but we explicitly tell the database adapter to map it to @db.Uuid. This ensures we don't accidentally fall back to string-based joins. If your Node.js application is struggling to generate these IDs quickly under load, be sure to read our troubleshooting article: Why Is My UUID Generation Slow?

6. Frequently Asked Questions

7. Conclusion

Linking entities using UUIDs is structurally identical to linking them with integers; the only operational difference lies in the underlying data types and indexing overhead. By ensuring you use native 128-bit types in PostgreSQL, or BINARY(16) in MySQL, you can maintain robust referential integrity without sacrificing query performance.

Remember that foreign keys are not automatically indexed by your database engine. Always apply explicit indexes to your UUID relational columns to ensure your application can traverse entities swiftly as your data grows.