On 5/24/17, Manoj Sengottuvel <smanoj...@gmail.com> wrote:
>
> If I create new table (table name : ACCOUNT_MSTR) then the auto index is
> created as sqlite_autoindex_ACCOUNT_MSTR_1.
>
> Then I am trying to execute the following query  ' select * from
> sqlite_master where type='index';'
>
> I got the following result [sqlite_master.sql field is NULL]
>

When a CREATE TABLE statement contains UNIQUE constraints, and
sometimes also PRIMARY KEYs (depending on the details of the PRIMARY
KEY), SQLite needs to create an index to support those features.  This
is done for you automatically.  You don't need to worry about it.

The sqlite_autoindex_ACCOUNT_MSTR_1 index is an example of such an index.

SQLite needs to know the location for the root b-tree page for this
index in order to use it.  For that reason, there is an entry in the
sqlite_master table for this index that records the rootpage.  But
there is no SQL associated with the index as it is created
automatically by the CREATE TABLE statement, so the sqlite_master.sql
for that entry is NULL.

All of the above is completely automatic.  There is no reason for you
to worry about any of it (unless you are hacking on the SQLite core).
It should not affect your usage of SQLite in any way.  You do not need
to understand it in order to use SQLite effectively  It just works.

The term "automatic index" is overloaded.  There are indexes
associated with UNIQUE and PRIMARY KEY constraints that are created
automatically, as described above.  There is a complete different
mechanism called "automatic indexes" that creates transient indexes
that only live for the duration of a single SELECT statement and which
are used to help optimize certain joins.  (They implement what MySQL
or Postgres would call a "hash join".)  Do not be confused by these
two completely different meanings for the term "automatic index".
-- 
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to