Re: Primary keys don't work?

From: Jan Doggen <j.doggen_at_qsa.nl>
Date: Thu, 20 Sep 2001 10:01:11 GMT
Message-ID: <H7jq7.1199677$ef.36899479@Flipper>

New facts:

I cannot determine my own SQL query, because it is a Delphi TTable that opens.
We just found out that the SQL query issued by the BDE is ORDER BY the wrong index!!! We created the table like this:

CREATE TABLE TABLES (

  Tbl_ID number(10,0) NOT NULL ,
  Tbl_Source_Module_ID number(10,0) NULL ,
  Tbl_SourceTableName varchar2(255) NULL ,
  Tbl_TargetTableName varchar2(255) NULL ,
  Tbl_Active number(1,0) DEFAULT 1 NOT NULL,
  Tbl_Created date NULL ,
  Tbl_Changed date NULL ,

PRIMARY KEY (Tbl_ID)
)
followed by:
CREATE INDEX BySource ON TABLES (Tbl_Source_Module_ID, Tbl_SourceTableName) followed by:
CREATE UNIQUE INDEX ByTarget ON TABLES (Tbl_TargetTableName)

And it now turns out the the last index is used instead of the primary key!

How come???

Jan

C. R. Soza <crsoza_at_hotmail.com> wrote in message news:c18ea449.0109192316.1dd88a6e_at_posting.google.com...
> If you are only selecting the column which is the PK, then the result
> could be fetched purely by doing an index read, hence it will be
> sorted. However, as the other reader said, the records in the table
> will not be sorted in the order of the PK. Try using an order by or
> ensure that you use the PK whilst doing your select.
Received on Thu Sep 20 2001 - 05:01:11 CDT