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