Using more than one key field

 

You can use more than one key field to arrange records in a database file. The key fields do not have to use the same sequence.

For example, when you use two key fields, one field can use ascending sequence while the other can use descending sequence. Consider the following records.

Record Order Ordate Line Item Qtyord Extens 1 52218 063088 01 88682 425 031875 2 41834 062888 03 42111 30 020550 3 41834 062888 02 61132 4 021700 4 52218 063088 02 40001 62 021700 5 41834 062888 01 00623 50 025000

If the access path uses the Order field, then the Line field as the key fields, both in ascending sequence, the order of the records in the access path is.

Record Order Ordate Line Item Qtyord Extens 5 41834 062888 01 00623 50 025000 3 41834 062888 02 61132 4 021700 2 41834 062888 03 42111 30 020550 1 52218 063088 01 88682 425 031875 4 52218 063088 02 40001 62 021700

If the access path uses the key field Order in ascending sequence, then the Line field in descending sequence, the order of the records in the access path is.

Record Order Ordate Line Item Qtyord Extens 2 41834 062888 03 42111 30 020550 3 41834 062888 02 61132 4 021700 5 41834 062888 01 00623 50 025000 4 52218 063088 02 40001 62 021700 1 52218 063088 01 88682 425 031875

When a record has key fields whose contents are the same as the key field in another record in the same file, then the file is said to have records with duplicate key values. However, the duplication must occur for all key fields for a record if they are to be called duplicate key values. For example, if a record format has two key fields Order and Ordate, duplicate key values occur when the contents of both the Order and Ordate fields are the same in two or more records. These records have duplicate key values.

Order Ordate Line Item Qtyord Extens 41834 062888 03 42111 30 020550 41834 062888 02 61132 04 021700 41834 062888 01 00623 50 025000

Using the Line field as a third key field defines the file so that there are no duplicate keys.

(First key field) order (Second key field) ordate (Third key field) line Item Qtyord Extens 41834 062888 03 42111 30 020550 41834 062888 02 61132 04 021700 41834 062888 01 00623 50 025000

A logical file that has more than one record format can have records with duplicate key values, even though the record formats are based on different physical files. That is, even though the key values come from different record formats, they are considered duplicate key values.

 

Parent topic:

Using keyed sequence access paths for database files