On May 24, 2017 11:33:13 AM EDT, Manoj Sengottuvel <[hidden email]> wrote:
>If I create new table (table name : ACCOUNT_MSTR) then the auto index
>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
>When I am seeing in the result sql column is NULL.
>Why the sql column shows NULL value?
>Do I need to create the index for the primary key?
>But the same case if I create my own index for the table (table name :
>ACCOUNT_MSTR) then I can able to see the index creation script in sql
>I don't understand how the auto index is working?
>sqlite-users mailing list
>[hidden email] >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
On 5/24/17, Manoj Sengottuvel <[hidden email]> 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
[hidden email] _______________________________________________
sqlite-users mailing list
[hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users