Documentation Source Text
Check-in [8bf9d71fd6597a013d7f25a4738362ff7fd78c04]
Not logged in

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

Overview
SHA1 Hash:8bf9d71fd6597a013d7f25a4738362ff7fd78c04
Date: 2013-08-08 02:54:33
User: drh
Comment:Fix typos in the partial index document.
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/partialindex.in

36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
...
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
</p>

<p>
^The expression following the WHERE clause may contain operators,
literal values, and names of columns in the table being indexed.
^The WHERE clause may <em>not</em> contains subqueries, references to other
tables, functions, or [bound parameters].  The LIKE, GLOB, MATCH,
and REGEXP operators in SQLite as functions by the same name.
^Since functions are prohibited in the 
WHERE clause of a CREATE INDEX statement, so too are the LIKE, GLOB,
MATCH, and REGEXP operators.</p>

<p>
^Only rows of the table for which the WHERE clause evaluates to true
are included in the index.  ^If the WHERE clause expression evaluates 
to NULL or to false for some row of the table, then those rows are omitted 
from the index.
</p>

<p>
^The columns referenced in the WHERE clause of a partial index can be
any of the columns in the table, not just columns that happen to be
indexed.  However, it is very common for the WHERE clause
................................................................................
  team_id         INTEGER REFERENCES team,
  is_team_leader  BOOLEAN,
  -- other fields elided
);
</pre></blockquote>)^

<p>The team_id field cannot be unique because there usually multiple people
on the same time.  One cannot make the combination of team_id and is_team_leader
unique since there are usually multiple non-leaders on each team.  ^(The
solution to enforcing one leader per team is to create a unique index
on team_id but restricted to those entries for which is_team_leader is
true:</p>

<blockquote>
CREATE UNIQUE INDEX team_leader ON person(team_id) WHERE is_team_leader;
</blockquote>)^

^(<p>Coincidentally, that same index is useful for locating the team leader
of a particular time:</p>

<blockquote>
SELECT person_id FROM person WHERE is_team_leader AND team_id=?1;
</blockquote>)^

<h2>3.0 Queries Using Partial Indexes</h2>








|







|







 







|










|







36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
...
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
</p>

<p>
^The expression following the WHERE clause may contain operators,
literal values, and names of columns in the table being indexed.
^The WHERE clause may <em>not</em> contains subqueries, references to other
tables, functions, or [bound parameters].  The LIKE, GLOB, MATCH,
and REGEXP operators in SQLite are implemented as functions by the same name.
^Since functions are prohibited in the 
WHERE clause of a CREATE INDEX statement, so too are the LIKE, GLOB,
MATCH, and REGEXP operators.</p>

<p>
^Only rows of the table for which the WHERE clause evaluates to true
are included in the index.  ^If the WHERE clause expression evaluates 
to NULL or to false for some rows of the table, then those rows are omitted 
from the index.
</p>

<p>
^The columns referenced in the WHERE clause of a partial index can be
any of the columns in the table, not just columns that happen to be
indexed.  However, it is very common for the WHERE clause
................................................................................
  team_id         INTEGER REFERENCES team,
  is_team_leader  BOOLEAN,
  -- other fields elided
);
</pre></blockquote>)^

<p>The team_id field cannot be unique because there usually multiple people
on the same team.  One cannot make the combination of team_id and is_team_leader
unique since there are usually multiple non-leaders on each team.  ^(The
solution to enforcing one leader per team is to create a unique index
on team_id but restricted to those entries for which is_team_leader is
true:</p>

<blockquote>
CREATE UNIQUE INDEX team_leader ON person(team_id) WHERE is_team_leader;
</blockquote>)^

^(<p>Coincidentally, that same index is useful for locating the team leader
of a particular team:</p>

<blockquote>
SELECT person_id FROM person WHERE is_team_leader AND team_id=?1;
</blockquote>)^

<h2>3.0 Queries Using Partial Indexes</h2>