O,
So you are pointing to this section
then that column is used internally as the actual key of the B-Tree for the table
and this
The B-Tree key for a row can always be accessed using one of the special names
"ROWID", "OID", or "_ROWID_".
But does this mean that SQLite will use that Key for where statements when not
referenced by column ROWID, OID or _ROWID_
What I mean is if you have a table called Product_Description with a column named
Product_ID which is INTEGER PRIMARY KEY and another table called ORDERS which has a
column Product_ID (with an separate index) That SQLite will use the hidden index when
this query is run
Select * , Product_Description.ProductName from ORDERS , Product_Description where
ORDERS.Product_ID = Product_Description.Product_ID
or would you have to do this
Select * , Product_Description.ProductName from ORDERS , Product_Description where
ORDERS.Product_ID = Product_Description.ROWID
regards
Greg
----- Original Message -----
From: Williams, Ken
To: 'Greg Obleshchuk' ; [EMAIL PROTECTED]
Sent: Tuesday, February 10, 2004 9:06 AM
Subject: RE: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index
-----Original Message-----
From: Greg Obleshchuk [mailto:[EMAIL PROTECTED]
Sent: Monday, February 09, 2004 4:00 PM
To: Williams, Ken; [EMAIL PROTECTED]
Subject: Re: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index
Hi Ken,
>Every sqlite table has an (hidden) index for its OID. When you create a
>column as INTEGER PRIMARY KEY, the index for that column will simply be the
>OID index. So creating another one is unnecessary.
Not that I disagree with you but where are you getting this information from? I
just want to do some reading on it.
It's this paragraph from the documentation that's been cited already in this thread:
----------------------
Specifying a PRIMARY KEY normally just creates a UNIQUE index on the primary key.
However, if primary key is on a single column that has datatype INTEGER, then that
column is used internally as the actual key of the B-Tree for the table. This means
that the column may only hold unique integer values. (Except for this one case, SQLite
ignores the datatype specification of columns and allows any kind of data to be put in
a column regardless of its declared datatype.) If a table does not have an INTEGER
PRIMARY KEY column, then the B-Tree key will be a automatically generated integer. The
B-Tree key for a row can always be accessed using one of the special names "ROWID",
"OID", or "_ROWID_". This is true regardless of whether or not there is an INTEGER
PRIMARY KEY.
----------------------
-Ken
