StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Make sure that your existing SQLite database declares integer primary keys using only "INTEGER" (verbatim)--not "int" or "int16" or any of the other possibilities for declaring an integer that SQLite will recognize. </p> <p>I ran into a related problem when importing a SQLite database in Adobe AIR (which has a common codebase with Goodle and Mozilla and other consortium members, IIRC). My PK had been defined as "int" (verbatim). <em>SQLite treats "INTEGER" primary keys differently than it treats "int" or "INT" or "int16" etc primary keys!</em></p> <p>Documented here: <a href="http://www.sqlite.org/datatypes.html" rel="nofollow">http://www.sqlite.org/datatypes.html</a></p> <p>An INTEGER primary key is treated by SQLite as a synonym for the RowId. Any other int type is just like a standard column, and with a standard column RowId will not necessarily equal the value in the PK column. </p> <p>However, Adobe and the other related subgroup of SQLite consortium members did not implement this (documented) behavior--for them <em>any/every</em> integer type used as the PK column is treated as a synonym for the row id-- and their failing to implement this distinction can result in erroneous joins when a pre-existing SQLite database is imported into their implementation(s), if the pre-existing database used anything other than "INTEGER" when declaring its integer-type primary keys.</p> <p>P.S. I brought this to Adobe's attention and discussed it <em>ad nauseam</em> on the SQLite mailing list and on the Adobe AIR forum. Adobe wrote me that they would document their departure from "standard" SQLite behavior but leave it as is, so I believe Android will also differ from SQLite documented behavior in this regard. </p> <p>P.P.S. It seems this subgroup of consortium members either did not envision the possibility that a database would be imported (i.e. they assumed the database would always be created anew via their interface) or they simply overlooked this (admittedly wonky) exceptional behavior in SQLite.</p> <p>P.P.P.S. This table, for example, from the database the OP is using would return spurious results when involved in joins on the [stop_id] column if attached by an implementation of SQLite that did not implement the "standard" INTEGER/int (et al) exceptional behavior but treated any/every int-type when used with the PK as a synonym for the rowid:</p> <pre><code> CREATE TABLE mt_stop ( stop_id int NOT NULL PRIMARY KEY ASC, stop_lat real NOT NULL CHECK (stop_lat >= -90 AND stop_lat <= 90), stop_lon real NOT NULL CHECK (stop_lon >= -180 AND stop_lon <= 180), stop_name varchar (120) DEFAULT 'Unknown' ) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload