1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>SQLite Query Language: CREATE INDEX</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
120
<h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>CREATE INDEX</h2><h4><a href="syntaxdiagrams.html#create-index-stmt">create-index-stmt:</a></h4><blockquote> <img alt="syntax diagram create-index-stmt" src="images/syntax/create-index-stmt.gif"></img> </blockquote>
121
<h4><a href="syntaxdiagrams.html#indexed-column">indexed-column:</a></h4><blockquote> <img alt="syntax diagram indexed-column" src="images/syntax/indexed-column.gif"></img> </blockquote>
124
<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
125
by the name of the new index, the keyword "ON", the name of a previously
126
created table that is to be indexed, and a parenthesized list of names of
127
columns in the table that are used for the index key.</p>
129
<a name="descidx"></a>
131
<p>Each column name can be followed by one of the "ASC" or "DESC" keywords
132
to indicate sort order. The sort order may or may not be ignored depending
133
on the database file format, and in particular the <a href="fileformat2.html#schemaformat">schema format number</a>.
134
The "legacy" schema format (1) ignores index
135
sort order. The descending index schema format (4) takes index sort order
136
into account. Only copies of SQLite newer than <a href="releaselog/3_3_0.html">version 3.3.0</a>
137
(released on 2006-01-10) are able to understand the newer descending
138
index file format. For compatibility, version of SQLite between 3.3.0
139
and 3.7.9 use the legacy schema format by default. The newer schema format is
140
used by default in version 3.7.10 and later.
141
The <a href="pragma.html#pragma_legacy_file_format">legacy_file_format pragma</a> can be used to change set the specific
142
behavior for any version of SQLite.</p>
144
<p>The COLLATE clause optionally following each column name defines a
145
collating sequence used for text entries in that column.
146
The default collating
147
sequence is the collating sequence defined for that column in the
148
<a href="lang_createtable.html">CREATE TABLE</a> statement. Or if no collating sequence is otherwise defined,
149
the built-in BINARY collating sequence is used.</p>
151
<p>There are no arbitrary limits on the number of indices that can be
152
attached to a single table. The number of columns in an index is
153
limited to the value set by
154
<a href="c3ref/limit.html">sqlite3_limit</a>(<a href="c3ref/c_limit_attached.html#sqlitelimitcolumn">SQLITE_LIMIT_COLUMN</a>,...).</p>
156
<a name="uniqueidx"></a>
158
<p>If the UNIQUE keyword appears between CREATE and INDEX then duplicate
159
index entries are not allowed. Any attempt to insert a duplicate entry
160
will result in an error. For the purposes of unique indices, all NULL values
161
are considered to different from all other NULL values and are thus unique.
162
This is one of the two possible interpretations of the SQL-92 standard
163
(the language in the standard is ambiguous) and is the interpretation
164
followed by PostgreSQL, MySQL, Firebird, and Oracle. Informix and
165
Microsoft SQL Server follow the other interpretation of the standard.</p>
167
<p>If the optional IF NOT EXISTS clause is present and another index
168
with the same name already exists, then this command becomes a no-op.</p>
170
<p>Indexes are removed with the <a href="lang_dropindex.html">DROP INDEX</a> command.</p>
added
removed
lang_createindex.html
