Concatenated (Composite) Primary Keys

A concatenated primary key, also called a composite primary key, is a primary key made up of two or more columns in the same table. Instead of relying on a single column to uniquely identify each row, the database uses the combination of values across several columns.

Recall that a primary key must uniquely identify every row and must never contain NULL values. In many tables, a single column such as OrderNo or CustomerID is sufficient. However, there are important cases where no single column can guarantee uniqueness. In those situations, a concatenated primary key is an appropriate design choice.

When a Single Column Is Not Enough

Consider a Line Item table that records the items on customer orders:

Line Item
OrderNo	CDNo	Quantity	SellingPrice	Discount	LineCost	Shipped

In this table, OrderNo identifies a customer order, and CDNo identifies the CD (or product) being sold. Neither column by itself can uniquely identify a row:

• An order can contain multiple CDs, so the same OrderNo value appears on several rows.
• The same CD can appear on many different orders, so the same CDNo value appears on several rows.

Because both columns contain duplicates, neither column alone can serve as the primary key for the table. We need a key that treats OrderNo and CDNo as a pair.

A concatenated primary key uses the existing columns in the table to form a unique identifier. In the Line Item table, each row is uniquely identified by the combination of:

1. OrderNo
2. CDNo

As long as the same CD does not appear more than once on the same order, the pair (OrderNo, CDNo) is unique for every row. Together, those two columns can safely serve as the primary key.

Data Analysis for DB Design

Example: Creating a Concatenated Primary Key in SQL

Here is a simplified version of the LineItem table definition using a concatenated primary key:

CREATE TABLE LineItem (
    OrderNo      INTEGER    NOT NULL,
    CDNo         INTEGER    NOT NULL,
    Quantity     INTEGER    NOT NULL,
    SellingPrice NUMERIC(8,2) NOT NULL,
    Discount     NUMERIC(5,2),
    LineCost     NUMERIC(8,2),
    Shipped      CHAR(1)    NOT NULL,
    CONSTRAINT pk_lineitem PRIMARY KEY (OrderNo, CDNo)
);

The PRIMARY KEY (OrderNo, CDNo) constraint tells the database to enforce two important rules:

1. The pair (OrderNo, CDNo) must be unique in the table.
2. Neither OrderNo nor CDNo may contain NULL values.

When you decide to use a concatenated primary key, keep these design rules in mind:

1. Use the smallest number of columns that ensure uniqueness.
   Only include the columns that are necessary to uniquely identify each row. In the Line Item example, OrderNo and CDNo are sufficient. Adding Quantity to the key would still produce unique rows, but it is unnecessary and would complicate foreign key relationships.
2. Prefer stable, meaningless identifiers.
   Whenever possible, the columns in a primary key—concatenated or not—should be stable over time and not encode business meaning that might change. Values such as names, addresses, or phone numbers are considered meaningful information and are often poor choices for primary keys because they change.
3. Avoid data that can be edited by end users as part of normal business.
   If users frequently update a column (for example, a customer name or product description), that column should not be part of the primary key.
4. Ensure the concatenation is unique and never NULL.
   The combined values across all key columns must uniquely identify each row, and none of the key columns may contain NULL.

In many modern designs, you will see a surrogate key (such as an auto-incrementing integer or GUID) used as the primary key. Even in those designs, concatenated keys still appear frequently in intersection tables that resolve many-to-many relationships (for example, StudentCourse(StudentID, CourseID)).

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.

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].

So far we have focused on practical design rules. Behind those rules is the formal language of relational theory. In that theory, a table is called a relation^[2]., a column is called an attribute, and a row is called a tuple.

The term tuple reflects the idea that a row is an ordered collection of attribute values. A relation with two attributes contains pairs of values (2-tuples), a relation with three attributes contains triples of values (3-tuples), and so on. Mathematicians use the general term n-tuple for these ordered sets.

In pure relational theory, relations do not impose an inherent ordering on either rows or columns. The order in which rows are stored or retrieved and the order in which columns are defined are both considered irrelevant. In practice, however, most database applications:

• Return rows in a predictable order, usually controlled by an ORDER BY clause.
• Present columns in a consistent order, typically the order in which they were created in the table definition.

The main takeaway for this lesson is that a concatenated primary key is still a primary key on a relation: it uniquely identifies tuples in that relation, just as a single-column primary key does.

Use a concatenated primary key when:

• No single column can uniquely identify each row.
• The combination of several existing columns is unique and will remain stable over time.
• You want to avoid embedding meaningful, user-editable data in the primary key.
• You are modeling an intersection table in a many-to-many relationship (for example, OrderLine or StudentCourse).

As you design your schema, always check that the key—single-column or concatenated—captures the business rule “each row represents one and only one thing” in a way the database can enforce.

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.

[2]Relation: Another word for table is a relation, where relations represent entities in database design.