Listing 5: Controlling conflict resolution.
SQLite version 2.8.2 Enter ".help" for instructions sqlite> -- Create table; sqlite> create table emp(name text UNIQUE ON CONFLICT ROLLBACK); sqlite> -- Populate; sqlite> insert into emp values('Larry'); sqlite> insert into emp values('Moe'); sqlite> insert into emp values('Curly'); sqlite> -- generate a UNIQUE constraint violation; sqlite> insert into emp values('Curly'); SQL error: uniqueness constraint failed sqlite> -- try to commit, won't work as previous resolution rolled back transaction. sqlite> commit; SQL error: cannot commit - no transaction is active sqlite> -- Set REPLACE at transaction scope. sqlite> begin on conflict replace; sqlite> -- try again: this time it will work sqlite> insert into emp values('Curly'); sqlite> commit; sqlite> -- Play around with statement level resolution; sqlite> begin on conflict replace; sqlite> -- ABORT will stop us, but leave transaction running. sqlite> insert or ABORT into emp values('Curly'); SQL error: uniqueness constraint failed sqlite> -- FAIL will stop us, but leave transaction running. sqlite> insert or FAIL into emp values('Curly'); SQL error: uniqueness constraint failed sqlite> -- IGNORE will silently fail, but leave transaction running. sqlite> insert or IGNORE into emp values('Curly'); sqlite> -- default transaction scope is REPLACE, will push it through. sqlite> insert into emp values('Curly'); sqlite> commit; sqlite>