Hello,
A few days ago I posted a question and I haven't seen any comments so far. I'm really curious about ROWID's volatility. How can I make sure that ROWIDs do not get re-initialized? I'm posting the message once again hoping that someone will explain how I should properly use ROWIDs.
Thanks again. Best regards,
-- Tito
Hi Tito,
AFAIK the ROWID is used internally as a means to organize the individual rows of a table in storage. Basically it is an integer, which is different between different rows.
However, if a table features an INTEGER PRIMARY KEY column (which obviously is different between different rows too), the database engine might use that value instead if an internally only handled value, thereby saving one stage of indirection.
Of course, when writing such a table into an external format and rereading it, these values shall not change - as it is with all values saved via ".dump" and reread (if they would, nobody would ".dump" call a backup utility, but "obfuscator" instead :)
I even think referencing the ROWID, even when possible, is IMHO a sign of bad table layout; why would you even want it when you could easily reference an "official" SQL column, without any tradebacks. (I even think that exposing the ROWID of a column via SQL is a bad thing(tm), but then I don't have to use it.)
- if I declare ROWID as INTEGER PRIMARY KEY in the CREATE TABLE statement, would that be enough to guarantee that the ROWID is safely dumped and reimported?
> [ ... ] > - If this is the case I guess I would have to create my own unique > column (i.e. MyUniqueUID and type INTEGER PRIMARY KEY, right?)
If I understand you right: you define a column named "ROWID"? Why would you do that? Just call it ID, and you are better off (and even save 3 keystrokes)
- The part that confuses me about Dr. Hipp's statement is this: 'If you use an INTEGER PRIMARY KEY, the ".dump" command will preserve the values and your references will not be broken by the export.': this is the reason I posted my first question above...
When importing a table from the .dump output, the database engine has no chance to guess what ROWID has been used prior to ".dump". Therefore, it cannot guarantee accessing the same RowID to the same row again. Consider this example:
create table a (i1, i2); insert into a values (1, 2); insert into a values (1, 3); insert into a values (1, 4); delete from a where i2 = 3; .dump
This creates the following output:
BEGIN TRANSACTION; create table a (i1, i2); INSERT INTO a VALUES(1,2); INSERT INTO a VALUES(1,4); COMMIT;
If this is all information the DB engine gets when reconstruction the table, it *cannot* know which ROWIDs have been assigned to each row.
If you use an INTEGER PRIMARY KEY the situatoin is different: the DB engine does use the appr. value of this column as the ROWID, knowing that it will be unique and therefore meets all requirements for an ROWID. It doesn't have to assign ROWIDs on its own.
But anyway: As far as I am concerned, I would want to see this ROWID stuff in the tips, tricks, and optimizations section of the documentation (and ROWID not accessible via the ROWID/OID/_ROWID_ columns.)
Any comments?
Thanks!
-- Tito
Hope it helped,
/eno
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
