The magic aliasing of rowid to the internal "record number" of a rowid table only works for a single row declared as exactly INTEGER PRIMARY KEY and in a rowid table.
If a table contains a field name rowid that has any different definition (even INT PRIMARY KEY), it will be just an ordinary column; for rowid tables, the internal record number can stillbe accessed via the 'oid' and '_rowid_' names. T1: rowid = record number T2: rowid not declared, record number does not exist T3: rowid is the key to a clustered index, has TEXT affinity; there is no record number T4: rowid is an ordinary field, use oid to access the record number T5: rowid is the key to a clustered index, has INTEGER affinity; there is no record number -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Tom Bassel Sent: Donnerstag, 21. Februar 2019 21:02 To: sqlite-users@mailinglists.sqlite.org Subject: [EXTERNAL] [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; insert sqlite> into t2 values('sun'); select rowid, * from t2; Error: no such column: rowid sqlite> create table t3(rowid text primary key) without rowid; insert sqlite> into t3 values('moon'); select rowid, * from t3; moon|moon sqlite> create table t4(rowid text primary key); insert into t4 sqlite> values('earth'); select rowid, * from t4; earth|earth sqlite> create table t5(rowid integer primary key) without rowid; insert sqlite> into t5 values('mars'); 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 ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
