Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sqlite ON CONFLICT difference between ABORT and FAIL

From http://www.sqlite.org/lang_conflict.html

ABORT When an applicable constraint violation occurs, the ABORT resolution algorithm aborts the current SQL statement with an SQLITE_CONSTRAIT error and backs out any changes made by the current SQL statement; but changes caused by prior SQL statements within the same transaction are preserved and the transaction remains active. This is the default behavior and the behavior proscribed the SQL standard.

FAIL When an applicable constraint violation occurs, the FAIL resolution algorithm aborts the current SQL statement with an SQLITE_CONSTRAINT error. But the FAIL resolution does not back out prior changes of the SQL statement that failed nor does it end the transaction. For example, if an UPDATE statement encountered a constraint violation on the 100th row that it attempts to update, then the first 99 row changes are preserved but changes to rows 100 and beyond never occur.

Both preserve changes made before the statement that caused constraint violation and do not end transaction. So, I suppose the only difference is that FAIL resolution does not let further changes to be made, while ABORT does only back up only conflicting statement. Did I get right?

like image 976
sids Avatar asked Jul 06 '12 23:07

sids


People also ask

What does on conflict mean SQL?

The INSERT ON CONFLICT statement allows you to update an existing row that contains a primary key when you execute the INSERT statement to insert a new row that contains the same primary key. This feature is also known as UPSERT or INSERT OVERWRITE. It is similar to the REPLACE INTO statement of MySQL.

What is a constraint failure?

It probably means your insert statement is violating a constraint in the new table. Could be a primary key constraint, a unique constraint, a foreign key constraint (if you're using PRAGMA foreign_keys = ON; ), and so on. You fix that either by dropping the constraint, correcting the data, or dropping the data.


1 Answers

The answer is simple: FAIL does not rollback changes done by the current statement.

Consider those 2 tables:

CREATE TABLE IF NOT EXISTS constFAIL (num UNIQUE ON CONFLICT FAIL);
CREATE TABLE IF NOT EXISTS constABORT (num UNIQUE ON CONFLICT ABORT);
INSERT INTO constFAIL VALUES (1),(3),(4),(5);
INSERT INTO constABORT VALUES (1),(3),(4),(5);

The statement

UPDATE constABORT SET num=num+1 WHERE num<5

will fail and change nothing. But this satement

UPDATE constFAIL SET num=num+1 WHERE num<5

will update the first row, then fail and leave the 1 row updated, so the new values are 2, 3, 4, 5

like image 50
Johannes Kuhn Avatar answered Oct 25 '22 14:10

Johannes Kuhn