Sequences Archives

Replace Duplicates in ID Column with Unique Values

Replace Duplicate Ids with Unique Values

A common scenario: in the development environment, a table’s ID column is missing its unique constraint, and developers have been creating their own data for testing. Suddenly someone realizes that the ID column has duplicates. The DBA is summoned. "Enable the unique key", the DBA is told, "but do not delete our data."

Can the DBA escape this catch-22? For sure, says this post — with an approach of replacing *only* the duplicate IDs with unique values, and letting everything else stay untouched.

Oracle’s MERGE statement is tailor-made for situations when you want to do an "upsert" i.e. update existing rows in a table or insert new rows depending on a match condition. This is typically the case when you have to synchronize a table periodically with data from another source (table/view/query). In place of 3 separate unwieldy INSERT, UPDATE and DELETE statements with conditional sub-queries, the all-in-one MERGE does the job in one shot.

Oracle does not a have ready-made command to restart a sequence. There is no direct way to do this:

alter sequence test_seq restart with 0;

But I have a simple workaround to share with you. This article gives you a script to restart a sequence, explains how it works and shows you a test run with a sample sequence.

RETURNING an Auto-Increment Column Value

In the last post, we saw a neat way to implement auto-increment functionality in an Oracle table. The auto-incremented column gets its value populated in the background, without the issuer of the insert statement even getting to know about it.

BUT – what if the issuer of the insert statement does want to know about it?

You might want to use the current inserted row’s id, maybe for a further transaction in related tables, or maybe for tracing/logging purposes. The implementation is hidden, which means that you don’t know directly the value of the generated ID.

Auto-Increment Columns: FAQ

What is an auto-increment column?

An auto-increment column is one of which the value increments automatically each time a row is inserted into the table.

What is the use of auto-increment columns?

An auto-increment column usually serves as the primary key or unique identifier for each row of the table. Since the value automatically increments with each insert, the column guarantees that each row has a unique value associated with it.

An auto-increment column also gives useful information about the sequence of transactions. The higher the column value, the later the row was entered into the table. This can be useful for finding data such as

Replace Duplicates in ID Column with Unique Values

MERGE: Insert New Rows, Update Existing Rows in One Shot

How to Reset a Sequence in Oracle

RETURNING the value of an auto-increment column

Auto-Increment Columns: FAQ