Re: Non-Unique Index For An SQL Table File

[CRPence <CRPbottle@xxxxxxxxx>]

RWMunday wrote:
> Our prime directive in creating files at the client site is to
> use SQL Tables and SQL Indexes.  I was presented with a change to
> some programs which made it necessary for me to have a key on an
> SQL Table originally built without a key.  A separate SQL Index
> over the SQL table works great and prevents duplicate key errors.

  From the subject and the inquiry, I infer that the "prevents 
duplicate key errors" comment about the INDEX is meant to imply that 
the INDEX "allows duplicate key values, as is desired."  Just to be 
clear that "errors"<>"problems".  ??

> For that reason I cannot key the original file.

  Not sure what is meant by "that reason".  See next comment.

>  I have found no viable option for specifying a non-unique key
> inside of an SQL table.
>
> Is there a way to build a keyed table with a non-unique key? 

  SQL has no concept of a keyed TABLE.  There is no equivalent in 
SQL, to a DDS keyed-physical database *FILE object.

> I tried IGNORE_DUP_KEY without success.  There has to be a
> combination which works within the SQL table.  My key consists 
> of two fields in which identical data can appear in the file
> multiple times.

  Barring use of DDS physical and DDS logical files, the non-SQL 
programs will need to refer to the non-unique SQL INDEX, instead of 
referring to the TABLE.  Those non-SQL programs will need to use the 
"separate SQL Index over the SQL table [that] works great", in order 
to access the data in by the key\order defined in the keyed logical 
database *FILE [that implements an SQL INDEX].

  As SQL has no concept of a keyed TABLE or query of an INDEX, the 
order of a set of data is instead effected by an ORDER BY clause for 
the SELECT.  The run-time environment establishes the collation 
rules that are applied to the retrieved data for each SELECT 
statement that includes an ORDER BY request.

Regards, Chuck