Safe Row Updates in Oracle Without Reliable Unique Keys

One of the harder legacy-database failures is a table that appears to have a record identifier at the application level while the database does not actually guarantee that identifier to be unique. A CC_ID, record number, or timestamp may be treated as a key by application code, yet duplicate physical rows can exist when there is no supporting PRIMARY KEY or UNIQUE constraint. In that situation, WHERE id = :id does not express a single-row update; it can modify every matching row.

Separate business identity from physical row identity

A business key tells the application how a record is named. Physical row identity answers a narrower question: which table row is being changed in this transaction. In an immutable legacy schema, treating those as equivalent is unsafe.

Oracle ROWID can address a row in a heap-organized table efficiently. It is therefore useful after duplicate candidates have been resolved and one physical row must be targeted. It must not become a durable business key: row movement, rebuilds, or export/import can change it. Its useful scope is the transaction or operation that selected the row.

Make the choice deterministic

A rule such as “take the newest row” is incomplete when two rows have the same timestamp. The ordering must break ties completely:

SELECT rid
FROM (
    SELECT ROWID rid,
           ROW_NUMBER() OVER (
               ORDER BY operation_time DESC, ROWID DESC
           ) rn
    FROM legacy_table
    WHERE business_id = :businessId
)
WHERE rn = 1;

ROWID in the second ordering position carries no business meaning. It simply gives equal-timestamp candidates a stable physical ordering. A meaningful unique secondary attribute should be preferred when one exists.

Close the race between selection and update

Selecting a row and updating it later leaves a concurrency window. When the operation requires one physical row to remain the target, selection and locking belong to the same transaction boundary. SELECT ... FOR UPDATE or ORM pessimistic locking can provide that boundary when the underlying object is updateable.

Not every view supports this pattern. Views containing DISTINCT, grouping, aggregation, or certain set operations can make row locking or updateability invalid. In such cases, forcing a lock through the view is less clear than resolving the base-table row and applying the write there.

ORM identity does not repair duplicate data

JPA and Hibernate assume stable entity identity. Mapping a non-unique business identifier as though it were a key does not make the source unique; it can instead create ambiguous first-level cache, dirty-checking, and update behavior. When the schema cannot be changed, the read representation and the physical write target may need separate treatment.

Boundaries

This technique is not a replacement for a sound schema. Where schema evolution is possible, a primary key or appropriate unique constraint remains the correct solution. ROWID is a controlled targeting mechanism for legacy constraints, not a new domain identifier.

The “newest row wins” rule is also a business rule, not merely an SQL ordering trick. The authoritative timestamp, treatment of NULL, and tie-breaking policy must be defined explicitly.

Related material: Oracle Database and PL/SQL: Architecture, SQL and Performance, ROWID, Pessimistic Locking, Consistent Read.

References

• Oracle Database 19c SQL Language Reference — ROWID Pseudocolumn
• Oracle Database 19c Concepts — Data Concurrency and Consistency