BlitzNG
/
database.mod
mirror of https://github.com/bmx-ng/database.mod.git


			
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101
							<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<title>SQLite Query Language: CREATE INDEX</title>
<style type="text/css">
body {
    margin: auto;
    font-family: Verdana, sans-serif;
    padding: 8px 1%;
}

a { color: #45735f }
a:visited { color: #734559 }

.logo { position:absolute; margin:3px; }
.tagline {
  float:right;
  text-align:right;
  font-style:italic;
  width:240px;
  margin:12px;
  margin-top:58px;
}

.toolbar {
  font-variant: small-caps;
  text-align: center;
  line-height: 1.6em;
  margin: 0;
  padding:1px 8px;
}
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
.toolbar a:visited { color: white; }
.toolbar a:hover { color: #80a796; background: white; }

.content    { margin: 5%; }
.content dt { font-weight:bold; }
.content dd { margin-bottom: 25px; margin-left:20%; }
.content ul { padding:0px; padding-left: 15px; margin:0px; }

/* rounded corners */
.se  { background: url(images/se.png) 100% 100% no-repeat #80a796}
.sw  { background: url(images/sw.png) 0% 100% no-repeat }
.ne  { background: url(images/ne.png) 100% 0% no-repeat }
.nw  { background: url(images/nw.png) 0% 0% no-repeat }

</style>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
  
</head>
<body>
<div><!-- container div to satisfy validator -->

<a href="lang.html">
           <h2 align="center">SQL As Understood By SQLite</h2></a><h1>CREATE INDEX</h1><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>
<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>


<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.</p>

<p>Each column name can be followed by one of the "ASC" or "DESC" keywords
to indicate sort order.  The sort order may or may not be ignored depending
on the database file format.  The "legacy" file format ignores index
sort order.  The descending index file format takes index sort order
into account.  Only copies of SQLite newer than version 3.3.0 
(released on 2006-01-10) are able to understand the newer descending
index file format and so for compatibility with older versions of
SQLite, the legacy file format is generated by default.  Use the
<a href="pragma.html#pragma_legacy_file_format">legacy_file_format</a> pragma to modify this behavior and generate
databases that use the newer file format.  Future versions of SQLite
may begin to generate the newer file format by default.</p>

<p>The COLLATE clause following each column name defines a collating
sequence used for text entries in that column.  The default collating
sequence is the collating sequence defined for that column in the
<a href="lang_createtable.html">CREATE TABLE</a> statement.  Or if no collating sequence is otherwise defined,
the built-in BINARY collating sequence is used.</p>

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table.  The number of columns in an index is 
limited to SQLITE_MAX_COLUMN.</p>

<p>If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  Any attempt to insert a duplicate entry
will result in an error.  For the purposes of unique indices, all NULL values
are considered to different from all other NULL values and are thus unique.
This is one of the two possible interpretations of the SQL-92 standard
(the language in the standard is ambiguious) and is the interpretation
followed by PostgreSQL, MySQL, Firebird, and Oracle.  Informix and
Microsoft SQL Server follow the other interpretation of the standard.</p>

<p>If the optional IF NOT EXISTS clause is present and another index
with the same name aleady exists, then this command becomes a no-op.</p>

<p>Indexes are removed with the <a href="lang_dropindex.html">DROP INDEX</a> 
command.</p>