Re: Primary keys don't work?

In article <3Cgq7.1195696$ef.36758473_at_Flipper>, "Jan says...
>
>
>????
>A Delphi TTable *does* show the records ordered automatically by primary
>key, by default. This goes for Paradox and MS-SQL tables, with which we've
>been working for years.
>Are you telling me that would not be the case with an Oracle database as
>backend?
>
>Jan
>

Unless you Delphi TTable's are adding an "order by pk" -- the order of the rows returned is not EVER assured -- even in MS SQL and Paradox, you just might not have seen it.

It is very easy to demonstrate:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t ( x int PRIMARY KEY ); Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( 1000 ); 1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( 1 ); 1 row created.

just to show that a primary key is in effect: ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( 1 ); insert into t values ( 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.SYS_C001007) violated

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from t;

         X

      1000
         1

In this case, the rows came out the order they went in....

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from t order by x;

         X

         1
      1000

That is the ONLY way to get rows to come out in SOME specific order in ANY relational database -- by using an order by...

and before you start thinking that rows come out in the order they went in:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:912210644860

will clear that up. databases store data in a heap, unless you use an order by the data is randomly returned and can vary from call to call (eg:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> update t set x = decode( x, 1000, 1, 1, 1000 );

2 rows updated.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from t;

         X

         1
      1000

the data now appears to come out in primary key order BUT ITS NOT -- its just coming out)

>
>Andrew Hardy <nobody_at_spam.from.news.AdvanticaTech.com> wrote in message
>news:9oauai$6rh$1_at_sun-cc204.lut.ac.uk...
>> A primary key does not imply the order in which rows are returned in SQL.
>>
>> To determine the order in which you want to return your data, use the
>ORDER
>> BY clause.
>>
>> Andy
>>
>> "Jan Doggen" <massoft_at_tref.nl> wrote in message
>> news:9oaq2j$4a0$1_at_reader05.wxs.nl...
>> > Hi everyone,
>> >
>> > in Delphi 4 we're creating and accessing tables through the BDE and
>TCP/IP
>> > connections to an Oracle 8 database.
>> > These tables have single-field primary keys, either created with
>> >
>> > > CREATE TABLE
>> > > ...
>> > > CONSTRAINT PK_INDX PRIMARY KEY (fldname)
>> > or
>> > > CREATE TABLE
>> > > followed by
>> > > ALTER TABLE ADD PRIMARY KEY(fldname)
>> >
>> > Sometimes the records are *not* ordered by their primary key:
>> > - if we use a Delphi TTable and jump to the last record it's not the
>last
>> ID
>> > - if we use Delphi SQL Explorer to look at the data, the records are not
>> > ordered by the primary key field
>> >
>> > The app is doing a lot of record appends, truncate tables etc.
>> > The app also runs on MS-SQL without problems, the code logic seems fine.
>> >
>> > What could be going on here?
>> > Is there such a thing as the index not being 'maintained' in Oracle?
>> >
>> > Thanks in advance for all suggestions
>> > Jan
>> >
>> >
>> >
>>
>>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp