Documentation Source Text

Check-in [217545f050]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Clarify that if a CHECK expression evaluates to NULL, it is not a constraint violation.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 217545f0500e177abf5a0c50505fb91537bf88d9
User & Date: dan 2010-10-01 19:03:32
Context
2010-10-06
12:23
Update the change documentation for the 3.7.3 release. Fixes to the description of the index_info pragma. check-in: d009514359 user: drh tags: trunk
2010-10-01
19:03
Clarify that if a CHECK expression evaluates to NULL, it is not a constraint violation. check-in: 217545f050 user: dan tags: trunk
2010-09-30
18:23
Fix a typo on lang_createtable.html. check-in: 2d50d47170 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814


815
816
817
818
819
820
821
  are considered distinct from all other values (including other NULLs).
  ^If an [INSERT] or [UPDATE] statement attempts to modify the table content so
  that two or more rows feature identical values in a set of columns that
  are subject to a UNIQUE constraint, it is a constraint violation.

<p>^[INTEGER PRIMARY KEY] columns aside, both UNIQUE and PRIMARY KEY
  constraints are implemented by creating an index in the database (in the same
  way as a [CREATE INDEX|"CREATE UNIQUE INDEX"] statement would). ^This index
  is used like any other index in the database to [optimizer|optimize queries].
  As a result, there often no advantage (but significant overhead) in creating
  an index on a set of columns that are already collectively subject to a
  UNIQUE or PRIMARY KEY constraint.

<p>^(A <b>CHECK</b> constraint may be attached to a column definition or
  specified as a table constraint. In practice it makes no difference.)^ ^(Each
  time a new row is inserted into the table or an existing row is updated,
  the expression associated with each CHECK constraint is evaluated as a
  [boolean expression]. If the result is false, then a constraint violation
  has occurred.)^



<p>CHECK constraints have been supported since [version 3.3.0]. Prior to
  version 3.3.0, CHECK constraints were parsed but not enforced.

<p>^A <b>NOT NULL</b> constraint may only be attached to a single column.
  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







|
|
|
|
|




|
|
|
>
>







796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
  are considered distinct from all other values (including other NULLs).
  ^If an [INSERT] or [UPDATE] statement attempts to modify the table content so
  that two or more rows feature identical values in a set of columns that
  are subject to a UNIQUE constraint, it is a constraint violation.

<p>^[INTEGER PRIMARY KEY] columns aside, both UNIQUE and PRIMARY KEY
  constraints are implemented by creating an index in the database (in the same
  way as a [CREATE INDEX|"CREATE UNIQUE INDEX"] statement would). ^Such an 
  index is used like any other index in the database to [optimizer|optimize
  queries].  As a result, there often no advantage (but significant overhead)
  in creating an index on a set of columns that are already collectively
  subject to a UNIQUE or PRIMARY KEY constraint.

<p>^(A <b>CHECK</b> constraint may be attached to a column definition or
  specified as a table constraint. In practice it makes no difference.)^ ^(Each
  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.

<p>CHECK constraints have been supported since [version 3.3.0]. Prior to
  version 3.3.0, CHECK constraints were parsed but not enforced.

<p>^A <b>NOT NULL</b> constraint may only be attached to a single column.
  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