Documentation Source Text

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Changes to pages/partialindex.in.

    36     36   </p>
    37     37   
    38     38   <p>
    39     39   ^The expression following the WHERE clause may contain operators,
    40     40   literal values, and names of columns in the table being indexed.
    41     41   ^The WHERE clause may <em>not</em> contains subqueries, references to other
    42     42   tables, functions, or [bound parameters].  The LIKE, GLOB, MATCH,
    43         -and REGEXP operators in SQLite as functions by the same name.
           43  +and REGEXP operators in SQLite are implemented as functions by the same name.
    44     44   ^Since functions are prohibited in the 
    45     45   WHERE clause of a CREATE INDEX statement, so too are the LIKE, GLOB,
    46     46   MATCH, and REGEXP operators.</p>
    47     47   
    48     48   <p>
    49     49   ^Only rows of the table for which the WHERE clause evaluates to true
    50     50   are included in the index.  ^If the WHERE clause expression evaluates 
    51         -to NULL or to false for some row of the table, then those rows are omitted 
           51  +to NULL or to false for some rows of the table, then those rows are omitted 
    52     52   from the index.
    53     53   </p>
    54     54   
    55     55   <p>
    56     56   ^The columns referenced in the WHERE clause of a partial index can be
    57     57   any of the columns in the table, not just columns that happen to be
    58     58   indexed.  However, it is very common for the WHERE clause
................................................................................
   100    100     team_id         INTEGER REFERENCES team,
   101    101     is_team_leader  BOOLEAN,
   102    102     -- other fields elided
   103    103   );
   104    104   </pre></blockquote>)^
   105    105   
   106    106   <p>The team_id field cannot be unique because there usually multiple people
   107         -on the same time.  One cannot make the combination of team_id and is_team_leader
          107  +on the same team.  One cannot make the combination of team_id and is_team_leader
   108    108   unique since there are usually multiple non-leaders on each team.  ^(The
   109    109   solution to enforcing one leader per team is to create a unique index
   110    110   on team_id but restricted to those entries for which is_team_leader is
   111    111   true:</p>
   112    112   
   113    113   <blockquote>
   114    114   CREATE UNIQUE INDEX team_leader ON person(team_id) WHERE is_team_leader;
   115    115   </blockquote>)^
   116    116   
   117    117   ^(<p>Coincidentally, that same index is useful for locating the team leader
   118         -of a particular time:</p>
          118  +of a particular team:</p>
   119    119   
   120    120   <blockquote>
   121    121   SELECT person_id FROM person WHERE is_team_leader AND team_id=?1;
   122    122   </blockquote>)^
   123    123   
   124    124   <h2>3.0 Queries Using Partial Indexes</h2>
   125    125