Documentation Source Text

Check-in [8bf9d71fd6]
Login

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

Overview
Comment:Fix typos in the partial index document.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 8bf9d71fd6597a013d7f25a4738362ff7fd78c04
User & Date: drh 2013-08-08 02:54:33.561
Context
2013-08-08
17:58
Fix typos in the "vfs.html" document. (check-in: 37c89b86f8 user: drh tags: trunk)
02:54
Fix typos in the partial index document. (check-in: 8bf9d71fd6 user: drh tags: trunk)
2013-08-07
23:38
Add documentation for SQLITE_ALLOW_URI_AUTHORITY. (check-in: cadb86b398 user: drh tags: trunk)
Changes
Side-by-Side Diff 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

36
37
38
39
40
41
42

43
44
45
46
47
48
49
50

51
52
53
54
55
56
57
58








-
+







-
+








</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.
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 row of the table, then those rows are omitted 
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
︙
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

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








-
+










-
+








  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
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 time:</p>
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>
︙