Re: Primary keys don't work?

Jan Doggen wrote:
>
> New facts:
>
> 1) I cannot determine my own SQL query, because it is a Delphi TTable that
> opens.
>
> 2) 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.

I'm not trying to be offensive, but there is no guarantee in ANY relational database that data from a table will come back in a particular order. Certains tools or implementations may actually do this - but this is a feature of the tool - not a relational db

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."