Relational Constructs   «Prev  Next»

Lesson 13 Concatenated primary keys
Objective Define concatenated primary keys.

Concatenated Primary Keys in Database Table

Concatenated primary key: Also known as composite primary key is a combination of two or more column values used to define a key in a table.
Primary-key columns were discussed in the preceding lesson.
Primary-key columns contain values that uniquely identify a record within a table.
Sometimes a single column does not contain sufficient information to distinguish one record from every other record in the table.
Consider the Line Item table:

Line Item
OrderNo CDNo Quantity SellingPrice Discount LineCost Shipped

Unable to create unique Record

No single column contains enough information to set a record apart from every other record in the table, though OrderNo and CDNo are the most likely prospects. The problem is, an order can contain more than one CD and a CD can be part of more than one order.
It is possible to create a CDOrderID column, but there is an easier solution:

Create Concatenated Primary key

Solution: Create a concatenated primary key.
You should create a concatenated primary key based on the existing columns in the database table.
A concatenated primary key is a primary key made up of two or more columns. In the case of the Line Item table, each record is uniquely identified by the combined values of
  1. the OrderNo and
  2. CDNo
which are columns located at the extreme left of the table. We are assuming a particular CD will not occur more than once in an order.

Two Additional Rules

Concatenated primary keys need to follow two additional rules:
  1. They should contain the smallest number of columns required to uniquely identify each record.
  2. The columns should not contain meaningful information. You may need to break this rule on occasion, but those circumstances are very rare.

Combine Two Table Columns

Some tables have no single column in which the values never duplicate. As an example, look at the sample order items table in Figure 2-1 below. Because there is more than one item on an order and because the same item can appear on more than one order, order numbers are repeated. Therefore, neither column by itself can serve as the primary key of the table. The combination of an order number and an item number, however, is unique. We can therefore concatenate the two columns that form the table's primary key. One could also concatenate all three columns in the table and still ensure a unique primary key. However, the quantity column is not necessary to ensure uniqueness and therefore should not be used.

Figure 2-1: Combining 2 columns is required in order to provide a unique identifier to act as a primary key.

We now have some additional properties of a good primary key:
  1. A concatenated primary key should be made up of the smallest number of columns necessary to ensure the uniqueness of the primary key.
  2. Whenever possible, the columns used in a concatenated primary key should be meaningless identifiers.
The next lesson describes all-key relations [1].

Primary Key - Quiz

Before moving on to the next lesson, click the Quiz link below to check your understanding of key columns and primary keys.
Primary Key - Quiz
[1]All-key relation: An All-key relation is a relation in which every field is a member of the relation's primary key.