About key fields

Several operations in ACL make use of key fields:

The term ‘key field’ can have a different meaning, and key fields can have a different function, depending on which operation you are performing. Key fields in ACL also differ somewhat from the typical definition of a key field in relational database terminology.

Key fields when joining, relating, or merging

Joining, relating, and merging in ACL are all data combining operations involving two or more tables. The term ‘key field’, in this context, means the common field in two tables being combined from which values are compared and matched – or in the case of merging, compared and interfiled. Extracting and appending, another method for combining data in ACL, does not make use of key fields.

The first table you open when joining or merging becomes the primary table, and the key field you choose becomes the primary key field. The second table you open becomes the secondary table, and the key field you choose becomes the secondary key field. When you relate tables, primary is referred to as ‘parent’, and secondary is referred to as ‘child’.

You are free to choose whatever primary and secondary tables and key fields you want when combining data. ACL does not enforce any particular choice of field, although it does require that key field pairs have an identical data structure. Because data imported into ACL, or residing on an ACL Server, is stored in non-relational flat files, fields that may previously have functioned as primary keys, unique keys, foreign keys, or secondary keys in a relational database are not treated any differently from non-key fields. A primary key from a relational database, such as employee ID, only becomes a primary or parent key in ACL when you designate it as such. ACL also does not enforce uniqueness in the key fields you designate. As a user making a decision about how to construct a join or a relation in ACL, you may need to know which fields were primary or unique keys in a source database, but ACL does not contain this information. The same is true of actual database tables that you access using an ACL database profile. ACL has no information about which fields are key fields in the database, and you may need to know this information yourself when constructing a database query.

Key fields when sorting or indexing

Sorting and indexing in ACL are single-table operations that impose a sequential order on a table. The term ‘key field’, in this context, means the field you specify, upon which the sorting or indexing is based, containing the values that are sorted or indexed. The sorting or indexing key field in ACL is equivalent to the ‘sort key’ or ‘index key’ in general computing or database terminology. Uniqueness is not enforced. In the case of nested sorting or indexing, a ‘primary’ sort or index key takes precedence over a ‘secondary’ sort or index key. Primary and secondary keys are established simply by the order in which you select them.

You are free to choose whatever key field you want when sorting or indexing data. ACL does not contain any information about fields that may have been sort or index keys in the original source data, although the values in those fields may still be in sequential order.

Related concepts
About joining tables
About relating tables
About merging tables
Using multiple key fields
About sorting
About indexing

(C) 2015 ACL Services Ltd. All Rights Reserved.