Documentation Source Text

Check-in [b354d15151]
Login

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

Overview
Comment:Document the goofy behavior of PRIMARY KEY(x DESC) still allowing x to be a rowid if it is of type INTEGER.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:b354d151517608bf7101f874c9b43e050d04d010
User & Date: drh 2009-05-11 17:41:10
Context
2009-05-14
18:12
Changed lang.html to use hd_resolve for linkage rather than crafted links. check-in: 17a3eefb66 user: shaneh tags: trunk
2009-05-11
17:41
Document the goofy behavior of PRIMARY KEY(x DESC) still allowing x to be a rowid if it is of type INTEGER. check-in: b354d15151 user: drh tags: trunk
14:24
Fixed typo. check-in: 218a28e1d5 user: shaneh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

749
750
751
752
753
754
755





















756
757
758
759
760
761
762
The AUTOINCREMENT keyword only works on a column that is an alias
for the rowid.</p>

<p>Note that searches against a rowid are generally about twice as
fast as searches against any other PRIMARY KEY or indexed value.
</p>























<tcl>
##############################################################################
Section {CREATE TRIGGER} createtrigger {{CREATE TRIGGER}}

BubbleDiagram create-trigger-stmt 1
</tcl>







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
The AUTOINCREMENT keyword only works on a column that is an alias
for the rowid.</p>

<p>Note that searches against a rowid are generally about twice as
fast as searches against any other PRIMARY KEY or indexed value.
</p>

<p><b>Goofy behavior alert:</b>  The following three declarations all cause
the column "x" be an alias for the rowid:</p>

<ul>
<li><tt>CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);</tt>
<li><tt>CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));</tt>
<li><tt>CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));</tt>
</ul>

<p>But, in contrast, the following declaration does <u>not</u> result in
"x" being an alias for the rowid:</p>

<ul>
<li><tt>CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);</tt>
</ul>

<p>This asymmetrical behavior is unfortunate and is really due to a bug
in the parser in early versions of SQLite.  But fixing the bug would
result in very serious backwards incompatibilities.  The SQLite developers
feel that goofy behavior in an corner case is far better than
a compatibility break, so the original behavior is retained.</p>

<tcl>
##############################################################################
Section {CREATE TRIGGER} createtrigger {{CREATE TRIGGER}}

BubbleDiagram create-trigger-stmt 1
</tcl>