Lesson 4 - Key and Index Properties

Top  Previous  Next

 

Overview

Keys are a mechanism for organizing or sorting records in a data table based upon the contents of a column or a combination of columns. Keys are used to quickly locate related records in a relational database and to allow data to be presented in a particular sort sequence.

To define keys for a data table:

1. From the Quick View Keys window, press the Add button on the Keys toolbar. You can also press the INSERT key in this window.

When adding columns to a new table, the list boxes on this screen are initially empty.

2.

Double-clicking on any table in the DCT Explorer opens the Entity Browser. From this window, select the Columns folder, and press the Add button on the Entity Browser toolbar, or press the INSERT key.

When adding keys to a new table, the list box on this screen is initially empty.

Information on the characteristics of a key are specified in the Key Properties window. A brief description of important properties follows:

Label

Enter the name (label) to be used when referring to this key from within the application. As with all Clarion labels, the key name may consist of alphabetic characters, numbers, the underscore character (_), and the colon (:). Labels may not begin with a number or a colon, may not contain spaces, and are not case sensitive.

Description

Descriptions are a string of characters used to fully describe the type of information stored in this column. If the key name specified is somewhat cryptic, it is a good idea to enter a meaningful description. Information entered in the Description column is treated as a comment.

External Name

Enter the name of this key as stored in the data table. This option is table driver dependent, and is usually implemented in SQL based databases.

 

Type

The following radio buttons in the Key Properties window allow you to specify the type of key being defined:

 

Row Key

A key is a sort sequence on a column or group of columns that is maintained dynamically by the program. As the records in the data table are modified, the sort sequence is automatically adjusted.

 

Static Index

An index is a sort sequence that reflects the contents of the data table at a particular time. Indexes are not dynamically maintained, they are built only when specified. Indexes are useful for sort sequences that are used on an infrequent basis like month end or year end reporting.

 

Runtime Index

Dynamic indexes allow selected sort sequences to be chosen when the program executes. A dynamic index is defined without components. At runtime, the BUILD statement is issued to create the dynamic index. For more information on the BUILD statement, see the Language Reference manual.

Order

Order adds a sort order definition to a template symbol (%ORDER) in a similar manner as a KEY without affecting the file declaration.

Orders specified in a dictionary are not yet supported in the included templates. Some third party templates may use this option.

 

Attributes

Before saving your key definition, let’s review some other important options.

Checkboxes (and a single entry option) are found in the Attributes section to determine certain key attributes:

Require Unique Value

Selecting this option determines that the column used for the key cannot contain duplicate values.

Primary Key

Checking this option specifies that the key components of this key uniquely identify each record. There can only be one primary key per table. A primary key also refers to a key in the Parent table (on the Parent side of a Parent/Child relationship) containing the common column.

Auto Number

Selecting this option specifies that the program should automatically assign a numeric value to the column within the key when records are added to the data table.

Case Sensitive

If a key is case sensitive, upper and lower case characters are sorted separately. Upper case characters are placed into the sort sequence before any lower case characters. If this option is not selected, lower case characters are sorted as if they were upper case.

Exclude Empty Keys

This option, if selected, specifies that if no value is specified for the key column components, no entry for the record will be placed in the sort sequence of the key.

The Key Columns tab option prompts for the columns that will be part of the key. The list box displays the available columns. If you are not creating a Runtime Index, you must select at least one column in order to be able to save the new key definition!

3. Press the Add button to select the column components that will be part of this key.

4. When you are finished setting key options and naming your column components, press the Save and Close button in either the Entity Browser or DCT Explorer to write your new key to the dictionary.

 

Single and Composite Keys

A key may have a single sort component, or may use multiple components when needed.

 

 

Composite keys (keys containing multiple columns) are created by pressing the Select button for each column that will be part of the key. When you have chosen more than one column for a key and you exit the Select a Column dialog, you will have the opportunity to move columns within a key relative to one another by using the mouse to drag and drop.

 

Next: Lesson 5 - Adding Relationships