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
