Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Documentation updates associated with WITHOUT ROWID and the extension of the sqlite3_index_info object. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
21034f356e81b2365fb77e63f6f5307c |
User & Date: | drh 2013-11-11 23:27:47.847 |
Context
2013-11-13
| ||
22:39 | Update the change log to show the skip-scan optimization and other recent changes. Add documentation on skip-scan. (check-in: 9b1dc53b75 user: drh tags: trunk) | |
2013-11-11
| ||
23:27 | Documentation updates associated with WITHOUT ROWID and the extension of the sqlite3_index_info object. (check-in: 21034f356e user: drh tags: trunk) | |
2013-11-09
| ||
17:27 | Tweaks to WITHOUT ROWID documentation. (check-in: 478a586d67 user: drh tags: trunk) | |
Changes
Changes to pages/autoinc.in.
︙ | ︙ | |||
105 106 107 108 109 110 111 | insert fails due to (for example) a uniqueness constraint, the ROWID of the failed insertion attempt might not be reused on subsequent inserts, resulting in gaps in the ROWID sequence. ^AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.</p> <p>Because AUTOINCREMENT keyword changes the behavior of the ROWID selection | | > | | | 105 106 107 108 109 110 111 112 113 114 115 | insert fails due to (for example) a uniqueness constraint, the ROWID of the failed insertion attempt might not be reused on subsequent inserts, resulting in gaps in the ROWID sequence. ^AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.</p> <p>Because AUTOINCREMENT keyword changes the behavior of the ROWID selection algorithm, AUTOINCREMENT is not allowed on [WITHOUT ROWID] tables or on any table column other than INTEGER PRIMARY KEY. ^Any attempt to use AUTOINCREMENT on a [WITHOUT ROWID] table or on a column other than the INTEGER PRIMARY KEY column results in an error.</p> |
Changes to pages/changes.in.
︙ | ︙ | |||
16 17 18 19 20 21 22 23 24 25 26 27 28 29 | global nChng aChng set aChng($nChng) [list $date $desc $options] incr nChng } chng {2013-12-?? (3.8.2)} { <li>Added support for [WITHOUT ROWID] tables. } chng {2013-10-17 (3.8.1)} { <li>Added the [unlikely()] and [likelihood()] SQL functions to be used as hints to the query planner. <li>Enhancements to the query planner: <ul> | > > > > > | 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | global nChng aChng set aChng($nChng) [list $date $desc $options] incr nChng } chng {2013-12-?? (3.8.2)} { <li>Added support for [WITHOUT ROWID] tables. <li>Extended the [virtual table] interface, and in particular the [sqlite3_index_info] object to allow a virtual table to report its estimate on the number of rows that will be returned by a query. <li>Update the [R-Tree extension] to make use of the enhanced virtual table interface. } chng {2013-10-17 (3.8.1)} { <li>Added the [unlikely()] and [likelihood()] SQL functions to be used as hints to the query planner. <li>Enhancements to the query planner: <ul> |
︙ | ︙ |
Changes to pages/lang.in.
︙ | ︙ | |||
823 824 825 826 827 828 829 | of values in its primary key columns. ^For the purposes of determining the uniqueness of primary key values, NULL values are considered distinct from all other values, including other NULLs. ^If an [INSERT] or [UPDATE] statement attempts to modify the table content so that two or more rows feature identical primary key values, it is a constraint violation. According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the | | | | | | 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 | of values in its primary key columns. ^For the purposes of determining the uniqueness of primary key values, NULL values are considered distinct from all other values, including other NULLs. ^If an [INSERT] or [UPDATE] statement attempts to modify the table content so that two or more rows feature identical primary key values, it is a constraint violation. According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. ^Unless the column is an [INTEGER PRIMARY KEY] or the table is a [WITHOUT ROWID] table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing so might break legacy applications. Hence, it has been decided to merely document the fact that SQLite allowing NULLs in most PRIMARY KEY columns. <tcl>hd_fragment uniqueconst {UNIQUE} {unique constraint}</tcl> <p>^A <b>UNIQUE</b> constraint is similar to a PRIMARY KEY constraint, except that a single table may have any number of UNIQUE constraints. ^For each UNIQUE constraint on the table, each row must contain a unique combination of values in the columns identified by the UNIQUE constraint. ^For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs. <p>^In most cases, UNIQUE and PRIMARY KEY constraints are implemented by creating a unique index in the database. (The exceptions are [INTEGER PRIMARY KEY] and PRIMARY KEYs on |
︙ | ︙ | |||
906 907 908 909 910 911 912 | <p>The data for rowid tables is stored as a B-Tree structure containing one entry for each table row, using the rowid value as the key. This means that retrieving or sorting records by rowid is fast. Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value. | | | | 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 | <p>The data for rowid tables is stored as a B-Tree structure containing one entry for each table row, using the rowid value as the key. This means that retrieving or sorting records by rowid is fast. Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value. <p> ^With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such a column is usually referred to as an "integer primary key". A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER". ^Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer [affinity] and a unique index, not as an alias for the rowid. |
︙ | ︙ |
Changes to pages/withoutrowid.in.
1 2 3 4 5 6 7 8 | <title>The WITHOUT ROWID Optimization</title> <tcl> hd_keywords {WITHOUT rowid} {WITHOUT ROWID} </tcl> <h1 align="center">The WITHOUT ROWID Optimization</h1> <h2>1.0 Introduction</h2> | | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | <title>The WITHOUT ROWID Optimization</title> <tcl> hd_keywords {WITHOUT rowid} {WITHOUT ROWID} </tcl> <h1 align="center">The WITHOUT ROWID Optimization</h1> <h2>1.0 Introduction</h2> <p>^By default, every row in SQLite has a special column, usually called the "[rowid]", that uniquely identifies that row within the table. ^However if the phrase "WITHOUT ROWID" is added to the end of a [CREATE TABLE] statement, then the special "rowid" column is omitted. There are sometimes space and performance advantages to omitting the rowid.</p> <h3>1.1 Syntax</h3> <p>^(To create a WITHOUT ROWID table, simply add the keywords "WITHOUT ROWID" |
︙ | ︙ |