Debian Codegraph @

www/faq.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>SQLite Frequently Asked Questions</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118
119
120 <h2>Frequently Asked Questions</h2><ol class=nounderline><li value='1'><a href="#q1">How do I create an AUTOINCREMENT field?</a></li><li value='2'><a href="#q2">What datatypes does SQLite support?</a></li><li value='3'><a href="#q3">SQLite lets me insert a string into a database column of type integer!</a></li><li value='4'><a href="#q4">Why doesn't SQLite allow me to use '0' and '0.0' as the primary
121 key on two different rows of the same table?</a></li><li value='5'><a href="#q5">Can multiple applications or multiple instances of the same
122 application access a single database file at the same time?</a></li><li value='6'><a href="#q6">Is SQLite threadsafe?</a></li><li value='7'><a href="#q7">How do I list all tables/indices contained in an SQLite database</a></li><li value='8'><a href="#q8">Are there any known size limits to SQLite databases?</a></li><li value='9'><a href="#q9">What is the maximum size of a VARCHAR in SQLite?</a></li><li value='10'><a href="#q10">Does SQLite support a BLOB type?</a></li><li value='11'><a href="#q11">How do I add, delete or rename columns from an existing table in SQLite?</a></li><li value='12'><a href="#q12">I deleted a lot of data but the database file did not get any
123 smaller. Is this a bug?</a></li><li value='13'><a href="#q13">Can I use SQLite in my commercial product without paying royalties?</a></li><li value='14'><a href="#q14">How do I use a string literal that contains an embedded single-quote (')
124 character?</a></li><li value='15'><a href="#q15">What is an SQLITE_SCHEMA error, and why am I getting one?</a></li><li value='17'><a href="#q17">I get some compiler warnings when I compile SQLite.
125 Isn't this a problem? Doesn't it indicate poor code quality?</a></li><li value='18'><a href="#q18">Case-insensitive matching of Unicode characters does not work.</a></li><li value='19'><a href="#q19">INSERT is really slow - I can only do few dozen INSERTs per second</a></li><li value='20'><a href="#q20">I accidentally deleted some important information from my SQLite database.
126 How can I recover it?</a></li><li value='21'><a href="#q21">What is an SQLITE_CORRUPT error? What does it mean for the database
127 to be "malformed"? Why am I getting this error?</a></li><li value='22'><a href="#q22">Does SQLite support foreign keys?</a></li><li value='23'><a href="#q23">I get a compiler error if I use the SQLITE_OMIT_...
128 compile-time options when building SQLite.</a></li><li value='24'><a href="#q24">My WHERE clause expression <tt>column1="column1"</tt> does not work.
129 It causes every row of the table to be returned, not just the rows
130 where column1 has the value "column1".</a></li><li value='25'><a href="#q25">How are the syntax diagrams (a.k.a. "railroad" diagrams) for
131 SQLite generated?</a></li><li value='26'><a href="#q26">The SQL standard requires that a UNIQUE constraint be enforced even if
132 one or more of the columns in the constraint are NULL, but SQLite does
133 not do this. Isn't that a bug?</a></li><li value='27'><a href="#q27">What is the Export Control Classification Number (ECCN) for SQLite?</a></li><li value='28'><a href="#q28">My query does not return the column name that I expect. Is this a bug?</a></li><li value='29'><a href="#q29">Where did my database go? (Or: How did my database become empty?)</a></li></ol><a name="q1"></a>
134 <p><b>(1) How do I create an AUTOINCREMENT field?</b></p>
135 <blockquote><p>Short answer: A column declared <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> will
136 autoincrement.</p>
137
138 <p>Longer answer:
139 If you declare a column of a table to be <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>, then
140 whenever you insert a NULL
141 into that column of the table, the NULL is automatically converted
142 into an integer which is one greater than the largest value of that
143 column over all other rows in the table, or 1 if the table is empty.
144 Or, if the largest existing integer key 9223372036854775807 is in use then an
145 unused key value is chosen at random.
146 For example, suppose you have a table like this:
147 <blockquote><pre>
148 CREATE TABLE t1(
149 a INTEGER PRIMARY KEY,
150 b INTEGER
151 );
152 </pre></blockquote>
153 <p>With this table, the statement</p>
154 <blockquote><pre>
155 INSERT INTO t1 VALUES(NULL,123);
156 </pre></blockquote>
157 <p>is logically equivalent to saying:</p>
158 <blockquote><pre>
159 INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);
160 </pre></blockquote>
161
162 <p>There is a function named
163 <a href="c3ref/last_insert_rowid.html">sqlite3_last_insert_rowid()</a> which will return the integer key
164 for the most recent insert operation.</p>
165
166 <p>Note that the integer key is one greater than the largest
167 key that was in the table just prior to the insert. The new key
168 will be unique over all keys currently in the table, but it might
169 overlap with keys that have been previously deleted from the
170 table. To create keys that are unique over the lifetime of the
171 table, add the <a href="autoinc.html">AUTOINCREMENT</a> keyword to the <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>
172 declaration. Then the key chosen will be one more than the
173 largest key that has ever existed in that table. If the largest
174 possible key has previously existed in that table, then the <a href="lang_insert.html">INSERT</a>
175 will fail with an <a href="rescode.html#full">SQLITE_FULL</a> error code.</p></blockquote></li>
176 <a name="q2"></a>
177 <p><b>(2) What datatypes does SQLite support?</b></p>
178 <blockquote>SQLite uses <a href="datatype3.html">dynamic typing</a>. Content can be stored as INTEGER,
179 REAL, TEXT, BLOB, or as NULL.</blockquote></li>
180 <a name="q3"></a>
181 <p><b>(3) SQLite lets me insert a string into a database column of type integer!</b></p>
182 <blockquote><p>This is a feature, not a bug. SQLite uses <a href="datatype3.html">dynamic typing</a>.
183 It does not enforce data type constraints. Data of any type can
184 (usually) be inserted into any column. You can put arbitrary length
185 strings into integer columns, floating point numbers in boolean columns,
186 or dates in character columns. The <a href="datatype3.html">datatype</a> you assign to a column in the
187 CREATE TABLE command does not restrict what data can be put into
188 that column. Every column is able to hold
189 an arbitrary length string. (There is one exception: Columns of
190 type <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> may only hold a 64-bit signed integer.
191 An error will result
192 if you try to put anything other than an integer into an
193 <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> column.)</p>
194
195 <p>But SQLite does use the declared type of a column as a hint
196 that you prefer values in that format. So, for example, if a
197 column is of type INTEGER and you try to insert a string into
198 that column, SQLite will attempt to convert the string into an
199 integer. If it can, it inserts the integer instead. If not,
200 it inserts the string. This feature is called <a href="datatype3.html#affinity">type affinity</a>.
201 </p></blockquote></li>
202 <a name="q4"></a>
203 <p><b>(4) Why doesn't SQLite allow me to use '0' and '0.0' as the primary
204 key on two different rows of the same table?</b></p>
205 <blockquote><p>This problem occurs when your primary key is a numeric type. Change the
206 <a href="datatype3.html">datatype</a> of your primary key to TEXT and it should work.</p>
207
208 <p>Every row must have a unique primary key. For a column with a
209 numeric type, SQLite thinks that <b>'0'</b> and <b>'0.0'</b> are the
210 same value because they compare equal to one another numerically.
211 (See the previous question.) Hence the values are not unique.</p></blockquote></li>
212 <a name="q5"></a>
213 <p><b>(5) Can multiple applications or multiple instances of the same
214 application access a single database file at the same time?</b></p>
215 <blockquote><p>Multiple processes can have the same database open at the same
216 time. Multiple processes can be doing a SELECT
217 at the same time. But only one process can be making changes to
218 the database at any moment in time, however.</p>
219
220 <p>SQLite uses reader/writer locks to control access to the database.
221 (Under Win95/98/ME which lacks support for reader/writer locks, a
222 probabilistic simulation is used instead.)
223 But use caution: this locking mechanism might
224 not work correctly if the database file is kept on an NFS filesystem.
225 This is because fcntl() file locking is broken on many NFS implementations.
226 You should avoid putting SQLite database files on NFS if multiple
227 processes might try to access the file at the same time. On Windows,
228 Microsoft's documentation says that locking may not work under FAT
229 filesystems if you are not running the Share.exe daemon. People who
230 have a lot of experience with Windows tell me that file locking of
231 network files is very buggy and is not dependable. If what they
232 say is true, sharing an SQLite database between two or more Windows
233 machines might cause unexpected problems.</p>
234
235 <p>We are aware of no other <i>embedded</i> SQL database engine that
236 supports as much concurrency as SQLite. SQLite allows multiple processes
237 to have the database file open at once, and for multiple processes to
238 read the database at once. When any process wants to write, it must
239 lock the entire database file for the duration of its update. But that
240 normally only takes a few milliseconds. Other processes just wait on
241 the writer to finish then continue about their business. Other embedded
242 SQL database engines typically only allow a single process to connect to
243 the database at once.</p>
244
245 <p>However, client/server database engines (such as PostgreSQL, MySQL,
246 or Oracle) usually support a higher level of concurrency and allow
247 multiple processes to be writing to the same database at the same time.
248 This is possible in a client/server database because there is always a
249 single well-controlled server process available to coordinate access.
250 If your application has a need for a lot of concurrency, then you should
251 consider using a client/server database. But experience suggests that
252 most applications need much less concurrency than their designers imagine.
253 </p>
254
255 <p>When SQLite tries to access a file that is locked by another
256 process, the default behavior is to return SQLITE_BUSY. You can
257 adjust this behavior from C code using the
258 <a href="c3ref/busy_handler.html">sqlite3_busy_handler()</a> or <a href="c3ref/busy_timeout.html">sqlite3_busy_timeout()</a>
259 API functions.</p></blockquote></li>
260 <a name="q6"></a>
261 <p><b>(6) Is SQLite threadsafe?</b></p>
262 <blockquote><p><a href="https://www2.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.html">Threads are evil</a>.
263 Avoid them.
264
265 <p>SQLite is threadsafe. We make this concession since many users choose
266 to ignore the advice given in the previous paragraph.
267 But in order to be thread-safe, SQLite must be compiled
268 with the SQLITE_THREADSAFE preprocessor macro set to 1. Both the Windows
269 and Linux precompiled binaries in the distribution are compiled this way.
270 If you are unsure if the SQLite library you are linking against is compiled
271 to be threadsafe you can call the <a href="c3ref/threadsafe.html">sqlite3_threadsafe()</a>
272 interface to find out.
273 </p>
274
275 <p>SQLite is threadsafe because it uses mutexes to serialize
276 access to common data structures. However, the work of acquiring and
277 releasing these mutexes will slow SQLite down slightly. Hence, if you
278 do not need SQLite to be threadsafe, you should disable the mutexes
279 for maximum performance. See the <a href="threadsafe.html">threading mode</a> documentation for
280 additional information.</p>
281
282 <p>Under Unix, you should not carry an open SQLite database across
283 a fork() system call into the child process.</p></blockquote></li>
284 <a name="q7"></a>
285 <p><b>(7) How do I list all tables/indices contained in an SQLite database</b></p>
286 <blockquote><p>If you are running the <b>sqlite3</b> command-line access program
287 you can type "<b>.tables</b>" to get a list of all tables. Or you
288 can type "<b>.schema</b>" to see the complete database schema including
289 all tables and indices. Either of these commands can be followed by
290 a LIKE pattern that will restrict the tables that are displayed.</p>
291
292 <p>From within a C/C++ program (or a script using Tcl/Ruby/Perl/Python
293 bindings) you can get access to table and index names by doing a SELECT
294 on a special table named "<b>SQLITE_SCHEMA</b>". Every SQLite database
295 has an SQLITE_SCHEMA table that defines the schema for the database.
296 The SQLITE_SCHEMA table looks like this:</p>
297 <blockquote><pre>
298 CREATE TABLE sqlite_schema (
299 type TEXT,
300 name TEXT,
301 tbl_name TEXT,
302 rootpage INTEGER,
303 sql TEXT
304 );
305 </pre></blockquote>
306 <p>For tables, the <b>type</b> field will always be <b>'table'</b> and the
307 <b>name</b> field will be the name of the table. So to get a list of
308 all tables in the database, use the following SELECT command:</p>
309 <blockquote><pre>
310 SELECT name FROM sqlite_schema
311 WHERE type='table'
312 ORDER BY name;
313 </pre></blockquote>
314 <p>For indices, <b>type</b> is equal to <b>'index'</b>, <b>name</b> is the
315 name of the index and <b>tbl_name</b> is the name of the table to which
316 the index belongs. For both tables and indices, the <b>sql</b> field is
317 the text of the original CREATE TABLE or CREATE INDEX statement that
318 created the table or index. For automatically created indices (used
319 to implement the PRIMARY KEY or UNIQUE constraints) the <b>sql</b> field
320 is NULL.</p>
321
322 <p>The SQLITE_SCHEMA table cannot be modified using UPDATE, INSERT,
323 or DELETE (except under
324 <a href="pragma.html#pragma_writable_schema">extraordinary conditions</a>).
325 The SQLITE_SCHEMA table is automatically updated by commands like
326 CREATE TABLE, CREATE INDEX, DROP TABLE, and DROP INDEX.</p>
327
328 <p>Temporary tables do not appear in the SQLITE_SCHEMA table. Temporary
329 tables and their indices and triggers occur in another special table
330 named SQLITE_TEMP_SCHEMA. SQLITE_TEMP_SCHEMA works just like SQLITE_SCHEMA
331 except that it is only visible to the application that created the
332 temporary tables. To get a list of all tables, both permanent and
333 temporary, one can use a command similar to the following:
334 <blockquote><pre>
335 SELECT name FROM
336 (SELECT * FROM sqlite_schema UNION ALL
337 SELECT * FROM sqlite_temp_schema)
338 WHERE type='table'
339 ORDER BY name
340 </pre></blockquote></blockquote></li>
341 <a name="q8"></a>
342 <p><b>(8) Are there any known size limits to SQLite databases?</b></p>
343 <blockquote><p>See <a href="limits.html">limits.html</a> for a full discussion of
344 the limits of SQLite.</p></blockquote></li>
345 <a name="q9"></a>
346 <p><b>(9) What is the maximum size of a VARCHAR in SQLite?</b></p>
347 <blockquote><p>SQLite does not enforce the length of a VARCHAR. You can declare
348 a VARCHAR(10) and SQLite will be happy to store a 500-million character
349 string there. And it will keep all 500-million characters intact.
350 Your content is never truncated. SQLite understands the column type
351 of "VARCHAR(<i>N</i>)" to be the same as "TEXT", regardless of the value
352 of <i>N</i>.
353 </p></blockquote></li>
354 <a name="q10"></a>
355 <p><b>(10) Does SQLite support a BLOB type?</b></p>
356 <blockquote><p>SQLite allows you to store BLOB data in any
357 column, even columns that are declared to hold some other type.
358 BLOBs can even be used as PRIMARY KEYs.</p></blockquote></li>
359 <a name="q11"></a>
360 <p><b>(11) How do I add, delete or rename columns from an existing table in SQLite?</b></p>
361 <blockquote><p>SQLite has limited ALTER TABLE support that you can use to
362 add, rename or drop columns or to change the name of a table
363 as detailed at <a href="lang_altertable.html">ALTER TABLE</a>.</p>
364
365 <p>If you want to make more complex changes in the structure or
366 constraints of a table or its columns, you will have to recreate it.
367 You can save existing data to a temporary table, drop the
368 old table, create the new table, then copy the data back in from
369 the temporary table. See <a href="lang_altertable.html#otheralter">
370 Making Other Kinds Of Table Schema Changes</a> for procedure.</p></blockquote></li>
371 <a name="q12"></a>
372 <p><b>(12) I deleted a lot of data but the database file did not get any
373 smaller. Is this a bug?</b></p>
374 <blockquote><p>No. When you delete information from an SQLite database, the
375 unused disk space is added to an internal "free-list" and is reused
376 the next time you insert data. The disk space is not lost. But
377 neither is it returned to the operating system.</p>
378
379 <p>If you delete a lot of data and want to shrink the database file,
380 run the <a href="lang_vacuum.html">VACUUM</a> command.
381 VACUUM will reconstruct
382 the database from scratch. This will leave the database with an empty
383 free-list and a file that is minimal in size. Note, however, that the
384 VACUUM can take some time to run and it can use up to twice
385 as much temporary disk space as the original file while it is running.
386 </p>
387
388 <p>An alternative to using the VACUUM command
389 is auto-vacuum mode, enabled using the
390 <a href="pragma.html#pragma_auto_vacuum">auto_vacuum pragma</a>.</p></blockquote></li>
391 <a name="q13"></a>
392 <p><b>(13) Can I use SQLite in my commercial product without paying royalties?</b></p>
393 <blockquote><p>Yes. SQLite is in the
394 <a href="copyright.html">public domain</a>. No claim of ownership is made
395 to any part of the code. You can do anything you want with it.</p></blockquote></li>
396 <a name="q14"></a>
397 <p><b>(14) How do I use a string literal that contains an embedded single-quote (')
398 character?</b></p>
399 <blockquote><p>The SQL standard specifies that single-quotes in strings are escaped
400 by putting two single quotes in a row. SQL works like the Pascal programming
401 language in this regard. Example:
402 </p>
403
404 <blockquote><pre>
405 INSERT INTO xyz VALUES('5 O''clock');
406 </pre></blockquote></blockquote></li>
407 <a name="q15"></a>
408 <p><b>(15) What is an SQLITE_SCHEMA error, and why am I getting one?</b></p>
409 <blockquote><p>An <a href="rescode.html#schema">SQLITE_SCHEMA</a> error is returned when a
410 prepared SQL statement is no longer valid and cannot be executed.
411 When this occurs, the statement must be recompiled from SQL using
412 the <a href="c3ref/prepare.html">sqlite3_prepare()</a> API.
413 An SQLITE_SCHEMA error can only occur when using the <a href="c3ref/prepare.html">sqlite3_prepare()</a>,
414 and <a href="c3ref/step.html">sqlite3_step()</a> interfaces to run SQL.
415 You will never receive an <a href="rescode.html#schema">SQLITE_SCHEMA</a> error from
416 <a href="c3ref/exec.html">sqlite3_exec()</a>. Nor will you receive an error if you
417 prepare statements using <a href="c3ref/prepare.html">sqlite3_prepare_v2()</a> instead of
418 <a href="c3ref/prepare.html">sqlite3_prepare()</a>.</p>
419
420 <p>The <a href="c3ref/prepare.html">sqlite3_prepare_v2()</a> interface creates a
421 <a href="c3ref/stmt.html">prepared statement</a> that will automatically recompile itself if
422 the schema changes. The easiest way to deal with
423 <a href="rescode.html#schema">SQLITE_SCHEMA</a> errors is to always use <a href="c3ref/prepare.html">sqlite3_prepare_v2()</a>
424 instead of <a href="c3ref/prepare.html">sqlite3_prepare()</a>.</blockquote></li>
425 <a name="q17"></a>
426 <p><b>(17) I get some compiler warnings when I compile SQLite.
427 Isn't this a problem? Doesn't it indicate poor code quality?</b></p>
428 <blockquote><p>Quality assurance in SQLite is done using
429 <a href="testing.html#coverage">full-coverage testing</a>,
430 not by compiler warnings or other static code analysis tools.
431 In other words, we verify that SQLite actually gets the
432 correct answer, not that it merely satisfies stylistic constraints.
433 Most of the SQLite code base is devoted purely to testing.
434 The SQLite test suite runs tens of thousands of separate test cases and
435 many of those test cases are parameterized so that hundreds of millions
436 of tests involving billions of SQL statements are run and evaluated
437 for correctness prior to every release. The developers use code
438 coverage tools to verify that all paths through the code are tested.
439 Whenever a bug is found in SQLite, new test cases are written to
440 exhibit the bug so that the bug cannot recur undetected in the future.</p>
441
442 <p>During testing, the SQLite library is compiled with special
443 instrumentation that allows the test scripts to simulate a wide
444 variety of failures in order to verify that SQLite recovers
445 correctly. Memory allocation is carefully tracked and no memory
446 leaks occur, even following memory allocation failures. A custom
447 VFS layer is used to simulate operating system crashes and power
448 failures in order to ensure that transactions are atomic across
449 these events. A mechanism for deliberately injecting I/O errors
450 shows that SQLite is resilient to such malfunctions. (As an
451 experiment, try inducing these kinds of errors on other SQL database
452 engines and see what happens!)</p>
453
454 <p>We also run SQLite using <a href="http://valgrind.org">Valgrind</a>
455 on Linux and verify that it detects no problems.</p>
456
457 <p>Some people say that we should eliminate all warnings because
458 benign warnings mask real warnings that might arise in future changes.
459 This is true enough. But in reply, the developers observe that all
460 warnings have already been fixed in the builds
461 used for SQLite development (various versions of GCC, MSVC,
462 and clang).
463 Compiler warnings usually only arise from compilers or compile-time
464 options that the SQLite developers do not use themselves.</p></blockquote></li>
465 <a name="q18"></a>
466 <p><b>(18) Case-insensitive matching of Unicode characters does not work.</b></p>
467 <blockquote>The default configuration of SQLite only supports case-insensitive
468 comparisons of ASCII characters. The reason for this is that doing
469 full Unicode case-insensitive comparisons and case conversions
470 requires tables and logic that would nearly double the size of
471 the SQLite library. The
472 SQLite developers reason that any application that needs full
473 Unicode case support probably already has the necessary tables and
474 functions and so SQLite should not take up space to
475 duplicate this ability.</p>
476
477 <p>Instead of providing full Unicode case support by default,
478 SQLite provides the ability to link against external
479 Unicode comparison and conversion routines.
480 The application can overload the built-in <a href="datatype3.html#collation">NOCASE</a> collating
481 sequence (using <a href="c3ref/create_collation.html">sqlite3_create_collation()</a>) and the built-in
482 <a href="lang_corefunc.html#like">like()</a>, <a href="lang_corefunc.html#upper">upper()</a>, and <a href="lang_corefunc.html#lower">lower()</a> functions
483 (using <a href="c3ref/create_function.html">sqlite3_create_function()</a>).
484 The SQLite source code includes an "ICU" extension that does
485 these overloads. Or, developers can write their own overloads
486 based on their own Unicode-aware comparison routines already
487 contained within their project.</blockquote></li>
488 <a name="q19"></a>
489 <p><b>(19) INSERT is really slow - I can only do few dozen INSERTs per second</b></p>
490 <blockquote>Actually, SQLite will easily do 50,000 or more <a href="lang_insert.html">INSERT</a> statements per second
491 on an average desktop computer. But it will only do a few dozen transactions
492 per second. Transaction speed is limited by the rotational speed of
493 your disk drive. A transaction normally requires two complete rotations
494 of the disk platter, which on a 7200RPM disk drive limits you to about
495 60 transactions per second.
496
497 <p>Transaction speed is limited by disk drive speed because (by default)
498 SQLite actually waits until the data really is safely stored on the disk
499 surface before the transaction is complete. That way, if you suddenly lose
500 power or if your OS crashes, your data is still safe. For details,
501 read about <a href="atomiccommit.html">atomic commit in SQLite.</a>.
502
503 <p>By default, each INSERT statement is its own transaction. But if you
504 surround multiple INSERT statements with <a href="lang_transaction.html">BEGIN</a>...<a href="lang_transaction.html">COMMIT</a> then all the
505 inserts are grouped into a single transaction. The time needed to commit
506 the transaction is amortized over all the enclosed insert statements and
507 so the time per insert statement is greatly reduced.
508
509 <p>Another option is to run <a href="pragma.html#pragma_synchronous">PRAGMA synchronous=OFF</a>. This command will
510 cause SQLite to not wait on data to reach the disk surface, which will make
511 write operations appear to be much faster. But if you lose power in the
512 middle of a transaction, your database file might go corrupt.
513
514 <p><i><b>Update 2024-11-19:</b> The text above is a really old answer.
515 SQLite will do far more than 50K inserts/second now, and everybody uses SSD
516 nowadays, making the performance faster still. But the gist of the answer
517 above remains correct: Putting multiple operations inside a single
518 transaction can improve performance dramatically by avoiding the overhead
519 of transaction control after each individual operation.</i></blockquote></li>
520 <a name="q20"></a>
521 <p><b>(20) I accidentally deleted some important information from my SQLite database.
522 How can I recover it?</b></p>
523 <blockquote>If you have a backup copy of your database file, recover the information
524 from your backup.
525
526 <p>If you do not have a backup, recovery is very difficult. You might
527 be able to find partial string data in a binary dump of the raw database
528 file. Recovering numeric data might also be possible given special tools,
529 though to our knowledge no such tools exist. SQLite is sometimes compiled
530 with the <a href="compile.html#secure_delete">SQLITE_SECURE_DELETE</a> option which overwrites all deleted content
531 with zeros. If that is the case then recovery is clearly impossible.
532 Recovery is also impossible if you have run <a href="lang_vacuum.html">VACUUM</a> since the data was
533 deleted. If SQLITE_SECURE_DELETE is not used and VACUUM has not been run,
534 then some of the deleted content might still be in the database file, in
535 areas marked for reuse. But, again, there exist no procedures or tools
536 that we know of to help you recover that data.</blockquote></li>
537 <a name="q21"></a>
538 <p><b>(21) What is an SQLITE_CORRUPT error? What does it mean for the database
539 to be "malformed"? Why am I getting this error?</b></p>
540 <blockquote><p>An <a href="rescode.html#corrupt">SQLITE_CORRUPT</a> error is returned when SQLite detects an error
541 in the structure, format, or other control elements of the
542 database file.</p>
543
544 <p>SQLite does not corrupt database files without external help.
545 If your application crashes in the middle of an
546 update, your data is safe. The database is safe even if your OS
547 crashes or takes a power loss. The crash-resistance of SQLite has
548 been extensively studied and tested and is attested by years of real-world
549 experience by billions of users.</p>
550
551 <p>That said, there are a number of things that external programs or bugs
552 in your hardware or OS can do to corrupt a database file. See
553 <a href="howtocorrupt.html">How To Corrupt An SQLite Database File</a> for
554 further information.
555
556 <p>You can use <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a>
557 to do a thorough but time intensive test of the database integrity.</p>
558
559 <p>You can use <a href="pragma.html#pragma_quick_check">PRAGMA quick_check</a> to do a faster
560 but less thorough test of the database integrity.</p>
561
562 <p>Depending how badly your database is corrupted, you may be able to
563 recover some of the data by using the CLI to dump the schema and contents
564 to a file and then recreate. Unfortunately, once humpty-dumpty falls off
565 the wall, it is generally not possible to put him back together again.</p></blockquote></li>
566 <a name="q22"></a>
567 <p><b>(22) Does SQLite support foreign keys?</b></p>
568 <blockquote><p>
569 As of <a href="releaselog/3_6_19.html">version 3.6.19</a> (2009-10-14),
570 SQLite supports <a href="foreignkeys.html">foreign key constraints</a>. But enforcement
571 of foreign key constraints is turned off by default (for backwards compatibility).
572 To enable foreign key constraint enforcement, run
573 <a href="pragma.html#pragma_foreign_keys">PRAGMA foreign_keys=ON</a> or compile with
574 <a href="compile.html#default_foreign_keys">-DSQLITE_DEFAULT_FOREIGN_KEYS=1</a>.</blockquote></li>
575 <a name="q23"></a>
576 <p><b>(23) I get a compiler error if I use the SQLITE_OMIT_...
577 compile-time options when building SQLite.</b></p>
578 <blockquote>The <a href="compile.html#omitfeatures">SQLITE_OMIT_...</a> compile-time options only work
579 when building from canonical source files. They do <u>not</u> work
580 when you build from the SQLite <a href="amalgamation.html">amalgamation</a> or from the pre-processed
581 source files.
582
583 <p>It is possible to build a special <a href="amalgamation.html">amalgamation</a> that will work with
584 a predetermined set of SQLITE_OMIT_... options. Instructions for doing
585 so can be found with the <a href="compile.html#omitfeatures">SQLITE_OMIT_... documentation</a>.</blockquote></li>
586 <a name="q24"></a>
587 <p><b>(24) My WHERE clause expression <tt>column1="column1"</tt> does not work.
588 It causes every row of the table to be returned, not just the rows
589 where column1 has the value "column1".</b></p>
590 <blockquote>Use single-quotes, not double-quotes, around string literals in SQL.
591 This is what the SQL standard requires. Your WHERE clause expression
592 should read: <tt>column1='column1'</tt>
593
594 <p>SQL uses double-quotes around identifiers (column or table names) that
595 contains special characters or which are keywords. So double-quotes are
596 a way of escaping identifier names. Hence, when you say
597 <tt>column1="column1"</tt> that is equivalent to
598 <tt>column1=column1</tt> which is obviously always true.</blockquote></li>
599 <a name="q25"></a>
600 <p><b>(25) How are the syntax diagrams (a.k.a. "railroad" diagrams) for
601 SQLite generated?</b></p>
602 <blockquote>Each diagram is hand-written using the <a href="https://pikchr.org/">Pikchr</a>
603 diagramming language. These hand-written specifications are converted into
604 SVG and inserted inline in the HTML files as part of the documentation build process.
605 <p>
606 Many historical versions of the SQLite documentation used a different process for
607 generating the syntax diagrams. The historical process was based on Tcl/Tk and is
608 described at <a href="http://wiki.tcl-lang.org/21708">http://wiki.tcl-lang.org/21708</a>. The newer Pikchr-based syntax diagrams
609 first landed on trunk on 2020-09-26.</blockquote></li>
610 <a name="q26"></a>
611 <p><b>(26) The SQL standard requires that a UNIQUE constraint be enforced even if
612 one or more of the columns in the constraint are NULL, but SQLite does
613 not do this. Isn't that a bug?</b></p>
614 <blockquote>Perhaps you are referring to the following statement from SQL92:
615
616 <blockquote>
617 A unique constraint is satisfied if and only if no two rows in a
618 table have the same non-null values in the unique columns.
619 </blockquote>
620
621 That statement is ambiguous, having at least two possible interpretations:
622
623 <ol>
624 <li>A unique constraint is satisfied if and only if no two rows in a
625 table have the same values and have non-null values in the unique columns.
626 <li>A unique constraint is satisfied if and only if no two rows in a
627 table have the same values in the subset of unique columns that are not null.
628 </ol>
629
630 SQLite follows interpretation (1), as does PostgreSQL, MySQL, Oracle,
631 and Firebird. It is true that Informix and Microsoft SQL Server use
632 interpretation (2), however we the SQLite developers hold that
633 interpretation (1) is the most natural reading
634 of the requirement and we also want to maximize compatibility with other
635 SQL database engines, and most other database engines also go with (1),
636 so that is what SQLite does.</blockquote></li>
637 <a name="q27"></a>
638 <p><b>(27) What is the Export Control Classification Number (ECCN) for SQLite?</b></p>
639 <blockquote>After careful review of the Commerce Control List (CCL), we are convinced
640 that the core public-domain SQLite source code is not described by any ECCN,
641 hence the ECCN should be reported as <b>EAR99</b>.
642
643 <p>The above is true for the core public-domain SQLite. If you extend
644 SQLite by adding new code, or if you statically link SQLite with your
645 application, that might change the ECCN in your particular case.</blockquote></li>
646 <a name="q28"></a>
647 <p><b>(28) My query does not return the column name that I expect. Is this a bug?</b></p>
648 <blockquote>If the columns of your result set are named by AS clauses, then SQLite
649 is guaranteed to use the identifier to the right of the AS keyword as the
650 column name. If the result set does not use an AS clause, then SQLite
651 is free to name the column anything it wants.
652 See the <a href="c3ref/column_name.html">sqlite3_column_name()</a> documentation for further information.</blockquote></li>
653 <a name="q29"></a>
654 <p><b>(29) Where did my database go? (Or: How did my database become empty?)</b></p>
655 <blockquote>Unless opened with flags to prevent it, a SQLite database is created
656 if it does not already exist. Newly created databases are initially empty.
657 This can confuse people who inadvertantly open different database files
658 in different contexts, due to either a typo in the filename
659 or use of a relative pathname that is used with
660 differing current directories for the opening processes.</blockquote></li>
661 </ol>
662 <p align="center"><small><i>This page was last updated on 2024-11-26 23:53:35Z </small></i></p>
663