Board index » off-topic » How to use Primary keys in BDE/Paradox

How to use Primary keys in BDE/Paradox


2005-11-19 07:14:34 AM
off-topic18
If a table has 3 primary keys, when I'm trying to .Locate a record, must I
use all three fields in my locate-statement in order for the BDE to use the
primary key to speed up the .Locate?
 
 

Re:How to use Primary keys in BDE/Paradox

By definition a table can only have one primary key but that key can be
a composite key composed of multiple fields. If you want the best
performance use FindKey or SetRange, not Locate. The index will be used
if you use the first N fields of the primary key.
--
Bill Todd (TeamB)
 

Re:How to use Primary keys in BDE/Paradox

Tom wrote:
Quote
1. By your "if you use the first N fields of the primary key" do you
mean that I don't have to use ALL the fields in the primary key when
using FindKey and SetRange?
Suppose you have a composite primary key consisting of last name, first
name and middle name. You can use just last name, or last_name and
first name or all three. However you can not use last name and middle
name or first name or first name and middle name.
Normally you would want to use all three fields with FindKey since it
locates a single record and there could be multiple records that match
if you only use part of the key.
Quote

2. When doing a FindKey or SetRange, if I want the BDE to take
advantage of a secondary index, do I have to explictily set the open
table's IndexName, or is the engine smart enough to find it?
You have to set the IndexName, however, you are guaranteed that the
index will be used. There is no guarantee with Locate. You can change
the IndexName while the table is open.
Quote

--
Bill Todd (TeamB)
 

{smallsort}

Re:How to use Primary keys in BDE/Paradox

1. By your "if you use the first N fields of the primary key" do you mean
that I don't have to use ALL the fields in the primary key when using
FindKey and SetRange?
2. When doing a FindKey or SetRange, if I want the BDE to take advantage of
a secondary index, do I have to explictily set the open table's IndexName,
or is the engine smart enough to find it?
Thanks, Bill, for your help
 

Re:How to use Primary keys in BDE/Paradox

From the SetRange help I found this line:
With Paradox or dBASE tables, SetRange works only on indexed fields
but I'm still wondering if I have to open the table with the proper
IndexName or not...