sqlite3/www/conflict.tcl

   1 #
   2 # Run this Tcl script to generate the constraint.html file.
   3 #
   4 set rcsid {$Id: conflict.tcl,v 1.4 2004/10/10 17:24:55 drh Exp $ }
   5 source common.tcl
   6 header {Constraint Conflict Resolution in SQLite}
   7 puts {
   8 <h1>Constraint Conflict Resolution in SQLite</h1>
   9
  10 <p>
  11 In most SQL databases, if you have a UNIQUE constraint on
  12 a table and you try to do an UPDATE or INSERT that violates
  13 the constraint, the database will abort the operation in
  14 progress, back out any prior changes associated with
  15 UPDATE or INSERT command, and return an error.
  16 This is the default behavior of SQLite.
  17 Beginning with version 2.3.0, though, SQLite allows you to
  18 define alternative ways for dealing with constraint violations.
  19 This article describes those alternatives and how to use them.
  20 </p>
  21
  22 <h2>Conflict Resolution Algorithms</h2>
  23
  24 <p>
  25 SQLite defines five constraint conflict resolution algorithms
  26 as follows:
  27 </p>
  28
  29 <dl>
  30 <dt><b>ROLLBACK</b></dt>
  31 <dd><p>When a constraint violation occurs, an immediate ROLLBACK
  32 occurs, thus ending the current transaction, and the command aborts
  33 with a return code of SQLITE_CONSTRAINT.  If no transaction is
  34 active (other than the implied transaction that is created on every
  35 command) then this algorithm works the same as ABORT.</p></dd>
  36
  37 <dt><b>ABORT</b></dt>
  38 <dd><p>When a constraint violation occurs, the command backs out
  39 any prior changes it might have made and aborts with a return code
  40 of SQLITE_CONSTRAINT.  But no ROLLBACK is executed so changes
  41 from prior commands within the same transaction
  42 are preserved.  This is the default behavior for SQLite.</p></dd>
  43
  44 <dt><b>FAIL</b></dt>
  45 <dd><p>When a constraint violation occurs, the command aborts with a
  46 return code SQLITE_CONSTRAINT.  But any changes to the database that
  47 the command made prior to encountering the constraint violation
  48 are preserved and are not backed out.  For example, if an UPDATE
  49 statement encountered a constraint violation on the 100th row that
  50 it attempts to update, then the first 99 row changes are preserved
  51 by change to rows 100 and beyond never occur.</p></dd>
  52
  53 <dt><b>IGNORE</b></dt>
  54 <dd><p>When a constraint violation occurs, the one row that contains
  55 the constraint violation is not inserted or changed.  But the command
  56 continues executing normally.  Other rows before and after the row that
  57 contained the constraint violation continue to be inserted or updated
  58 normally.  No error is returned.</p></dd>
  59
  60 <dt><b>REPLACE</b></dt>
  61 <dd><p>When a UNIQUE constraint violation occurs, the pre-existing row
  62 that caused the constraint violation is removed prior to inserting
  63 or updating the current row.  Thus the insert or update always occurs.
  64 The command continues executing normally.  No error is returned.</p></dd>
  65 </dl>
  66
  67 <h2>Why So Many Choices?</h2>
  68
  69 <p>SQLite provides multiple conflict resolution algorithms for a
  70 couple of reasons.  First, SQLite tries to be roughly compatible with as
  71 many other SQL databases as possible, but different SQL database
  72 engines exhibit different conflict resolution strategies.  For
  73 example, PostgreSQL always uses ROLLBACK, Oracle always uses ABORT, and
  74 MySQL usually uses FAIL but can be instructed to use IGNORE or REPLACE.
  75 By supporting all five alternatives, SQLite provides maximum
  76 portability.</p>
  77
  78 <p>Another reason for supporting multiple algorithms is that sometimes
  79 it is useful to use an algorithm other than the default.
  80 Suppose, for example, you are
  81 inserting 1000 records into a database, all within a single
  82 transaction, but one of those records is malformed and causes
  83 a constraint error.  Under PostgreSQL or Oracle, none of the
  84 1000 records would get inserted.  In MySQL, some subset of the
  85 records that appeared before the malformed record would be inserted
  86 but the rest would not.  Neither behavior is especially helpful.
  87 What you really want is to use the IGNORE algorithm to insert
  88 all but the malformed record.</p>
  89
  90 }
  91 footer $rcsid