Does Room's OnConflict.replace also works if Unique constraint fails?
When a UNIQUE or PRIMARY KEY constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally.
https://sqlite.org/lang_conflict.html
So if the UNIQUE constraint fails then what you want occurs; the following demonstrates this :-
DROP TABLE IF EXISTS test;
CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, col1 INTEGER, col2 INTEGER, col3 INTEGER DEFAULT (strftime('%s-%f','now')));
CREATE UNIQUE INDEX IF NOT EXISTS idx_test_col1_col2 ON test (col1,col2);
INSERT INTO test (col1,col2) VALUES (1,1),(1,2),(2,1),(2,2);
SELECT * FROM test;
INSERT OR REPLACE INTO TEST (col1,col2) VALUES (2,1);
SELECT * FROM test;
DROP TABLE IF EXISTS test; -- Cleanup
Which results in :-
- before the unique constraint
and then :-
- after the unique conflict.
Here's a more comprehensive test that demonstrates other permutations (note the last)
DROP TABLE IF EXISTS test;
CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, col1 INTEGER, col2 INTEGER, col3 INTEGER DEFAULT (strftime('%s-%f','now')));
CREATE UNIQUE INDEX IF NOT EXISTS idx_test_col1_col2 ON test (col1,col2);
INSERT INTO test VALUES (0,0,0,strftime('%s-%f','now'));
INSERT INTO test (col1,col2) VALUES (1,1),(1,2),(2,1),(2,2);
SELECT * FROM test; /* RESULT 1 INITIAL VALUES */
INSERT OR REPLACE INTO test (col1,col2) VALUES (2,1);
SELECT * FROM test; /* RESULT 2 UNIQUE conflict */
INSERT OR REPLACE INTO test (id,col1,col2) VALUES (1,7,7);
SELECT * FROM test; /* RESULT 3 PRIMARY KEY conflict */
INSERT OR REPLACE INTO test (id,col1,col2) VALUES (2,1,2);
SELECT * FROM test; /* RESULT 4 BOTH PRIMARY KEY and UNIQUE conflict on the same row (see timing) */
INSERT OR REPLACE INTO test (id,col1,col2) VALUES (4,7,7);
SELECT * FROM test; /* RESULTS 5 BOTH conflict BUT conflicts are for different rows */
DROP TABLE IF EXISTS test; -- Cleanup
This results in:-
- So with the last INSERT 2 rows were deleted. Row with id 4 due to the PRIMARY KEY conflict and also row with id 1 due to the UNIQUE conflict and 1 row (4,7,7) has been added.