Following ROWIDs Through an Oracle Unique Index Update

I've always been amazed by how Oracle Database handles updates to a unique column—performing set-based operations that don't violate the unique constraint, yet when executed row by row, it temporarily permits duplicates. SQL > create table franck ( val int unique ); Table created . SQL > insert into franck values ( - 1 ) , ( 1 ) ; 2 rows created . SQL > select val from franck ; VAL ---------- - 1 1 SQL > update franck set val =- val ; 2 rows updated . SQL > select val from franck ; VAL ---------- 1 - 1 From a SQL perspective, this is expected behavior, but not all databases support it without raising an error: Db2 , SQL Server , and Oracle handle it without error. PostgreSQL raises ERROR: duplicate key value violates unique constraint "franck_val_key", DETAIL: Key (val)=(1) already exists. This works with a deferred constraint. MySQL or MariaDB raise Duplicate entry '1' for key 'franck.val' SQLite raises { "code": "SQLITE_CONSTRAINT_UNIQUE" } MongoDB raises E11000 duplicate key error collection: test.franck index: val_1 dup key: { val: 1 } db . franck . createIndex ({ val : 1 }, { unique : true }); db . franck . insertMany ([ { val : - 1 }, { val : 1 } ]); db . franck . updateMany ({},[ { $set : { val : { $multiply :[ " $val " , - 1 ]} } } ]); MongoServerError : Plan executor error during update :: caused by :: E11000 duplicate key error collection : test . franck index : val_1 dup key : { val : 1 } This is surprising because Oracle unique indexes store the indexed columns as the B-tree key and the ROWID as the associated data. Non-unique indexes add the ROWID to the physical key and are required for a deferrable unique constraint to allow temporary duplication before the end of the transaction. So how do non-deferrable unique indexes allow duplication during a single update statement? In this simple example, I would expect: The initial index entries are: (-1): row #1 and (1): row #2 Updating the first row deletes the first entry (-1): row #1 and adds one with (1):