Documentation Source Text

Check-in [68d2666398]
Login

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

Overview
Comment:Enhance the file format documentation with additional information about internal schema objects.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 68d26663983d22074d2eb60a6816591c64561ac4
User & Date: drh 2012-03-29 14:14:50
Context
2012-03-31
12:10
Fix typo in the fileformat2 document. check-in: 912e06a446 user: drh tags: trunk
2012-03-29
14:14
Enhance the file format documentation with additional information about internal schema objects. check-in: 68d2666398 user: drh tags: trunk
2012-03-28
17:00
Add the typeof()/length() optimization to the change log. check-in: a2d5d784a5 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/autoinc.in.

73
74
75
76
77
78
79
80

81
82
83
84
85
86
87
88
89
has previously held a row with the largest possible ROWID, then new INSERTs
are not allowed and any attempt to insert a new row will fail with an
SQLITE_FULL error.
</p>

<p>
^SQLite keeps track of the largest ROWID that a table has ever held using
the special SQLITE_SEQUENCE table.  ^The SQLITE_SEQUENCE table is created

and initialized automatically whenever a normal table that contains an
AUTOINCREMENT column is created.  ^The content of the SQLITE_SEQUENCE table
can be modified using ordinary UPDATE, INSERT, and DELETE statements.
^But making modifications to this table will likely perturb the AUTOINCREMENT
key generation algorithm.  Make sure you know what you are doing before
you undertake such changes.
</p>

<p>







|
>

|







73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
has previously held a row with the largest possible ROWID, then new INSERTs
are not allowed and any attempt to insert a new row will fail with an
SQLITE_FULL error.
</p>

<p>
^SQLite keeps track of the largest ROWID that a table has ever held using
an [internal table] named "[sqlite_sequence]".
^The sqlite_sequence table is created
and initialized automatically whenever a normal table that contains an
AUTOINCREMENT column is created.  ^The content of the sqlite_sequence table
can be modified using ordinary UPDATE, INSERT, and DELETE statements.
^But making modifications to this table will likely perturb the AUTOINCREMENT
key generation algorithm.  Make sure you know what you are doing before
you undertake such changes.
</p>

<p>

Changes to pages/fileformat2.in.

16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
...
985
986
987
988
989
990
991

992
993
994
995
996
997
998
....
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012



1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
....
1053
1054
1055
1056
1057
1058
1059
1060


1061
1062
1063
1064




































































































































































































1065
1066
1067
1068
1069
1070
1071
<p>During a transaction, SQLite stores additional information 
in a second file called the "rollback journal", or if SQLite is in
[WAL mode], a write-ahead log file.
If the application or
host computer crashes before the transaction completes, then the rollback
journal or write-ahead log contains critical state information needed 
to restore the main database file to a consistent state.  When a rollback 
journal or write-ahead log contain information necessary for recovering 
the state of the database, they are called a "hot journal" or "hot WAL file".
Hot journals and WAL files are only a factor during error recovery
scenarios and so are uncommon, but they are part of the state of an SQLite
database and so cannot be ignored.  This document defines the format
of a rollback journal and the write-ahead log file, but the focus is
on the main database file.</p>

................................................................................
are unique.</p>

<p>^There is a one-to-one mapping between rows in a table and
entries in each index associated with that table.
^Corresponding rows int the index and table b-trees share the same rowid
value, and contain the same value for all indexed columns.</p>


<h3>2.5 Storage Of The SQL Database Schema</h3>

<p>^Page 1 of a database file is the root page of a table b-tree that
holds a special table named "sqlite_master" (or "sqlite_temp_master" in
the case of a TEMP database) which stores the complete
database schema.  ^(The structure of the sqlite_master table is as
if it had been created using the following SQL:</p>
................................................................................
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);
</pre></blockquote>)^

<p>^The sqlite_master table contains a row for each table, index, view,
and trigger in the database schema, except there is no entry for the
sqlite_master table itself.</p>




<p>^(The sqlite_master.type column will be one
of the following text strings:  'table', 'index', 'view', or 'trigger'
according to the type of object defined.  The 'table' string is used
for both ordinary and [virtual tables].)^</p>

</p>^(The sqlite_master.name column will hold the name of the object.)^
^(For indices that are automatically created by UNIQUE or PRIMARY KEY
constraints, the name is "sqlite_autoindex_TABLE_N" where TABLE is 
replaced by the name of the table that contains the constraint and N 
is an integer beginning
with 1 and increasing by one with each constraint seen.)^</p>

<p>The sqlite_master.tbl_name column holds the name of a table or view
that the object is associated with.  ^For a table or view, the
tbl_name column is a copy of the name column.  ^For an index, the tbl_name
is the name of the table that is indexed.  ^For a trigger, the tbl_name
column stores the name of the table or view that causes the trigger 
to fire.</p>
................................................................................
<li>^Leading spaces are removed.
<li>^All spaces following the first two keywords are converted into a single
space.
</ul>

<p>^(The text in the sqlite_master.sql column is a copy of the original
CREATE statement text that created the object, except normalized as
described above and as modified by subsequent [ALTER TABLE] statements.)^</p>



<p>^(For indices that are automatically created by UNIQUE or
PRIMARY KEY constraints, the sqlite_master.sql field is NULL.)^</p>






































































































































































































<tcl>hd_fragment rollbackjournal {rollback journal format}</tcl>
<h2>3.0 The Rollback Journal</h2>

<p>The rollback journal is a file associated with each SQLite database
file that hold information used to restore the database file to its initial
state during the course of a transaction.







|







 







>







 







|
|
|
>
>
>







|
|
|
|
|







 







|
>
>

<
<

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
...
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
....
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
....
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067


1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
<p>During a transaction, SQLite stores additional information 
in a second file called the "rollback journal", or if SQLite is in
[WAL mode], a write-ahead log file.
If the application or
host computer crashes before the transaction completes, then the rollback
journal or write-ahead log contains critical state information needed 
to restore the main database file to a consistent state.  When a rollback 
journal or write-ahead log contains information necessary for recovering 
the state of the database, they are called a "hot journal" or "hot WAL file".
Hot journals and WAL files are only a factor during error recovery
scenarios and so are uncommon, but they are part of the state of an SQLite
database and so cannot be ignored.  This document defines the format
of a rollback journal and the write-ahead log file, but the focus is
on the main database file.</p>

................................................................................
are unique.</p>

<p>^There is a one-to-one mapping between rows in a table and
entries in each index associated with that table.
^Corresponding rows int the index and table b-trees share the same rowid
value, and contain the same value for all indexed columns.</p>

<tcl>hd_fragment sqlite_master {sqlite_master} {sqlite_master table}</tcl>
<h3>2.5 Storage Of The SQL Database Schema</h3>

<p>^Page 1 of a database file is the root page of a table b-tree that
holds a special table named "sqlite_master" (or "sqlite_temp_master" in
the case of a TEMP database) which stores the complete
database schema.  ^(The structure of the sqlite_master table is as
if it had been created using the following SQL:</p>
................................................................................
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);
</pre></blockquote>)^

<p>^The sqlite_master table contains one row for each table, index, view,
and trigger (collectively "objects") in the database schema, except there
is no entry for the sqlite_master table itself.  ^The sqlite_master table
contains entries for [internal schema objects] in addition to application-
and programmer-defined objects.


<p>^(The sqlite_master.type column will be one
of the following text strings:  'table', 'index', 'view', or 'trigger'
according to the type of object defined.  The 'table' string is used
for both ordinary and [virtual tables].)^</p>

</p>^(The sqlite_master.name column will hold the name of the object.)^
^([UNIQUE] and [PRIMARY KEY] constraints on tables cause SQLite to create
[internal indices] with names of the form "sqlite_autoindex_TABLE_N"
where TABLE is replaced by the name of the table that contains the
constraint and N is an integer beginning with 1 and increasing by one
with each constraint seen in the table definition.)^</p>

<p>The sqlite_master.tbl_name column holds the name of a table or view
that the object is associated with.  ^For a table or view, the
tbl_name column is a copy of the name column.  ^For an index, the tbl_name
is the name of the table that is indexed.  ^For a trigger, the tbl_name
column stores the name of the table or view that causes the trigger 
to fire.</p>
................................................................................
<li>^Leading spaces are removed.
<li>^All spaces following the first two keywords are converted into a single
space.
</ul>

<p>^(The text in the sqlite_master.sql column is a copy of the original
CREATE statement text that created the object, except normalized as
described above and as modified by subsequent [ALTER TABLE] statements.)^
^(The sqlite_master.sql is NULL for the [internal indices] that are
automatically created by [UNIQUE] or [PRIMARY KEY] constraints.)^</p>




<tcl>hd_fragment intschema {internal schema objects} \
{internal schema object} {internal index} {internal indices} \
{internal table} {internal tables}</tcl>
<h4>2.5.1 Internal Schema Objects</h4>

<p>^In addition to the tables, indices, views, and triggers created by
the application and/or the developer using CREATE statements SQL, the
sqlite_master table may contain zero or more entries for 
<i>internal schema objects</i> that are created by SQLite for its 
own internal use.  ^The names of internal schema objects
always begin with "sqlite_" and any table, index, view, or trigger
whose name begins with "sqlite_" is an internal schema object.
^SQLite prohibits applications from creating objects whose names begin
with "sqlite_".  

<p>Internal schema objects used by SQLite may include the following:

<ul>
<li><p>Indices with names of the form "sqlite_autoindex_TABLE_N" that
       are used to implement [UNIQUE] and [PRIMARY KEY] constraints on
       ordinary tables.

<li><p>A table with the name "sqlite_sequence" that is used to keep track
       of the maximum historical [INTEGER PRIMARY KEY] for a table that
       using [AUTOINCREMENT].

<li><p>Tables with names of the form "sqlite_statN" where N is an integer.
       Such tables store database statistics gathered by the [ANALYZE]
       command and used by the query planner to help determine the best
       algorithm to use for each query.
</ul>

<p>Additional internal schema objects names, always beginning with "sqlite_",
may be added to the SQLite file format in future releases.

<tcl>hd_fragment seqtab {sqlite_sequence}</tcl>
<h4>2.5.2 The sqlite_sequence table</h4>

<p>^The sqlite_sequence table is an internal table used to help implement
[AUTOINCREMENT].  ^The sqlite_sequence table is created automatically
whenever any ordinary table with an AUTOINCREMENT integer primary
key is created.  ^Once created, the sqlite_sequence table exists in the
sqlite_master table forever; it cannot be dropped.
The schema for the sqlite_sequence table is:

<blockquote><pre>
CREATE TABLE sqlite_sequence(name,seq);
</pre></blockquote>

<p>^There is a single row in the sqlite_sequence table for each ordinary
table that uses AUTOINCREMENT.  The name of the table (as it appears in
sqlite_master.name) is in the sqlite_sequence.main field and the largest
[INTEGER PRIMARY KEY] ever used by that table is in the sqlite_sequence.seq
field.  ^New automatically generated integer primary keys for AUTOINCREMENT
tables are guaranteed to be larger than the sqlite_sequence.seq field for
that table.
^If the sqlite_sequence.seq field of an AUTOINCREMENT table is already at
the largest integer value (9223372036854775807) then attempts to add new
rows to that table with an automatically generated integer primary will fail
with an [SQLITE_FULL] error.
^The sqlite_sequence.seq field is automatically updated if required when
new entries are added to an AUTOINCREMENT table.  
^The sqlite_sequence row for an AUTOINCREMENT table is automatically deleted
when the table is dropped.
^If the sqlite_sequence row for an AUTOINCREMENT table does not exist when
the AUTOINCREMENT table is updated, then a new sqlite_sequence row is created.
^If the sqlite_sequence.seq value for an AUTOINCREMENT table is manually 
set to something other than an integer and there is a subsequent attempt to
insert the or update the AUTOINCREMENT table, then the behavior is undefined.

<p>^Application code is allowed to modify the sqlite_sequence table, to add
new rows, to delete rows, or to modify existing rows.  ^However, application
code cannot create the sqlite_sequence table if it does not already exist.
^Application code can delete all entries from the sqlite_sequence table,
but application code cannot drop the sqlite_sequence table.

<tcl>hd_fragment stat1tab {sqlite_stat1}</tcl>
<h4>2.5.3 The sqlite_stat1 table</h4>

<p>^The sqlite_stat1 is an internal table created by the [ANALYZE] command
and used to hold supplemental information about tables and indices that the
query planner can use to help it find better ways of performing queries.
^Applications can update, delete from, insert into or drop the sqlite_stat1
table, but may not create or alter the sqlite_stat1 table.
^The schema of the sqlite_stat1 table is as follows:

<blockquote><pre>
CREATE TABLE sqlite_stat1(tbl,idx,stat);
</pre></blockquote>

<p>There is normally one row per index, with the index identified by the
name in the sqlite_stat1.idx column.  The sqlite_stat1.tbl column is
the name of the table to which the index belongs.  In each such row, 
the sqlite_stat.stat column will be
a string consisting of a list of integers.  The first integer in this
list is the number of rows in the index and in the table.  The second
integer is the average number of rows in the index that have the same
value in the first column of the index.  The third integer is the average
number of rows in the index that have the same value for the first two
columns.  The N-th integer (for N>1) is the average number of rows in 
the index which have the same value for the first N-1 columns.  For
a K-column index, there will be K+1 integers in the stat column.  If
the index is unique, then the last integer will be 1.

<p>The list of integers in the stat column can optionally be followed
by the keyword "unordered".  The "unordered" keyword, if it is present,
must be separated from the last integer by a single space.  If the
"unordered" keyword is present, then the query planner assumes that
the index is unordered and will not use the index for a range query.

<p>If the sqlite_stat1.idx column is NULL, then the sqlite_stat1.stat
column contains a single integer which is the (estimated) number of
rows in the table identified by sqlite_stat1.tbl.

<tcl>hd_fragment stat2tab {sqlite_stat2}</tcl>
<h4>2.5.4 The sqlite_stat2 table</h4>

<p>The sqlite_stat2 is only created and is only used if SQLite is compiled
with SQLITE_ENABLE_STAT2 and if the SQLite version number is between
3.6.18 and 3.7.8.  The sqlite_stat2 table is neither read nor written by any
version of SQLite before 3.6.18 nor after 3.7.8.
The sqlite_stat2 table contains additional information
about the distribution of keys within an index.
The schema of the sqlite_stat2 table is as follows:

<blockquote><pre>
CREATE TABLE sqlite_stat2(tbl,idx,sampleno,sample);
</pre></blockquote>

<p>The sqlite_stat2.idx column and the sqlite_stat2.tbl column in each 
row of the sqlite_stat2 table identify an index described by that row.
There are usually 10 rows in the sqlite_stat2
table for each index.

<p>The sqlite_stat2 entries for an index that have sqlite_stat2.sampleno
between 0 and 9 inclusive are samples of the left-most key value in the
index taken at evenly spaced points along the index.
Let C be the number of rows in the index.
Then the sampled rows are given by

<blockquote>
     rownumber = (i*C*2 + C)/20
</blockquote>

<p>The variable i in the previous expression varies between 0 and 9.
Conceptually, the index space is divided into
10 uniform buckets and the samples are the middle row from each bucket.

<p>The format for sqlite_stat2 is recorded here for legacy reference.  
Recent versions of SQLite no longer support sqlite_stat2 and the
sqlite_stat2 table, it is exists, is simply ignored.

<tcl>hd_fragment stat3tab {sqlite_stat3}</tcl>
<h4>2.5.5 The sqlite_stat3 table</h4>

<p>The sqlite_stat3 is only created and is only used if SQLite is compiled
with [SQLITE_ENABLE_STAT3] and if the SQLite version number is
3.7.9 or greater.  The sqlite_stat3 table is neither read nor written by any
version of SQLite before 3.6.9.
The sqlite_stat3 table contains additional information
about the distribution of keys within an index, information that the
query planner can use to devise better and faster query algorithms.
The schema of the sqlite_stat3 table is as follows:

<blockquote><pre>
CREATE TABLE sqlite_stat3(tbl,idx,nEq,nLt,nDLt,sample);
</pre></blockquote>

<p>There are usually multiple entries in the sqlite_stat3 table for each index.
The sqlite_stat3.sample column holds the value of the left-most field of an
index identified by the sqlite_stat3.idx and the sqlite_stat3.tbl columns.
If the sqlite_stat3.idx and sqlite_stat3.tbl
columns hold the same value, then that row contains a sample from
the [INTEGER PRIMARY KEY] of the table.
The sqlite_stat3.nEq column holds the approximate
number of entries in the index whose left-most column exactly matches
the sample.  
The sqlite_stat3.nLt holds the approximate number of entries in the
index whose left-most column is less than the sample.
The sqlite_stat3.nDLt column holds the approximate
number of distinct left-most entries in the index that are less than
the sample.

<p>Future versions of SQLite might change to store a string containing
multiple integers values in the sqlite_stat3.nDLt column, a string in which
the first integer will be the number of prior index entries that are
distinct in the left-most column, the second integer is the number of
prior index entries that are distinct in the first two columns, the
third integer is the number of prior index entries that are distinct
in the first three columns, and so forth  With such an extension, the
nDLt field will become similar in function to the sqlite_stat1.stat field.

<p>There can be an arbitrary number of sqlite_stat3 entries per index.
The [ANALYZE] command will typically generate sqlite_stat3 tables
that contain between 10 and 40 samples that are distributed across
the key space and with large nEq values.

<tcl>hd_fragment rollbackjournal {rollback journal format}</tcl>
<h2>3.0 The Rollback Journal</h2>

<p>The rollback journal is a file associated with each SQLite database
file that hold information used to restore the database file to its initial
state during the course of a transaction.

Changes to pages/lang.in.

186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205



206
207
208

209
210
211

212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
...
603
604
605
606
607
608
609

610
611
612
613
614
615
616
...
776
777
778
779
780
781
782

783
784
785
786
787
788
789
...
806
807
808
809
810
811
812

813
814
815
816
817
818
819
...
838
839
840
841
842
843
844

845
846
847
848
849
850
851
Section {ANALYZE} analyze ANALYZE

BubbleDiagram analyze-stmt 1
</tcl>

<p> ^The ANALYZE command gathers statistics about tables and
indices and stores the collected information
in a special tables in the database where the query optimizer can use
them to help make better query planning choices.
^If no arguments are given, all attached databases are
analyzed.  ^If a database name is given as the argument, then all tables
and indices in that one database are analyzed.  
^If the argument is a table name, then only that table and the
indices associated with that table are analyzed.  ^If the argument
is an index name, then only that one index is analyzed.</p>

<p> ^The default implementation stores all statistics in a single
table named <b>sqlite_stat1</b>.  ^If SQLite is compiled with the
[SQLITE_ENABLE_STAT3] option, then additional histogram data is
collected and stored in <b>sqlite_stat3</b>.



Future enhancements may create
additional tables with the same name pattern except with the "1"
or "3" changed to larger digits.</p>


<p> ^The [ALTER TABLE] command does
not work on the <b>sqlite_stat1</b> or <b>sqlite_stat3</b> tables,

but all the content of those tables can be queried using [SELECT]
and can be deleted, augmented, or modified using the [DELETE],
[INSERT], and [UPDATE] commands.
^(The [DROP TABLE] command works on <b>sqlite_stat1</b> and
<b>sqlite_stat3</b> as of SQLite version 3.7.9.)^
Appropriate care should be used when changing the content of the statistics
tables as invalid content can cause SQLite to select inefficient
query plans.  Generally speaking, one should not modify the content of
the statistics tables by any mechanism other than invoking the
ANALYZE command.</p>

<p> ^Statistics gathered by ANALYZE are <u>not</u> automatically updated as
the content of the database changes.  If the content of the database
changes significantly, or if the database schema changes, then one should
consider rerunning the ANALYZE command in order to update the statistics.</p>

<p> The query planner might not notice manual changes to the
<b>sqlite_stat1</b> and/or <b>sqlite3_stat2</b> tables.  ^An application
can force the query planner to reread the statistics tables by running
<b>ANALYZE sqlite_master</b>. </p>

<tcl>
##############################################################################
Section {ATTACH DATABASE} attach *ATTACH

................................................................................
the built-in BINARY collating sequence is used.</p>

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table.  ^(The number of columns in an index is 
limited to the value set by
[sqlite3_limit]([SQLITE_LIMIT_COLUMN],...).)^</p>


<p>^If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  ^Any attempt to insert a duplicate entry
will result in an error.  ^For the purposes of unique indices, all NULL values
are considered to different from all other NULL values and are thus unique.
This is one of the two possible interpretations of the SQL-92 standard
(the language in the standard is ambiguous) and is the interpretation
followed by PostgreSQL, MySQL, Firebird, and Oracle.  Informix and
................................................................................
compile-time parameter. ^A single row of a table cannot store more than
[SQLITE_MAX_LENGTH] bytes of data. ^Both of these limits can be lowered at
runtime using the [sqlite3_limit()] C/C++ interface.</p>

<tcl>hd_fragment {constraints} {constraints}</tcl>
<h3>SQL Data Constraints</h3>


<p>^Each table in SQLite may have at most one <b>PRIMARY KEY</b>. ^If the
  keywords PRIMARY KEY are added to a column definition, then the primary key
  for the table consists of that single column. ^Or, if a PRIMARY KEY clause 
  is specified as a [table-constraint], then the primary key of the table
  consists of the list of columns specified as part of the PRIMARY KEY clause.
  ^If there is more than one PRIMARY KEY clause in a single CREATE TABLE
  statement, it is an error.
................................................................................
  conform to the standard (and we might do so in the future), but by the time
  the oversight was discovered, SQLite was in such wide use that we feared
  breaking legacy code if we fixed the problem.  So for now we have chosen to
  continue allowing NULLs in PRIMARY KEY columns.  Developers should be
  aware, however, that we may change SQLite to conform to the SQL standard in
  future and should design new programs accordingly.


<p>^A <b>UNIQUE</b> constraint is similar to a PRIMARY KEY constraint, except
  that a single table may have any number of UNIQUE constraints. ^For each
  UNIQUE constraint on the table, each row must feature a unique combination
  of values in the columns identified by the UNIQUE constraint. ^As with
  PRIMARY KEY constraints, for the purposes of UNIQUE constraints NULL values
  are considered distinct from all other values (including other NULLs).
  ^If an [INSERT] or [UPDATE] statement attempts to modify the table content so
................................................................................
  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.
  Attempting to set the column value to NULL when inserting a new row or
  updating an existing one causes a constraint violation.)^

<p>Exactly how a constraint violation is dealt with is determined by the







|









|

|
>
>
>

|
<
>


<
>



|
|












|







 







>







 







>







 







>







 







>







186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210

211
212
213

214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
...
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
...
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
...
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
...
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
Section {ANALYZE} analyze ANALYZE

BubbleDiagram analyze-stmt 1
</tcl>

<p> ^The ANALYZE command gathers statistics about tables and
indices and stores the collected information
in a [internal tables] in the database where the query optimizer can use
them to help make better query planning choices.
^If no arguments are given, all attached databases are
analyzed.  ^If a database name is given as the argument, then all tables
and indices in that one database are analyzed.  
^If the argument is a table name, then only that table and the
indices associated with that table are analyzed.  ^If the argument
is an index name, then only that one index is analyzed.</p>

<p> ^The default implementation stores all statistics in a single
table named "[sqlite_stat1]".  ^If SQLite is compiled with the
[SQLITE_ENABLE_STAT3] option, then additional histogram data is
collected and stored in [sqlite_stat3].
Older versions of SQLite would make use of the [sqlite_stat2] table
when compiled with [SQLITE_ENABLE_STAT2] but all recent versions of
SQLite ignore the sqlite_stat2 table.
Future enhancements may create
additional [internal tables] with the same name pattern except with

final digit larger than "3".</p>

<p> ^The [ALTER TABLE] command does

not work on the sqlite_stat1 or sqlite_stat3 tables,
but all the content of those tables can be queried using [SELECT]
and can be deleted, augmented, or modified using the [DELETE],
[INSERT], and [UPDATE] commands.
^(The [DROP TABLE] command works on sqlite_stat1 and
sqlite_stat3 as of SQLite version 3.7.9.)^
Appropriate care should be used when changing the content of the statistics
tables as invalid content can cause SQLite to select inefficient
query plans.  Generally speaking, one should not modify the content of
the statistics tables by any mechanism other than invoking the
ANALYZE command.</p>

<p> ^Statistics gathered by ANALYZE are <u>not</u> automatically updated as
the content of the database changes.  If the content of the database
changes significantly, or if the database schema changes, then one should
consider rerunning the ANALYZE command in order to update the statistics.</p>

<p> The query planner might not notice manual changes to the
sqlite_stat1 and/or sqlite_stat3 tables.  ^An application
can force the query planner to reread the statistics tables by running
<b>ANALYZE sqlite_master</b>. </p>

<tcl>
##############################################################################
Section {ATTACH DATABASE} attach *ATTACH

................................................................................
the built-in BINARY collating sequence is used.</p>

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table.  ^(The number of columns in an index is 
limited to the value set by
[sqlite3_limit]([SQLITE_LIMIT_COLUMN],...).)^</p>

<tcl>hd_fragment uniqueidx {unique index}</tcl>
<p>^If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  ^Any attempt to insert a duplicate entry
will result in an error.  ^For the purposes of unique indices, all NULL values
are considered to different from all other NULL values and are thus unique.
This is one of the two possible interpretations of the SQL-92 standard
(the language in the standard is ambiguous) and is the interpretation
followed by PostgreSQL, MySQL, Firebird, and Oracle.  Informix and
................................................................................
compile-time parameter. ^A single row of a table cannot store more than
[SQLITE_MAX_LENGTH] bytes of data. ^Both of these limits can be lowered at
runtime using the [sqlite3_limit()] C/C++ interface.</p>

<tcl>hd_fragment {constraints} {constraints}</tcl>
<h3>SQL Data Constraints</h3>

<tcl>hd_fragment primkeyconst {PRIMARY KEY} {PRIMARY KEY constraint}</tcl>
<p>^Each table in SQLite may have at most one <b>PRIMARY KEY</b>. ^If the
  keywords PRIMARY KEY are added to a column definition, then the primary key
  for the table consists of that single column. ^Or, if a PRIMARY KEY clause 
  is specified as a [table-constraint], then the primary key of the table
  consists of the list of columns specified as part of the PRIMARY KEY clause.
  ^If there is more than one PRIMARY KEY clause in a single CREATE TABLE
  statement, it is an error.
................................................................................
  conform to the standard (and we might do so in the future), but by the time
  the oversight was discovered, SQLite was in such wide use that we feared
  breaking legacy code if we fixed the problem.  So for now we have chosen to
  continue allowing NULLs in PRIMARY KEY columns.  Developers should be
  aware, however, that we may change SQLite to conform to the SQL standard in
  future and should design new programs accordingly.

<tcl>hd_fragment uniqueconst {UNIQUE} {unique constraint}</tcl>
<p>^A <b>UNIQUE</b> constraint is similar to a PRIMARY KEY constraint, except
  that a single table may have any number of UNIQUE constraints. ^For each
  UNIQUE constraint on the table, each row must feature a unique combination
  of values in the columns identified by the UNIQUE constraint. ^As with
  PRIMARY KEY constraints, for the purposes of UNIQUE constraints NULL values
  are considered distinct from all other values (including other NULLs).
  ^If an [INSERT] or [UPDATE] statement attempts to modify the table content so
................................................................................
  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.

<tcl>hd_fragment {notnullconst} {NOT NULL} {NOT NULL constraint}</tcl>
<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.
  Attempting to set the column value to NULL when inserting a new row or
  updating an existing one causes a constraint violation.)^

<p>Exactly how a constraint violation is dealt with is determined by the