• Welcome, Guest. Please login.
 
July 08, 2020, 01:31:01 am

News:

Welcome to the SQLitening support forums!


Further clarification for ROWID

Started by Marty Francom, January 20, 2010, 02:53:15 pm

Previous topic - Next topic

Marty Francom

The ROWID is essentially a record number for a specific record.  Correct?

For a particular RECORD the ROWID will be the same no matter what index is being used to look up that record.    Is that correct?

If records are deleted will their ROWID be reused by a different record or will all Records be ressign a different ROWID when a record is deleted?

What I am getting at is   Can I depend on the ROWID  always  being associated with a specific record?    Is it safe for me to store the ROWID of a RECORD in one Table  in the Record of another Tablet  and that the other Record with always be able to retriev the record it needs by getting the record using the ROWID it had stored for that Record.    Is this thinking correct?

Does vacumming  the database to remove deletes effect the Record's RowID?

If I want to get a record by its ROWID woud this be the correct syntax?

SQL1 = "SELECT ROWID, * FROM Refills WHERE [" rowid  "] " & direction & " '" & sTxt & "' ORDER BY  [" rwoid "]"




Fred Meier

QuoteThe ROWID is essentially a record number for a specific record.  Correct?

Yes

QuoteFor a particular RECORD the ROWID will be the same no matter what index is being used to look up that record.    Is that correct?

Yes

QuoteIf records are deleted will their ROWID be reused by a different record or will all Records be ressign a different ROWID when a record is deleted?

Normally a new higher number will be assigned to the row being inserted, there are exceptions.  See http://www.sqlite.org/autoinc.html

QuoteWhat I am getting at is   Can I depend on the ROWID  always  being associated with a specific record?    Is it safe for me to store the ROWID of a RECORD in one Table  in the Record of another Tablet  and that the other Record with always be able to retriev the record it needs by getting the record using the ROWID it had stored for that Record.    Is this thinking correct?

Yes, but you probably want an alias column name with Integer Primary Key.

QuoteDoes vacumming  the database to remove deletes effect the Record's RowID?

The Vacuum command will renumber all RowIDs that don't have an alias column name with Integer Primary Key. The Pragma Incremental Vacuum does not.

QuoteIf I want to get a record by its ROWID woud this be the correct syntax?
SQL1 = "SELECT ROWID, * FROM Refills WHERE [" rowid  "] " & direction & " '" & sTxt & "' ORDER BY  [" rwoid "]"

No, it would be as follows.  Makes no sense to order by RowID nor where on direction cause there is only one row with that RowID.
Quote"Select * from Refills where RowID=" format$(llSavedRowID)


Marty Francom

Fred,  Thank you that was helpful.

I am still not quite sure how to set a field with  "Integer Primary Key"  properties.   
Can I use "SQLite-Expert" to do this  (the database already exists and I would rather
not have to initialize a new one.

SQLiteExpert allows me to set that field with these properties:
    Primary
    Autoincrement
    Unique
I set all these on,  I will try that and see if it works as I expect.

Marty Francom

Well,  that didn't work.   Now, after making that one change the database crashes.   I am getting the message:

-13 = Invalid column name or number

Apparently I am still not understanding


Bern Ertl

Quote from: Marty Francom on January 20, 2010, 06:30:43 pmI am still not quite sure how to set a field with  "Integer Primary Key"  properties.


You need to specify that when you create/define the table:

http://www.sqlite.org/lang_createtable.html#rowid