Documentation Source Text

  <li><p> A set of SQL constraints for each table. SQLite supports UNIQUE, NOT
       NULL, CHECK and FOREIGN KEY constraints.

  <li><p> Optionally, a [generated column] constraint.

  <li><p> Whether the table is a [WITHOUT ROWID] table.

  <li><p> Whether the table is subject to [strict type checking].
</ul>

<p>Every CREATE TABLE statement must specify a name for the new table.
  ^(Table names that begin with "sqlite_" are reserved for internal use. It
  is an error to attempt to create a table with a name that starts with
  "sqlite_".)^

  all other values, including other NULLs. ^If an [INSERT] or [UPDATE]
  statement attempts to modify the table content so that two or more rows
  have identical primary key values, that is a constraint violation.

<p> According to the SQL standard, PRIMARY KEY should always imply NOT NULL.
  Unfortunately, due to a bug in some early versions, this is not the
  case in SQLite. ^Unless the column is an [INTEGER PRIMARY KEY] or
  the table is a [WITHOUT ROWID] table or a [STRICT] table 
  or the column is declared NOT NULL,
  SQLite allows NULL values in a PRIMARY KEY column.  SQLite could be fixed to
  conform to the standard, but doing so might break legacy applications.
  Hence, it has been decided to merely document the fact that SQLite
  allows NULLs in most PRIMARY KEY columns.

<tcl>hd_fragment uniqueconst {UNIQUE} {unique constraint} {UNIQUE constraint} \
           {UNIQUE constraints}</tcl>

  time a new row is inserted into the table or an existing row is updated,
  the expression associated with each CHECK constraint is evaluated and
  cast to a NUMERIC value in the same way as a [CAST expression]. If the 
  result is zero (integer value 0 or real value 0.0), then a constraint
  violation has occurred.)^ ^If the CHECK expression evaluates to NULL, or
  any other non-zero value, it is not a constraint violation.
  ^The expression of a CHECK constraint may not contain a subquery.

<p>CHECK constraints are only verified when the table is written, not when
   it is read.  Furthermore, verification of CHECK constraints can be
   temporarily disabled using the "[PRAGMA ignore_check_constraints=ON;]"
   statement.  Hence, it is possible that a query might produce results that
   violate the CHECK constraints.

<tcl>hd_fragment {notnullconst} {NOT NULL} {NOT NULL constraint}\
                 {NOT NULL constraints}</tcl>
<h2>NOT NULL constraints</h2>
<p>^A NOT NULL constraint may only be attached to a column definition,
  not specified as a table constraint.  Not surprisingly, ^(a NOT NULL
  constraint dictates that the associated column may not contain a NULL value.
  Attempting to set the column value to NULL when inserting a new row or
  updating an existing one causes a constraint violation.)^  NOT NULL
  constraints are not verified during queries, so a query of a column might
  produce a NULL value even though the column is marked as NOT NULL, if the
  database file is corrupt.


<h1>Constraint enforcement</h1>

<p>Constraints are checked during [INSERT] and [UPDATE] and by
[PRAGMA integrity_check] and [PRAGMA quick_check] and sometimes
by [ALTER TABLE].  Queries and [DELETE]
statements do not normally verify constraints.
Hence, if a database file has been corrupted (perhaps by an external
program making direct changes to the database file without going through 
the SQLite library) a query might return data that violates a constraint.
For example:

<codeblock>
CREATE TABLE t1(x INT CHECK( x>3 ));
/* Insert a row with X less than 3 by directly writing into the
** database file using an external program */
PRAGMA integrity_check;  -- Reports row with x less than 3 as corrupt
INSERT INTO t1(x) VALUES(2);  -- Fails with SQLITE_CORRUPT
SELECT x FROM t1;  -- Returns an integer less than 3 in spite of the CHECK constraint
</codeblock>

<p>Enforcement of CHECK constraints can be temporarily disabled using
the [PRAGMA ignore_check_constraints=ON;] statement.

<h2>Response to constraint violations</h2>

<p>The response to a constraint violation is determined by the
  [conflict clause|constraint conflict resolution algorithm]. Each 
  PRIMARY KEY, UNIQUE, NOT NULL and CHECK constraint has a default conflict
  resolution algorithm. ^PRIMARY KEY, UNIQUE and NOT NULL constraints may be
  explicitly assigned a default conflict resolution algorithm by including
  a [conflict-clause] in their definitions. ^Or, if a constraint definition
  does not include a [conflict-clause] or it is a CHECK constraint, the default
  conflict resolution algorithm is ABORT. ^Different constraints within the

<title>STRICT Tables</title>
<tcl>
hd_keywords {STRICT tables} {STRICT table} {strict type checking} {STRICT}
</tcl>
<fancy_format>

<h1>Introduction</h1>

<p>^SQLite strives to be flexible regarding the datatype of
the content that it stores.  ^For example, if a table column has a type of