# Safe Row Updates in Oracle Without Reliable Unique Keys

> Why a single-row update is unsafe in legacy Oracle tables whose uniqueness the database never enforced: separating business key from physical row identity, deterministic candidate selection, ROWID targeting, transaction boundaries, and ORM identity assumptions.

- Author: Muhammet Ali Köker
- Language: en
- Canonical: https://alikoker.com.tr/en/safe-row-updates-in-oracle-without-reliable-unique-keys
- Translation: https://alikoker.com.tr/oracle-benzersiz-anahtar-olmadan-guvenli-satir-guncelleme
- Published: 2020-06-18T12:00:00+03:00
- Modified: 2026-09-02T00:00:00+03:00
- Verified: 2026-09-02T00:00:00+03:00
- Type: article

The most dangerous table in a legacy database is the one whose application-level record identifier is not enforced by the database. A record number, document number, or operation timestamp is treated as a key in code, but without a `PRIMARY KEY` or a validated `UNIQUE` constraint the same value can exist in several physical rows. Against such a table, `UPDATE t SET ... WHERE record_no = :no` does not express a single-row update; it changes every matching row. Code that uses the same column in a scalar subquery stops with `ORA-01427: single-row subquery returns more than one row`, and on the JPA side `getSingleResult()` throws `NonUniqueResultException`. The failure is not a gap in SQL knowledge. It comes from treating a business key and a physical row identity as the same thing.

## Where the duplicates come from

Duplicates in an unconstrained table arrive through a handful of typical routes, and the remediation depends on which one applies. The most common case is that the constraint was never defined: uniqueness was checked in the application, and the check stopped holding the moment a second application began writing to the table. The second case is a constraint that was disabled for a bulk load and never re-enabled; a `DISABLE`d constraint blocks nothing during the load. The third case is data from two separate systems migrated into one table; each source was unique on its own, the union is not.

The fourth case is more subtle: physically different rows may represent the same business record after application-level normalization. Case, trailing spaces, character normalization, and `NULL` values in composite keys must be treated explicitly. Oracle treats a zero-length character string as `NULL`, while the uniqueness semantics of keys containing `NULL` also depend on the other key components and the constraint definition. Duplicate detection should therefore use the same normalization rules as the real business key.

Diagnosis always precedes the update. The first query should reliably identify only which business keys map to more than one physical row; whether candidate rows are equivalent in content should then be examined separately over the columns that matter to the domain. Building a fingerprint by concatenating a few columns inside `COUNT(DISTINCT ...)` can be misleading because of `NULL`, NLS conversion, and delimiter collisions.

```sql
SELECT record_no,
       COUNT(*) AS cnt
FROM   transaction_table
GROUP  BY record_no
HAVING COUNT(*) > 1;
```

Whether rows under the same business key are truly equivalent is a domain decision. No row set should be called an exact duplicate until the comparison column set has been defined explicitly.

## Business key versus physical row identity

A business key states how the application names a record. Physical row identity answers a narrower question: which table row this operation will change. In a healthy schema with the constraint in place the two coincide and the distinction looks academic. Without the constraint the coincidence disappears, and every write has to answer both parts separately: which row was selected, and did it remain the same row until the write.

Code written without that separation fails in two ways. The first is a scope error: a statement intended to update one row updates n rows, and `SQL%ROWCOUNT` returns n instead of the expected 1. The second is an ambiguity error: the code reads one of the n rows, decides, then writes using the "same" key; the row read and the row written need not be the same. The first failure is noisy and shows up in testing; the second is silent and accumulates in production as data inconsistency.

## A deterministic selection rule

"The newest record wins" is not sufficient on its own, and it is not always correct either. That rule applies only when the domain is genuinely defined that way, that is, when a correction flow exists in which a later row for the same business key supersedes the earlier one. In a merge where independent source systems produced their own records, the newest row is not necessarily the most accurate one; there the rule may be source precedence or completeness.

Whatever the rule, it must state three things explicitly: which column is authoritative, where `NULL` sorts in that order, and which row wins on an exact tie. Ties are not rare in a `DATE` column with second resolution; rows arriving through a bulk load can share one stamp across dozens of records.

```sql
SELECT rid
FROM (
    SELECT ROWID AS rid,
           ROW_NUMBER() OVER (
               ORDER BY operation_time DESC NULLS LAST,
                        version_no     DESC NULLS LAST,
                        ROWID          DESC
           ) AS rn
    FROM   transaction_table
    WHERE  record_no = :recordNo
)
WHERE rn = 1;
```

The trailing `ROWID` carries no business meaning; it completes the ordering among the currently available candidates with identical timestamp and version. As long as the candidate set and physical row placement remain unchanged, it breaks the tie deterministically. This matters because an ordering that leaves ties unresolved can let two application servers target different physical rows for the same business key. When the data model offers a meaningful and unique secondary criterion (a version number, a source-system code, a count of populated fields) it should precede `ROWID` in the ordering; `ROWID` is the tie-breaker of last resort.

The same result can be produced with the `MAX(...) KEEP (DENSE_RANK FIRST ORDER BY ...)` aggregate instead of an analytic function; both forms run in a single pass. The syntax is secondary. What matters is that the ordering defines a total order that breaks every tie.

## What ROWID addresses and what it does not

The Oracle `ROWID` pseudocolumn encodes the location of a heap-table row: data object number, relative file number, block number, and row slot within the block. As long as the row stays where it is, this address provides a direct single-row access path; it can appear in execution plans as `TABLE ACCESS BY USER ROWID` and avoids a separate business-key index probe. That is exactly the property required to target one row after a choice has been made among duplicate business keys.

`ROWID` is not, however, a durable identity. `ALTER TABLE ... MOVE`, `SHRINK SPACE`, an update of the partition key in a partitioned table with row movement enabled, export/import, and Flashback Table can all change the address. Index-organized tables return a logical `UROWID` tied to the primary key rather than to a physical slot. The address of a deleted row can be reassigned to another row. `ROWID` is therefore not something to keep in application memory, in an HTTP session, or in another table; it is a temporary address carried between selection and update inside one transaction boundary. Storing it and using it later invites `ORA-08006: specified row no longer exists`, or worse, a silent update of a different row that now occupies that address.

## Closing the race between selection and update

Selecting a candidate, returning its address to the application, and updating it in a second request opens a window between the two operations. In that window another session can insert a new row for the same business key, delete the selected row, or update it with a different value. If the update is meant to hit the selected row, selection, locking, and update must stay in the same transaction.

The simplest form is a single statement:

```sql
UPDATE transaction_table
SET    status = :newStatus,
       updated_at = SYSTIMESTAMP
WHERE  ROWID = (
    SELECT rid FROM (
        SELECT ROWID AS rid,
               ROW_NUMBER() OVER (
                   ORDER BY operation_time DESC NULLS LAST, ROWID DESC
               ) AS rn
        FROM   transaction_table
        WHERE  record_no = :recordNo
    )
    WHERE rn = 1
);
```

A single statement is evaluated under Oracle's statement-level read-consistency and write-consistency rules; candidate selection and DML are parts of the same SQL statement, and `ROWID = (scalar_subquery)` targets at most one physical row as long as the subquery returns zero or one address. When the application must read, decide, and then write, selection and locking should remain in one transaction. Applying `FOR UPDATE` directly to an analytic inline view depends on view merging and can fail with errors such as `ORA-02014`. A clearer pattern is to resolve the address first and then lock the base-table row:

```sql
SELECT rid
FROM (
    SELECT ROWID AS rid,
           ROW_NUMBER() OVER (
               ORDER BY operation_time DESC NULLS LAST, ROWID DESC
           ) AS rn
    FROM   transaction_table
    WHERE  record_no = :recordNo
)
WHERE rn = 1;

SELECT status, amount
FROM   transaction_table
WHERE  ROWID = :rid
FOR UPDATE WAIT 3;
```

Both statements must execute in the same transaction. If the second query returns no row, the selected row no longer exists and that is a distinct error path. `FOR UPDATE` waits by default; `NOWAIT` returns immediately, while `WAIT n` bounds the wait. `SKIP LOCKED` may skip the intended row and is therefore unsuitable when the semantics require one specific business record. After the lock is acquired, the update uses `WHERE ROWID = :rid` and its affected-row count is checked again.

Oracle combines read consistency with write consistency during DML; concurrent changes may cause internal re-evaluation or statement restart behavior when the target row no longer matches the version initially observed. This mechanism does not by itself solve the general lost-update problem, especially when an application performs separate read-decide-write statements. Those flows still need locking or version checks. The decision about which row is "newest" must likewise be interpreted in the context of the transaction isolation level and the consistent view seen by the statement. If a source keeps inserting new rows for the same business key, the race between selection and insertion cannot be fully closed without fixing the data model; what is gained is that the target is always exactly one identified row.

How long the lock is held is also a design decision. Holding a row while a user thinks in front of a screen is not acceptable; such flows call for optimistic version checking rather than a pessimistic lock. A version column, however, does not determine the target of the update; it only detects interference. The target is still determined by deterministic selection and `ROWID` addressing.

## Updating through views and the key-preserved rule

Legacy applications are often bound to views rather than tables. An `UPDATE` through a join view is subject to Oracle's key-preserved table rule: every row of the view must correspond to exactly one row of the base table, which only holds when the join key is unique on the other side. A table without a unique constraint cannot be key-preserved by definition, so the update fails with `ORA-01779: cannot modify a column which maps to a non key-preserved table`. Views containing `DISTINCT`, `GROUP BY`, aggregation, `UNION`, or other set operators are not inherently updatable, and `FOR UPDATE` is rejected against them as well (`ORA-02014`).

Rather than forcing a lock through the view, two steps are clearer: keep the view as a read model, resolve the physical row of the base table with the rule above, and write directly to the table. An `INSTEAD OF` trigger can make the view updatable, but a selection rule buried in a trigger becomes invisible to the calling code and merely moves the ambiguity one layer down.

## Working with JPA and Hibernate

The JPA entity model assumes stable identity: the `@Id` column is the key of the first-level cache, dirty checking is keyed on it, and the statement produced at `flush` time is `UPDATE ... WHERE id = ?`. Mapping a non-unique column as `@Id` does not make the data unique; two different physical rows collapse into one cached object, it becomes unclear which row is being written, and the generated `UPDATE` changes both.

When the schema cannot change, the read model and the write model should be separated. Mapping duplicate business keys to a JPA entity `@Id` as if they were unique is unsafe; a DTO/projection or controlled native read model plus a separate physical-row write path is more predictable. Hibernate `@RowId` can use a rowid-like locator for CRUD operations on supported dialects, but it does not remove the entity-identity requirement and does not make a non-unique fake `@Id` safe. If the legacy table has no stable entity identifier, explicit `ROWID`-targeted SQL or a procedure-based write layer is usually clearer. `LockModeType.PESSIMISTIC_WRITE`, lock-timeout hints, and `@RowId` behavior are version- and dialect-sensitive and should be verified from the SQL actually emitted instead of assuming a particular `NOWAIT` or `ROWID` form.

The same problem becomes more visible in bulk operations. When the row count expected by the ORM differs from the number actually affected, optimistic-lock or stale-state failures can surface. On a table with duplicate business keys, such failures should not be retried blindly; the generated `UPDATE` predicate and physical affected-row count should be inspected first, otherwise a data-model defect can be mistaken for a transient concurrency conflict.

## Bulk cleanup when a constraint can be added

When the schema can evolve, the real fix is to remove duplicates and add the constraint. The classic `DELETE ... WHERE ROWID NOT IN (SELECT MIN(ROWID) ... GROUP BY record_no)` works, but `MIN(ROWID)` means neither "oldest" nor "most correct"; it merely picks the physically lowest address. The surviving row should be chosen by the business rule described above, and the delete should target the addresses with `rn > 1` in the same `ROW_NUMBER()` ordering.

If cleanup cannot be completed in one pass, `ENABLE NOVALIDATE` can be part of a transition strategy: existing rows are not revalidated while subsequent DML is checked. For `PRIMARY KEY` or `UNIQUE` constraints, however, the supporting-index structure and the exact DDL sequence must be planned around the existing duplicates; a single `ADD CONSTRAINT ... ENABLE NOVALIDATE` statement should not be assumed to succeed for every starting schema. Where appropriate, a suitable non-unique index can be prepared first and existing violations identified separately with `EXCEPTIONS INTO`. The goal is to stop new violations while old data is being cleaned.

## Decision rules

The discussion above reduces to a small set of rules in practice.

```text
Constraint can be added        → clean first, then add UNIQUE; the rest is a stopgap
Constraint cannot be added     → business key selects, ROWID targets
Selection rule                 → defined by the domain; "newest" is not a default
Ties                           → keep adding criteria until the ordering is a total order
Read-decide-write flow         → one transaction, FOR UPDATE with a bounded wait
Single-step update             → one UPDATE statement with ROW_NUMBER in a subquery
Lifetime of a ROWID            → between selection and write; never persisted
Complex view                   → write to the base table, do not hide it in INSTEAD OF
ORM                            → separate the read model from the write target
Affected row count             → expect 1; zero and n are distinct error paths
```

The last line is the one most often skipped. If `SQL%ROWCOUNT`, or the update count returned through JDBC, is not checked, none of the rules above has been verified to work.

## Guarantees provided and not provided

A total ordering, `ROWID` targeting within the same transaction, and `FOR UPDATE` on the base-table row where needed provide three useful properties: a write targets at most one physical row; ties are broken consistently while the candidate set and physical placement remain unchanged; and once the row lock is acquired, another session cannot modify that same row until the lock is released. What is not provided is equally clear: the approach does not remove duplicates, does not prove that the selection rule is correct from a business standpoint, does not stop a source from inserting new rows under the same key, and does not substitute for a schema fix. In a system where the constraint can be added, this is not the preferred solution but a way to keep operating without corrupting data during the transition.

Related material: [Oracle Database and PL/SQL: Architecture, SQL, and Performance](/en/oracle-database-plsql-architecture-sql-performance), [Safe Numeric Conversion in Oracle SQL](/en/safe-numeric-conversion-in-oracle-sql), [Building a Reflection-Based ORM in Java](/en/building-a-reflection-based-orm-in-java), [ROWID](/en/wiki/rowid), [Pessimistic Locking](/en/wiki/pessimistic-locking), [Optimistic Locking](/en/wiki/optimistic-locking), [Consistent Read](/en/wiki/consistent-read), [MVCC](/en/wiki/mvcc).

## References

- Oracle. ROWID Pseudocolumn. Oracle Database SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWID-Pseudocolumn.html
- Oracle. SELECT: FOR UPDATE Clause. Oracle Database SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
- Oracle. Data Concurrency and Consistency. Oracle Database Concepts, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-concurrency-and-consistency.html
- Oracle. Managing Views, Sequences, and Synonyms. Oracle Database Administrator's Guide, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-views-sequences-and-synonyms.html
- Oracle. Managing Integrity Constraints. Oracle Database Administrator's Guide, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-integrity.html
- Red Hat. Hibernate ORM User Guide: Locking. https://docs.jboss.org/hibernate/orm/6.6/userguide/html_single/Hibernate_User_Guide.html
- Thomas Kyte, Darl Kuhn. Expert Oracle Database Architecture, 3rd Edition. Apress, 2014.

## Cite This Work

Köker, M. A. (2020). Safe Row Updates in Oracle Without Reliable Unique Keys. alikoker.com.tr. https://alikoker.com.tr/en/safe-row-updates-in-oracle-without-reliable-unique-keys

- BibTeX: https://alikoker.com.tr/en/safe-row-updates-in-oracle-without-reliable-unique-keys.bib
- RIS: https://alikoker.com.tr/en/safe-row-updates-in-oracle-without-reliable-unique-keys.ris
- CSL-JSON: https://alikoker.com.tr/en/safe-row-updates-in-oracle-without-reliable-unique-keys.csl.json