Defining Primary Keys

  

Virtual Machine  

The primary key definition for a file must always comprise one or more contiguous variable names, starting with the first name defined within the file definition. In the absence of alternate keys, record access is always in primary key sequence.

Note: This section applies to SQL databases (not PROISAM).

For example, in a Customer file containing the names and addresses of a company's customers, the primary key is typically that customers number, (a number that uniquely identifies a customer).  Records are accessed in ascending order of customer number.  This is the most efficient way when retrieving the details of a particular customer by customer number because PROIV is able to directly access the required record.  It is not very efficient, however, when retrieving the details of a particular customer by name. This is because each customer's details (starting with customer number 1) have to be accessed in turn and the customer name checked against the required name, until a match is found.

Alternate key definitions enable other keys to be specified that do not have to start with the first variable name defined or to comprise contiguous names.  In the Customer file example, customer name could be made an alternate key, and then be used to directly access the required customer, If the customer's name is unique. Refer to Defining Duplicate Keys for more information.

Comment on this topic

Topic ID: 720061