## Run this Tcl script to generate the constraint.html file.#set rcsid {$Id: conflict.tcl,v 1.4 2004/10/10 17:24:55 drh Exp $ }source common.tclheader {Constraint Conflict Resolution in SQLite}puts {<h1>Constraint Conflict Resolution in SQLite</h1><p>In most SQL databases, if you have a UNIQUE constraint ona table and you try to do an UPDATE or INSERT that violatesthe constraint, the database will abort the operation inprogress, back out any prior changes associated withUPDATE or INSERT command, and return an error.This is the default behavior of SQLite.Beginning with version 2.3.0, though, SQLite allows you todefine alternative ways for dealing with constraint violations.This article describes those alternatives and how to use them.</p><h2>Conflict Resolution Algorithms</h2><p>SQLite defines five constraint conflict resolution algorithmsas follows:</p><dl><dt><b>ROLLBACK</b></dt><dd><p>When a constraint violation occurs, an immediate ROLLBACKoccurs, thus ending the current transaction, and the command abortswith a return code of SQLITE_CONSTRAINT.  If no transaction isactive (other than the implied transaction that is created on everycommand) then this algorithm works the same as ABORT.</p></dd><dt><b>ABORT</b></dt><dd><p>When a constraint violation occurs, the command backs outany prior changes it might have made and aborts with a return codeof SQLITE_CONSTRAINT.  But no ROLLBACK is executed so changesfrom prior commands within the same transactionare preserved.  This is the default behavior for SQLite.</p></dd><dt><b>FAIL</b></dt><dd><p>When a constraint violation occurs, the command aborts with areturn code SQLITE_CONSTRAINT.  But any changes to the database thatthe command made prior to encountering the constraint violationare preserved and are not backed out.  For example, if an UPDATEstatement encountered a constraint violation on the 100th row thatit attempts to update, then the first 99 row changes are preservedby change to rows 100 and beyond never occur.</p></dd><dt><b>IGNORE</b></dt><dd><p>When a constraint violation occurs, the one row that containsthe constraint violation is not inserted or changed.  But the commandcontinues executing normally.  Other rows before and after the row thatcontained the constraint violation continue to be inserted or updatednormally.  No error is returned.</p></dd><dt><b>REPLACE</b></dt><dd><p>When a UNIQUE constraint violation occurs, the pre-existing rowthat caused the constraint violation is removed prior to insertingor updating the current row.  Thus the insert or update always occurs.The command continues executing normally.  No error is returned.</p></dd></dl><h2>Why So Many Choices?</h2><p>SQLite provides multiple conflict resolution algorithms for acouple of reasons.  First, SQLite tries to be roughly compatible with asmany other SQL databases as possible, but different SQL databaseengines exhibit different conflict resolution strategies.  Forexample, PostgreSQL always uses ROLLBACK, Oracle always uses ABORT, andMySQL usually uses FAIL but can be instructed to use IGNORE or REPLACE.By supporting all five alternatives, SQLite provides maximumportability.</p><p>Another reason for supporting multiple algorithms is that sometimesit is useful to use an algorithm other than the default.Suppose, for example, you areinserting 1000 records into a database, all within a singletransaction, but one of those records is malformed and causesa constraint error.  Under PostgreSQL or Oracle, none of the1000 records would get inserted.  In MySQL, some subset of therecords that appeared before the malformed record would be insertedbut the rest would not.  Neither behavior is especially helpful.What you really want is to use the IGNORE algorithm to insertall but the malformed record.</p>}footer $rcsid