Every row of every table has a ROWID. The ROWID can be called "ROWID", "_ROWID_", and/or "OID". All three names refer to the same value and can be used interchangably. But if you declare a column with any of those names, the name refers to your declared column, not the actual ROWID. This is similar to how an local automatic variable will hide a global variable by the same name in C/C++.
If you declare a column to be an INTEGER PRIMARY KEY, then that column becomes another alias for the ROWID.
Examples:
Given a table of the following form:
CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
All of the following statements are equivalent:
SELECT rowid, y FROM t1; SELECT _rowid_, y FROM t1; SELECT oid, y FROM t1; SELECT x, y FROM t1;
As far as SQLite is concerned, "rowid", "_rowid_", "oid", and "x" are just different names for the same value.
These statements are also all equivalent:
INSERT INTO t1(rowid,y) VALUES(1,2); INSERT INTO t1(_rowid_,y) VALUES(1,2); INSERT INTO t1(oid,y) VALUES(1,2); INSERT INTO t1(x,y) VALUES(1,2);
Any place you can use the column "x" associated with table "t1", you can substitute "rowid", "_rowid_", or "oid" and get the same result.
When you do a "SELECT *", the results contain only columns that are explicitly declared in the CREATE TABLE statement. If you have declared an INTEGER PRIMARY KEY column, then the rowid will appear under that column name. If there is no INTEGER PRIMARY KEY, then the rowid will not be a part of the result. The ".dump" command works by doing a "SELECT *". So if you do not have an INTEGER PRIMARY KEY in your table declaration, the rowid will not be part of the saved data and will be lost when the table is reconstructed.
-- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
