WITHOUT ROWID tables with a ROWID

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

WITHOUT ROWID tables with a ROWID

Tom Bassel
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WITHOUT ROWID tables with a ROWID

J. King-3
On 2019-02-21 15:02:23, "Tom Bassel" <[hidden email]> wrote:

>Are tables t3, t4, and t5 below working as designed?
>
Yes. See <https://sqlite.org/lang_createtable.html#rowid>:

 > The rowid value can be accessed using one of the special
case-independent names "rowid", "oid", or "_rowid_" in place of a column
name. 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.

--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WITHOUT ROWID tables with a ROWID

David Raymond
In reply to this post by Tom Bassel
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:[hidden email]] On Behalf Of Tom Bassel
Sent: Thursday, February 21, 2019 3:02 PM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] WITHOUT ROWID tables with a ROWID

Hick Gunter
In reply to this post by Tom Bassel
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:[hidden email]] On Behalf Of Tom Bassel
Sent: Donnerstag, 21. Februar 2019 21:02
To: [hidden email]
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users