Documentation Source Text
Check-in [1f3d32]
Not logged in

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

Overview
Comment:Add a requirement that CHECK constraints cannot contain subqueries.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:1f3d321253ced57c80992c6f898c144a24b87b69
User & Date: drh 2011-05-13 16:47:34
Context
2011-05-18
13:14
Add documentation for 8+3 filenames. check-in: 4e973e user: drh tags: trunk
2011-05-13
16:57
Rather than document goofy behavior, better to make the behavior less goofy. (Was: Clarify in the documentation that last_insert_rowid() does not work for virtual tables.) Closed-Leaf check-in: f10211 user: drh tags: mistake
16:47
Add a requirement that CHECK constraints cannot contain subqueries. check-in: 1f3d32 user: drh tags: trunk
2011-05-11
20:53
Improvements to capi3ref hyperlinking. check-in: b12665 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
...
820
821
822
823
824
825
826

827
828
829
830
831
832
833
834

835
836
837
838
839
840
841
<li>^If a NOT NULL constraint is specified, then the column must have a
    default value other than NULL.
<li>^If [foreign key constraints] are [foreign_keys pragma | enabled] and
    a column with a [foreign-key-clause | REFERENCES clause]
    is added, the column must have a default value of NULL.
</ul>

<p>^Note also that when adding a CHECK constraint, the CHECK constraint
is not tested against preexisting rows of the table.
^This can result in a table that contains data that
is in violation of the CHECK constraint.  Future versions of SQLite might
change to validate CHECK constraints as they are added.</p>

<p> The execution time of the ALTER TABLE command is independent of
the amount of data in the table.  The ALTER TABLE command runs as quickly
................................................................................
  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 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.







|







 







>








>







163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
...
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
<li>^If a NOT NULL constraint is specified, then the column must have a
    default value other than NULL.
<li>^If [foreign key constraints] are [foreign_keys pragma | enabled] and
    a column with a [foreign-key-clause | REFERENCES clause]
    is added, the column must have a default value of NULL.
</ul>

<p>^Note also that when adding a [CHECK constraint], the CHECK constraint
is not tested against preexisting rows of the table.
^This can result in a table that contains data that
is in violation of the CHECK constraint.  Future versions of SQLite might
change to validate CHECK constraints as they are added.</p>

<p> The execution time of the ALTER TABLE command is independent of
the amount of data in the table.  The ALTER TABLE command runs as quickly
................................................................................
  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.

<tcl>hd_fragment {ckconst} {CHECK} {CHECK constraint} {CHECK constraints}</tcl>
<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.
  ^The expression of a CHECK constraint may not contain a subquery.

<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 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.