What do you think about this Primary Key situation

[Dan Ridinger]

Hello Das,

Here is a link to how rowid’s work with the declaration of an integer primary key. Since Rowid is the primary key of the table. 

https://www.sqlite.org/rowidtable.html <https://www.sqlite.org/rowidtable.html>

Dan Ridinger

> On May 5, 2020, at 8:11 AM, Das Goravani <das at goravani.com> wrote:
> 
> 
> Hello
> 
> In SQLite primary key fields that are numeric are supposed to be alias’s for the ROWID which is an implicit column SQLite creates and maintains which is a unique row ID 
> 
> But my primary key fields are not bringing up this value when queried and selected and fetched
> 
> They bring up zero
> 
> But if I check on the ROW ID just after an insert, there is of course a ROW ID assigned
> 
> I can place that ROW ID that I have to fetch after the insert INTO the RSN field and save it back
> 
> Seems odd that they are not acting like ALIAS’s and when I fetch the records to bring them back in to a list they should have that ROW ID showing if they are alias’s but they do NOT show it, unless I save it there
> 
> I would have to have an update right after my insert to save the ROW ID which is fetched AFTER insert, to place it into the RSN field I would have to have an update after insert
> 
> Doesn’t that seem wrong?  To have to go like this:
> 
> INSERT new record
> Fetch last row id
> Place that into RSN field
> UPDATE to save that value in row
> 
> All within the INSERT statement and every time a record is saved.. INSERT FETCH UPDATE
> 
> IS THAT normal?  I mean within the realm of acceptable ? 
> 
> You can’t know the ROW ID until AFTER the insert.. you can fetch it and I do.. I can then UPDATE that into the record.. I’m going to do that now.. write that code.. because in my eyes it’s the only way to have a unique number in that field.. or the slickest way.. 
> 
> I could recreate my database and put in auto increment constraints on the RSN columns, I don’t want to do that because I will face data mapping issues that exist with SQLite.. just uncertainties.. that is..
> 
> The fields set as primary key are supposed to be alias’s for the ROW ID and I thought this meant that if you queried rows and brought them into a list then in that field they would have the ROW ID as a value in the field.. but instead it brings in zeroes.  So I’m thinking of saving the value into the field myself basically.
> 
> What do you think about this situation? 
> 
> Das
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com