What Is an Atomic Key in DB2?
IBM’s DB2 database software, as with other databases, uses keys to speed up finding data in tables. For example, a bank’s customer database might have a customer account number as a key; to retrieve an account record, a clerk needs only the account number. An atomic key is one composed of a single, simple data item that does not break down into meaningful parts.
Other People Are Reading
-
Database Tables
-
Relational databases, such as DB2, store information in separate groups called tables. The database for an online retailer, for example, may have tables for customers, catalog items and orders, among other topics. Each table consists of fields; a sample customer table might have fields such as a customer code, name, street address and credit information. The records in the table fill in the data in the fields, so one record may have “01500” for the customer code, “Sam Smith” for the name, “508 Main St.” for the street address and a credit rating of “Good.” The database designer usually designates one field as a key; the computer uses the key to locate records quickly.
Keys and Indexes
-
For large tables, it takes longer to find information if the computer has to read each record to locate the right one. Even on a fast computer, it takes several seconds to find the right record out of millions in a table. If the computer processes thousands of lookup requests, the time spent searching becomes excessive. DB2 and most other databases solve this problem by placing a table’s key data in a separate file called an index. A mathematical process organizes the index such that the computer locates a record by reading a few key items; the key record contains the exact location of the record in the main table.
-
Atomic Keys
-
A table may have no keys, just one or several. Keys may consist of a single field, combinations of fields and even parts of fields joined together. An order table, for example, may have a combined key consisting of an order number combined with a catalog number. Each field by itself is not enough to locate one record by itself; an order may have several items, and each catalog number may be on thousands of orders. An atomic key uses one field containing information that identifies a record. Order numbers, Social Security numbers and driver’s license numbers are all good examples of fields you can use for atomic keys.
Unique Keys
-
Database designers consider it good practice to give tables unique keys: those that identify a single record. Atomic keys are frequently but not necessarily unique. For a customer file, a Social Security number uniquely identifies each record, as no two people have the same Social Security number. A wish-list table, however, may have several records for a given customer; a Social Security number cannot find individual records. To make a unique key for a wish-list table, the designer must combine the Social Security number with another piece of data or create an identifier number field for the table.
-
References
- Photo Credit Thinkstock Images/Comstock/Getty Images
