Conflict Resolution Algorithms

Page 28 of 41
[prev] [next]

Constraint violations can be handled in 5 ways:
- ROLLBACK - Undo all changes and exit the transaction.
- ABORT - Current statement is undone. Prior statements remain. (Default)
- FAIL - Statement stops. Partial results are retained.
- IGNORE - No change to current row. Continue executing.
- REPLACE - Delete prior rows that caused the conflict.

CREATE TABLE tbl1(
   a INTEGER UNIQUE ON CONFLICT IGNORE,
   b ANY NOT NULL ON CONFLICT ROLLBACK
);

CREATE UNIQUE INDEX idx1 ON tbl1(a,b) ON CONFLICT REPLACE;

INSERT OR REPLACE INTO tbl1 VALUES(1,2);
- Or use just "REPLACE" as shorthand for "INSERT OR REPLACE"

UPDATE OR IGNORE tbl1 SET a=a+1 WHERE b=2;