Documentation Source Text

Check-in [be570dc031]
Login

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

Overview
Comment:Updates to the CREATE TABLE documentation.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: be570dc0313947a050fe49535b0c9bea66fbac4b
User & Date: drh 2008-04-27 15:10:15
Context
2008-04-27
17:51
Updates to the SQL language documentation with many added hyperlink targets. check-in: f060763119 user: drh tags: trunk
15:10
Updates to the CREATE TABLE documentation. check-in: be570dc031 user: drh tags: trunk
2008-04-25
12:31
Update the atomiccommit document to reflect the latest PRAGMA journal_mode changes. check-in: ee0e82d0aa user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
...
551
552
553
554
555
556
557
558
559
560
561
562

563
564
565
566
567
568
569
570
571
572
573
574
575
576
<p>According to the SQL standard, PRIMARY KEY should imply NOT NULL.
Unfortunately, due to a long-standing coding oversight, this is not 
the case in SQLite.  SQLite allows NULL values
in a PRIMARY KEY column.  We could change SQLite to 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 contain 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>

<p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"
and "TABLE" then the table that is created is only visible
within that same database connection
................................................................................
default algorithm specified in the CREATE TABLE statement.
See the section titled
<a href="lang_conflict.html">ON CONFLICT</a> for additional information.</p>

<p>CHECK constraints are supported as of version 3.3.0.  Prior
to version 3.3.0, CHECK constraints were parsed but not enforced.</p>

<p>There are no arbitrary limits on the number
of columns or on the number of constraints in a table.
The total amount of data in a single row is limited to about
1 megabytes in version 2.8.  In version 3.0 there is no arbitrary
limit on the amount of data in a row.</p>



<p>The CREATE TABLE AS form defines the table to be
the result set of a query.  The names of the table columns are
the names of the columns in the result.</p>

<p>The exact text
of each CREATE TABLE statement is stored in the <b>sqlite_master</b>
table.  Every time the database is opened, all CREATE TABLE statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the table layout.
If the original command was a CREATE TABLE AS then then an equivalent
CREATE TABLE statement is synthesized and store in <b>sqlite_master</b>
in place of the original command.







|







 







|
|
|
|
|
>






|







518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
...
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
<p>According to the SQL standard, PRIMARY KEY should imply NOT NULL.
Unfortunately, due to a long-standing coding oversight, this is not 
the case in SQLite.  SQLite allows NULL values
in a PRIMARY KEY column.  We could change SQLite to 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>

<p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"
and "TABLE" then the table that is created is only visible
within that same database connection
................................................................................
default algorithm specified in the CREATE TABLE statement.
See the section titled
<a href="lang_conflict.html">ON CONFLICT</a> for additional information.</p>

<p>CHECK constraints are supported as of version 3.3.0.  Prior
to version 3.3.0, CHECK constraints were parsed but not enforced.</p>

<p>The number of columns in a table is limited by the
[SQLITE_MAX_COLUMN] 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>


<p>The CREATE TABLE AS form defines the table to be
the result set of a query.  The names of the table columns are
the names of the columns in the result.</p>

<p>The text
of each CREATE TABLE statement is stored in the <b>sqlite_master</b>
table.  Every time the database is opened, all CREATE TABLE statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the table layout.
If the original command was a CREATE TABLE AS then then an equivalent
CREATE TABLE statement is synthesized and store in <b>sqlite_master</b>
in place of the original command.

Changes to pages/limits.in.

39
40
41
42
43
44
45
46






47
48
49
50
51
52
53
54
55
56
57
58
59
60
..
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
...
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
...
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
...
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183

184
185
186
187
188
189
190
...
193
194
195
196
197
198
199
200

201
202
203
204
205
206
207
...
212
213
214
215
216
217
218
219

220
221
222
223
224
225
226
...
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
...
267
268
269
270
271
272
273
274

275
276
277
278
279
280
281
an application might want to recompile SQLite with much lower
limits to avoid excess resource utilization in the event of
bug in higher-level SQL statement generators or to help thwart 
attackers who inject malicious SQL statements.
</p>

<tcl>
proc limititem {title text} {






  hd_resolve "<li><p><b>$title</b></p>\n$text</li>"
}
hd_puts {
<ol>
}

limititem {Maximum length of a string or BLOB} {
<p>
The maximum number of bytes in a string or BLOB in SQLite is defined
by the preprocessor macro SQLITE_MAX_LENGTH.  The default value
of this macro is 1 billion (1 thousand million or 1,000,000,000).
You can raise or lower this value at compile-time using a command-line 
option like this:
</p>
................................................................................
During part of SQLite's INSERT and SELECT processing, the complete
content of each row in the database is encoded as a single BLOB.
So the SQLITE_MAX_LENGTH parameter also determines the maximum
number of bytes in a row.
</p>
}

limititem {Maximum Number Of Columns} {
<p>
The SQLITE_MAX_COLUMN compile-time parameter is used to set an upper
bound on:
</p>

<ul>
<li>The number of columns in a table</li>
................................................................................
that are O(N&sup2;) where N is the number of columns.  
So if you redefine SQLITE_MAX_COLUMN to be a
really huge number and you generate SQL that uses a large number of
columns, you may find that [sqlite3_prepare_v2()]
runs slowly.
}

limititem {Maximum Length Of An SQL Statement} {
<p>
The maximum number of bytes in the text of an SQL statement is 
limited to SQLITE_MAX_SQL_LENGTH which defaults to 1000000.  You
can redefine this limit to be as large as the smaller of SQLITE_MAX_LENGTH
and 1073741824.  
</p>

................................................................................
to bind your large string values to the SQL statement.  The use of binding
obviates the need to escape quote characters in the string, reducing the
risk of SQL injection attacks.  It is also runs faster since the large
string does not need to be parsed or copied as much.
</p>
}

limititem {Maximum Number Of Tables In A Join} {
<p>
SQLite does not support joins containing more than 64 tables.
This limit arises from the fact that the SQLite code generator
uses bitmaps with one bit per join-table in the query optimizer.
</p>
}

limititem {Maximum Depth Of An Expression Tree} {
<p>
SQLite parses expressions into a tree for processing.  During
code generation, SQLite walks this tree recursively.  The depth
of expression trees is therefore limited in order to avoid
using too much stack space.
</p>

................................................................................
<p>
The SQLITE_MAX_EXPR_DEPTH parameter determines the maximum expression
tree depth.  If the value is 0, then no limit is enforced.  The
current implementation has a default value of 1000.
</p>
}

limititem {Maximum Number Of Arguments On A Function} {
<p>
The SQLITE_MAX_FUNCTION_ARG parameter determines the maximum number
of parameters that can be passed to an SQL function.  The default value
of this limit is 100.  We know of no 
technical reason why SQLite would not work with functions that have 
millions of parameters.  However, we suspect that anybody who tries
to invoke a function with millions of parameters is really
trying to find security exploits in systems that use SQLite, 
not do useful work, 
and so for that reason we have set this parameter relatively low.
}

limititem {Maximum Number Of Terms In A Compound SELECT Statement} {

<p>
A compound SELECT statement is two or more SELECT statements connected
by operators UNION, UNION ALL, EXCEPT, or INTERSECT.  We call each
individual SELECT statement within a compound SELECT a "term".
</p>

<p>
................................................................................
therefore limit the number of terms in a compound SELECT.  The maximum
number of terms is SQLITE_MAX_COMPOUND_SELECT which defaults to 500.
We think this is a generous allotment since in practice we almost
never see the number of terms in a compound select exceed single digits.
</p>
}

limititem {Maximum Length Of A LIKE Or GLOB Pattern} {

<p>
The pattern matching algorithm used in the default LIKE and GLOB
implementation of SQLite can exhibit O(N&sup2) performance (where
N is the number of characters in the pattern) for certain pathological
cases.  To avoid denial-of-service attacks from miscreants who are able
to specify their own LIKE or GLOB patterns, the length of the LIKE
or GLOB pattern is limited to SQLITE_MAX_LIKE_PATTERN_LENGTH bytes.
................................................................................
since most useful LIKE or GLOB patterns are at most a few dozen bytes
in length, paranoid application developers may want to reduce this
parameter to something in the range of a few hundred if they know that
external users are able to generate arbitrary patterns.
</p>
}

limititem {Maximum Number Of Host Parameters In A Single SQL Statement} {

<p>
A host parameter is a place-holder in an SQL statement that is filled
in using one of the
<a href="c3ref/bind_blob.html">sqlite3_bind_XXXX()</a> interfaces.
Many SQL programmers are familiar with using a question mark ("?") as a
host parameter.  SQLite also supports named host parameters prefaced
by ":", "$", or "@" and numbered host parameters of the form "?123".
................................................................................

<p>
The maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER.
This setting defaults to 999.
</p>
}

limititem {Maximum Number Of Attached Databases} {
<p>
The <a href="lang_attach.html">ATTACH</a> statement is an SQLite extension
that allows two or more databases to be associated to the same database
connection and to operate as if they were a single database.  The number
of simulataneously attached databases is limited to SQLITE_MAX_ATTACHED
which is set to 10 by default.
The code generator in SQLite uses bitmaps
to keep track of attached databases.  That means that the number of
attached databases cannot be increased above 30 on a 32-bit machine
or 62 on a 64-bit machine.
}

limititem {Maximum Database Page Size} {
<p>
An SQLite database file is organized as pages.  The size of each
page is a power of 2 between 512 and SQLITE_MAX_PAGE_SIZE.
The default value for SQLITE_MAX_PAGE_SIZE is 32768.  The current
implementation will not support a larger value.
</p>

................................................................................
maximum page size on embedded devices with limited stack memory.  But
more recent versions of SQLite put these large structures on the
heap, not on the stack, so reducing the maximum page size is no
longer necessary on embedded devices.
</p>
}

limititem {Maximum Number Of Pages In A Database File} {

<p>
SQLite is able to limit the size of a database file to prevent
the database file from growing too large and consuming too much
disk or flash space.
The SQLITE_MAX_PAGE_COUNT parameter, which is normally set to
1073741823, is the maximum number of pages allowed in a single
database file.  An attempt to insert new data that would cause







|
>
>
>
>
>
>






|







 







|







 







|







 







|







|







 







|












|
>







 







|
>







 







|
>







 







|












|







 







|
>







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
..
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
...
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
...
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
...
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
...
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
...
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
...
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
...
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
an application might want to recompile SQLite with much lower
limits to avoid excess resource utilization in the event of
bug in higher-level SQL statement generators or to help thwart 
attackers who inject malicious SQL statements.
</p>

<tcl>
proc limititem {title keywords text} {
  if {[llength $keywords]} {
    set frag [string tolower [lindex $keywords 0]]
    regsub {sqlite_} $frag {} frag
    hd_fragment $frag
    foreach kw $keywords {hd_keywords $kw}
  }
  hd_resolve "<li><p><b>$title</b></p>\n$text</li>"
}
hd_puts {
<ol>
}

limititem {Maximum length of a string or BLOB} SQLITE_MAX_LENGTH {
<p>
The maximum number of bytes in a string or BLOB in SQLite is defined
by the preprocessor macro SQLITE_MAX_LENGTH.  The default value
of this macro is 1 billion (1 thousand million or 1,000,000,000).
You can raise or lower this value at compile-time using a command-line 
option like this:
</p>
................................................................................
During part of SQLite's INSERT and SELECT processing, the complete
content of each row in the database is encoded as a single BLOB.
So the SQLITE_MAX_LENGTH parameter also determines the maximum
number of bytes in a row.
</p>
}

limititem {Maximum Number Of Columns} SQLITE_MAX_COLUMN {
<p>
The SQLITE_MAX_COLUMN compile-time parameter is used to set an upper
bound on:
</p>

<ul>
<li>The number of columns in a table</li>
................................................................................
that are O(N&sup2;) where N is the number of columns.  
So if you redefine SQLITE_MAX_COLUMN to be a
really huge number and you generate SQL that uses a large number of
columns, you may find that [sqlite3_prepare_v2()]
runs slowly.
}

limititem {Maximum Length Of An SQL Statement} SQLITE_MAX_SQL_LENGTH {
<p>
The maximum number of bytes in the text of an SQL statement is 
limited to SQLITE_MAX_SQL_LENGTH which defaults to 1000000.  You
can redefine this limit to be as large as the smaller of SQLITE_MAX_LENGTH
and 1073741824.  
</p>

................................................................................
to bind your large string values to the SQL statement.  The use of binding
obviates the need to escape quote characters in the string, reducing the
risk of SQL injection attacks.  It is also runs faster since the large
string does not need to be parsed or copied as much.
</p>
}

limititem {Maximum Number Of Tables In A Join} {} {
<p>
SQLite does not support joins containing more than 64 tables.
This limit arises from the fact that the SQLite code generator
uses bitmaps with one bit per join-table in the query optimizer.
</p>
}

limititem {Maximum Depth Of An Expression Tree} SQLITE_MAX_EXPR_DEPTH {
<p>
SQLite parses expressions into a tree for processing.  During
code generation, SQLite walks this tree recursively.  The depth
of expression trees is therefore limited in order to avoid
using too much stack space.
</p>

................................................................................
<p>
The SQLITE_MAX_EXPR_DEPTH parameter determines the maximum expression
tree depth.  If the value is 0, then no limit is enforced.  The
current implementation has a default value of 1000.
</p>
}

limititem {Maximum Number Of Arguments On A Function} SQLITE_MAX_FUNCTION_ARG {
<p>
The SQLITE_MAX_FUNCTION_ARG parameter determines the maximum number
of parameters that can be passed to an SQL function.  The default value
of this limit is 100.  We know of no 
technical reason why SQLite would not work with functions that have 
millions of parameters.  However, we suspect that anybody who tries
to invoke a function with millions of parameters is really
trying to find security exploits in systems that use SQLite, 
not do useful work, 
and so for that reason we have set this parameter relatively low.
}

limititem {Maximum Number Of Terms In A Compound SELECT Statement} \
          SQLITE_MAX_COMPOUND_SELECT {
<p>
A compound SELECT statement is two or more SELECT statements connected
by operators UNION, UNION ALL, EXCEPT, or INTERSECT.  We call each
individual SELECT statement within a compound SELECT a "term".
</p>

<p>
................................................................................
therefore limit the number of terms in a compound SELECT.  The maximum
number of terms is SQLITE_MAX_COMPOUND_SELECT which defaults to 500.
We think this is a generous allotment since in practice we almost
never see the number of terms in a compound select exceed single digits.
</p>
}

limititem {Maximum Length Of A LIKE Or GLOB Pattern} \
          SQLITE_MAX_LIKE_PATTERN_LENGTH {
<p>
The pattern matching algorithm used in the default LIKE and GLOB
implementation of SQLite can exhibit O(N&sup2) performance (where
N is the number of characters in the pattern) for certain pathological
cases.  To avoid denial-of-service attacks from miscreants who are able
to specify their own LIKE or GLOB patterns, the length of the LIKE
or GLOB pattern is limited to SQLITE_MAX_LIKE_PATTERN_LENGTH bytes.
................................................................................
since most useful LIKE or GLOB patterns are at most a few dozen bytes
in length, paranoid application developers may want to reduce this
parameter to something in the range of a few hundred if they know that
external users are able to generate arbitrary patterns.
</p>
}

limititem {Maximum Number Of Host Parameters In A Single SQL Statement} \
          SQLITE_MAX_VARIABLE_NUMBER {
<p>
A host parameter is a place-holder in an SQL statement that is filled
in using one of the
<a href="c3ref/bind_blob.html">sqlite3_bind_XXXX()</a> interfaces.
Many SQL programmers are familiar with using a question mark ("?") as a
host parameter.  SQLite also supports named host parameters prefaced
by ":", "$", or "@" and numbered host parameters of the form "?123".
................................................................................

<p>
The maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER.
This setting defaults to 999.
</p>
}

limititem {Maximum Number Of Attached Databases} SQLITE_MAX_ATTACHED {
<p>
The <a href="lang_attach.html">ATTACH</a> statement is an SQLite extension
that allows two or more databases to be associated to the same database
connection and to operate as if they were a single database.  The number
of simulataneously attached databases is limited to SQLITE_MAX_ATTACHED
which is set to 10 by default.
The code generator in SQLite uses bitmaps
to keep track of attached databases.  That means that the number of
attached databases cannot be increased above 30 on a 32-bit machine
or 62 on a 64-bit machine.
}

limititem {Maximum Database Page Size} SQLITE_MAX_PAGE_SIZE {
<p>
An SQLite database file is organized as pages.  The size of each
page is a power of 2 between 512 and SQLITE_MAX_PAGE_SIZE.
The default value for SQLITE_MAX_PAGE_SIZE is 32768.  The current
implementation will not support a larger value.
</p>

................................................................................
maximum page size on embedded devices with limited stack memory.  But
more recent versions of SQLite put these large structures on the
heap, not on the stack, so reducing the maximum page size is no
longer necessary on embedded devices.
</p>
}

limititem {Maximum Number Of Pages In A Database File} \
          SQLITE_MAX_PAGE_COUNT {
<p>
SQLite is able to limit the size of a database file to prevent
the database file from growing too large and consuming too much
disk or flash space.
The SQLITE_MAX_PAGE_COUNT parameter, which is normally set to
1073741823, is the maximum number of pages allowed in a single
database file.  An attempt to insert new data that would cause