Non unique index as primary index

[anandknr]

Hello all,

I am writing a query to list all tables with no unique and primary index defined.

define var havePrimary as logical.
define var haveUnique as logical.

for each _file where _file._tbl-type = "T":
    assign haveUnique = can-find(first _Index where _index._file-recid = recid(_file)
                                      and _Index._unique)
           havePrimary = can-find (first _index where recid( _index ) = _file._prime-index).
   
    disp haveUnique havePrimary.
end.

Strangely, I couldnt find a single (user added ) table with havePrimary as false. So my question is, Can we have a table with two indexes (A and B), A being unique and B being non unique but primary??

 

[TomBascom]

Yes.

 

[Rajat Sharma]

If we don't create primary index on any table (we should always create at least one primary index per table) then progress automatically create primary index of recid's and index data structure contains all recid's (row identifiers).

Thanks & Regards!
Rajat.

 

[TheMadDBA]

Rajat - that is what happens when you don't define ANY index on a Progress table.

Primary is essentially meaningless in Progress and doesn't have the same meaning as some other databases (Primary Unique Key)... basically it means default index in Progress.

 

[Rajat Sharma]

I agree, MadDBA.
I have described that because anandknr said that: "Strangely, I couldn't find a single (user added) table with have Primary as false".

Thanks & Regards!
Rajat.

 

[TomBascom]

A "default index" is what you have described.

It is very possible to have indexes which are primary but non-unique.

"Primary" is not a magic attribute that makes one index better than another. It is used as a tie-breaker in the index selection algorithm and as the default index when dumping a table. That's really all that it means.

FWIW and IMHO the designation of an index as "primary" is not usually well thought out by the application designer. And I speak as someone who might, perhaps, be responsible for a certain number of bad decisions out there in the wild...

 

[TheMadDBA]

Indeed... it would be nice if Progress would just rename Primary as Default and save a lot of grief/misunderstanding.

I am sure they will get right to that after they add that cost based optimizer to the 4GL