Does Room's OnConflict.replace also works if Unique constraint fails?

Question

So, i have a room database where apart from the Primary key i have a unique indexed column too !

what i want is, IF -> the unique constraint fails - replace the old row with new row ( changing the PRIMARY KEY too ), will OnConflictStrategy.REPLACE help ?

or should i delete the previous data upon insert fail and add the new one ?

Answer 1

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.