What is an atomic key in DB2?

Courtesy of IBM

DB2 is a relational database management system produced by IBM. If you have used Microsoft Access to set up a database, you have used a relational database. DB2 is available for the PC and competes with Access in that market. It also competes with the more successful Oracle RDMS on Unix systems.

DB2’s concept of “atomic key” is a fundamental part of its implementation of the relational model for its database system.

Relational databases

A relational database has specific characteristics. It breaks records of data down into sections to remove repeating groups. A repeating group is usually found where information contains header and line information. For example, An invoice contains many line items. The invoice number is relevant to each lone of the sale. However, it is not necessary to put the header information of the invoice, like invoice number, date, delivery address and VAT number on every single line of the invoice when they are stored. Instead an ID for the invoice header links each line to the header so they can be matched together again to reassemble the invoice from its composite parts.


Data in the relation database is split into tables. In the model of the database these tables are represented as “entities.” Entities have “attributes” which equate to the column headings in the table. A row in the table is called an “instance.” The key is an attribute that can uniquely identify each instance of the entity. This usually means an ID number. In the invoice example, the Invoice Header entity has a key formed by an ID field and the Invoice Line entity has its own. These are called “primary keys.” When the Invoice Header ID is included as an attribute of the Invoice Line entity, it forms a link between the two and is called a “foreign key” in the Invoice Line entity.

Atomic key

The term “atomic key” is a rarely used term in database design. Its antonym, “composite key” is much better known. Usually an atomic key is just called a key. In the case of the Invoice example, the Invoice Header ID requires only one attribute to provide uniqueness and thus become the primary key. When a database designer cannot find a real world attribute that uniquely identifies each instance, he creates one, as is the case with Invoice Header ID. This is a computer-controlled sequence number that exists solely to identify each record and will never be seen in the real world, like Invoice Number and Line Number will. An atomic key is a primary key formed from one single attribute.

Composite key

The opposite of atomic key is a composite key. This is a key that is created by referring to two or more attributes. An example might be found with a shop. You could say “I’m going to BHS,” but which BHS? “The BHS in Nottingham.” In that case the shop is uniquely identified by its name and location which are two attributes.