How to Migrate Existing IDs to a UUID System

Pallav Kalal

Senior Full-Stack Engineer • Last updated: July 2026

Transitioning a production application from sequential auto-incrementing integers to Universally Unique Identifiers (UUIDs) is one of the most perilous architectural shifts an engineering team can undertake. The primary key is the structural spine of a relational database. Modifying it demands surgically altering every connected table, index, and query in the system without disrupting active user traffic. If you are researching how to migrate existing IDs to a UUID system, you must abandon any thought of a quick, single-step `ALTER TABLE` execution.

A successful transition requires a zero-downtime, multi-phase migration strategy. This involves establishing dual columns, executing background data backfills, deploying overlapping application logic, and carefully orchestrating the final schema switch. This comprehensive guide will walk you through the precise technical steps required to safely perform this operation on a live, highly trafficked database.

1. The Complexities of Primary Key Migrations

The fundamental difficulty in migrating primary keys stems from relational integrity. If your `users` table uses an integer ID, that integer is highly likely referenced as a foreign key in `orders`, `sessions`, `support_tickets`, and countless other tables. You cannot simply drop the integer and replace it with a UUID, because all dependent tables would instantly become orphaned, resulting in catastrophic data corruption.

Furthermore, locking a table to rewrite millions of primary keys blocks all incoming read and write transactions. For an application with any significant traffic, a synchronous lock of this magnitude will trigger massive connection timeouts and effectively take the system offline. Therefore, the migration must be executed asynchronously over days or weeks, ensuring that both the legacy integer system and the new UUID system remain completely synchronized until the final cutover. Before starting, ensure you understand the storage implications by reviewing our breakdown of UUID performance impact on large databases.

2. Dual-Column Schema Preparation

The first practical step is to modify the database schema without touching the existing primary keys. We utilize a "dual-column" strategy. For the target parent table (e.g., `users`), you must add a new, nullable column designed to hold the UUID. In PostgreSQL, this would be `uuid_id UUID DEFAULT uuid_generate_v4()`. In MySQL, it would be `uuid_id BINARY(16)`.

Crucially, you must also replicate this schema addition across every single child table that maintains a foreign key relationship to the parent table. For example, the `orders` table must receive a new `user_uuid` column. During this phase, you are simply adding empty, nullable columns. Because you are not dropping or rewriting existing data, these `ALTER TABLE` operations execute extremely quickly and do not require long-term exclusive locks.

3. Generating the New UUID Mapping

With the new columns in place, you must backfill the UUID data for all legacy rows. For the parent table, you execute an `UPDATE` query that assigns a freshly generated UUID to the new `uuid_id` column for every existing row. If you have a massive table (tens of millions of rows), you should execute this update in small, batched chunks using a background script to prevent transaction log bloat and replication lag.

Once the parent table is fully populated with UUIDs, you must backfill the child tables. This is achieved through a `JOIN` operation. You update the `user_uuid` column in the `orders` table by joining it to the `users` table via the legacy integer ID, copying the new UUID over to the child record. When this phase completes, every row in your database will possess both the legacy integer ID and the new UUID mapping.

Simultaneously, you must implement a mechanism to ensure any new data inserted during the migration window receives both identifiers. You can accomplish this either by utilizing database triggers that automatically populate the UUID on insert, or by deploying an intermediate application update that performs "dual-writes" for all new records. If you need to generate UUIDs manually to test your application logic, use our UUID Generator.

4. Updating Foreign Key Constraints

Once the backfill is verified and dual-writes are active, the schema must be hardened. The new UUID columns, which were initially created as nullable, should now be altered to `NOT NULL`. Next, you must construct the new structural relationships. Create a `UNIQUE` index on the parent table's new UUID column.

With the unique constraint in place, you can safely establish the new foreign key relationships between the child tables and the parent table using the UUID columns. At this exact moment, your database maintains two parallel, perfectly synchronized relational hierarchies: the legacy integer hierarchy and the modern UUID hierarchy.

5. The Application Code Rollout Phase

The database is now fully prepared; the bottleneck shifts to the application codebase. You must meticulously audit your application to identify every SQL query, ORM model, and API endpoint that references the legacy integer ID. If you are using an ORM like Hibernate or ActiveRecord, you must reconfigure the primary key mapping for the relevant models.

Deploy this code update carefully. The application should stop querying the legacy integer columns and begin executing all `SELECT`, `INSERT`, `UPDATE`, and `DELETE` operations utilizing the new UUID columns. Because you maintained dual-writes in the previous phase, the application will seamlessly retrieve the data. Monitor your application logs and APM dashboards rigorously during this rollout to identify any hardcoded integer queries that were missed during the audit. Reviewing the differences outlined in UUID vs integer primary keys can help you anticipate ORM configuration challenges.

6. Dropping Legacy Integer Columns

The final phase is the cleanup operation. Do not rush this step. Allow the application to run exclusively on the new UUID infrastructure for at least one full deployment cycle (e.g., a week or a sprint) to ensure total stability and to verify that no obscure background cron jobs or reporting scripts are still secretly relying on the legacy integers.

Once you possess absolute confidence, you can execute the final, non-reversible operations. Drop the legacy foreign key constraints. Drop the legacy integer primary key constraint. Promote the UUID column to be the official `PRIMARY KEY` of the table. Finally, drop the legacy integer columns entirely to reclaim the wasted storage space. The migration is complete.

7. Managing Foreign Key Constraints During Cutover

One of the most complex challenges you will face during a live migration is handling strict relational integrity, specifically Foreign Key (FK) constraints. In a highly normalized relational database, the primary key of your main table is referenced by foreign keys across dozens, sometimes hundreds, of child tables. If you attempt to arbitrarily drop the integer primary key on the parent table or swap its type to UUID without addressing the child tables first, the database engine will immediately block the transaction to prevent orphaned rows and structural corruption.

To safely navigate this, you must apply the exact same dual-column methodology to every single child table that references the migrating parent table. First, you add a nullable parent_uuid column to all dependent tables. During the data backfill phase, you must execute complex multi-table UPDATE statements utilizing JOIN operations to map the newly generated UUID in the parent table to the corresponding newly created UUID column in the child table. This process can be computationally heavy and should be executed in small, indexed batches to avoid stalling the database engine.

Once the application logic has been successfully updated to dual-write to both columns across the entire relational graph, you can begin the constraint modification phase. You must drop the existing integer-based foreign key constraints, establish new foreign key constraints linking the child UUID columns to the parent UUID column, and finally transition the application to read exclusively from the new relationships. Only after the application has run stably on the new UUID-based relational graph for several days should you execute the final destructive schema changes to drop the legacy integer columns entirely.

Because these cascading schema changes require intricate coordination, maintaining a strict migration script repository is critical. Employing robust database migration frameworks that allow for easy rollbacks and forward-only state progression will save your team from catastrophic deployment failures. For broader context on how modern systems approach these constraints, read our guide exploring why should you use UUID for API identifiers.

8. Frequently Asked Questions

Can I perform a UUID migration without downtime?

Yes, but it requires a multi-phase approach. You must implement a dual-column strategy where the old integer and new UUID coexist, synchronize them with database triggers, and gradually transition the application layer.

Should I generate UUIDs in the database or the application during migration?

During the backfill phase for legacy data, you should generate UUIDs directly in the database using built-in functions to maximize throughput. Future inserts should ideally be handled by the application.

What happens if I miss a foreign key relationship during migration?

If a foreign key relationship is missed, the child table will remain orphaned on the legacy integer ID. When you eventually drop the legacy integer column, those queries will fail, causing application downtime.

How long does a database UUID migration typically take?

While the physical execution of SQL queries can be fast, a safe migration strategy spanning dual-writes, application deployment, and cleanup usually takes a minimum of two weeks across multiple sprints.

9. Conclusion

Mastering how to migrate existing IDs to a UUID system is a defining exercise in defensive database engineering. By rejecting the temptation of a quick, locking alteration and instead embracing a methodical, dual-column progression, you eliminate the risk of downtime and data corruption. While the process demands rigorous synchronization between your database schema and your application code, the resulting architecture - immune to IDOR vulnerabilities, resistant to data scraping, and primed for distributed scalability - is worth the engineering investment.