Take a look at https://www.sqlite.org/lang_createtable.html Under the section "ROWIDs and the INTEGER PRIMARY KEY"
"If a table contains a user defined column named "rowid", "oid" or "_rowid_", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value." So t3 is a "without rowid table" (index b-tree) which happens to have a normal field named "rowid" t4 is a "regular rowid table" (table b-tree) where the identifier "rowid" goes to a different field name than the "must be an integer rowid" That table should still have the "must be an integer rowid" visible as "oid" or "_rowid_" t5 is a "without rowid table" (index b-tree) with a primary key of a single field with "integer" affinity. Aka "prefer to store it as an integer but don't have to" sort. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Tom Bassel Sent: Thursday, February 21, 2019 3:02 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] WITHOUT ROWID tables with a ROWID Hi, this is my first post to this group. I am not trying to be difficult. I work on an app that uses SQLite as the underlying database engine. At one time years ago I remember assuming that the ROWID column in a table in SQLite would always be an integer. However, I can see a user who is not familiar with SQLite create a table with a column named "rowid" that is not an integer (in table t4 below). Are tables t3, t4, and t5 below working as designed? Thanks and I really enjoy using SQLite and reading this mailing list. Tom SQLite version 3.27.1 2019-02-08 13:17:39 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t1(nonrowid); sqlite> insert into t1(rowid, nonrowid) values ('pluto', 'venus'); Error: datatype mismatch sqlite> create table t2(nonrowid text primary key) without rowid; sqlite> insert into t2 values('sun'); sqlite> select rowid, * from t2; Error: no such column: rowid sqlite> create table t3(rowid text primary key) without rowid; sqlite> insert into t3 values('moon'); sqlite> select rowid, * from t3; moon|moon sqlite> create table t4(rowid text primary key); sqlite> insert into t4 values('earth'); sqlite> select rowid, * from t4; earth|earth sqlite> create table t5(rowid integer primary key) without rowid; sqlite> insert into t5 values('mars'); sqlite> select rowid, * from t5; mars|mars _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
