Composite Primary Keys in Relational Table

In the preceding lesson we discussed primary-key columns, which contain values that uniquely identify a record^[1] within a table. Sometimes a single column does not contain enough data to distinguish a record from all the other records in a table.
Here is the Line Item table:

The columns in the table do not contain enough information to set a record apart from every other record in the table.
The columns OrderNo and CDNo are the best columns to achieve uniqueness.
The problem is,

1. an order can contain more than one CD and
2. 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 a composite primary key based on the existing table columns OrderNo and CDNo. A composite 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 the OrderNo and CDNo columns located at the extreme left of the table.
We are assuming a particular CD will not occur more than once in an order.

A composite primary key in database design serves a critical role in ensuring data integrity and optimizing database performance. It is a type of primary key composed of two or more columns (fields) in a table that, together, uniquely identify a record within that table. The primary functions and purposes of implementing a composite primary key are as follows:

1. Unique Identification of Records: The foremost purpose of any primary key, including a composite one, is to uniquely identify each record in a database table. By combining multiple columns, a composite primary key ensures that the combination of values in these columns is unique across all records in the table. This uniqueness is essential for accurately referencing and accessing specific records.
2. Enforcement of Entity Integrity: Entity integrity is a fundamental principle in database design that stipulates that no primary key can have a null value. A composite primary key upholds this principle by ensuring that each component of the key has a non-null value, thereby maintaining the integrity of the database entities.
3. Facilitation of Relationships Between Tables: In relational databases, relationships between different tables are often established through primary keys. A composite primary key can represent a more complex relationship, where a combination of fields, rather than a single field, defines the connection between tables. This is particularly useful in representing many-to-many relationships and in junction tables where multiple foreign keys are combined to form a primary key.
4. Normalization Support: The use of composite primary keys is frequently aligned with the principles of normalization, a process designed to reduce data redundancy and improve data integrity. By allowing for the combination of multiple fields, composite primary keys facilitate the design of more normalized database schemas, particularly in situations where no single column uniquely identifies a record.
5. Reflecting Real-World Complexity: Often, real-world entities cannot be uniquely identified by a single attribute. A composite primary key enables the database to more accurately model complex real-world scenarios where the uniqueness of an entity is defined by a combination of attributes.
6. Performance Optimization in Queries: Composite primary keys can lead to more efficient database queries. When used appropriately, they can speed up the retrieval of data by allowing queries to filter on multiple columns simultaneously. This can be particularly beneficial in large databases with extensive data.
7. Indexing and Query Optimization: Most database systems automatically create an index on the primary key. With composite primary keys, this index is a composite index. Such an index can be very efficient for queries that involve all or the leading parts of the composite key, thereby improving query performance.

In conclusion, composite primary keys are a powerful tool in database design, offering unique identification of records, enhancing data integrity, supporting complex relationships, aiding in normalization, modeling real-world complexity, and optimizing query performance. However, their implementation must be carefully considered and managed to avoid complexity and ensure that they align with the specific requirements and constraints of the database system and its applications.

A composite primary key is a combination of 2 or more columns used to create a unique primary key. The term key can refer to a unique key, which can be designated and used as primary key^[2] or non-unique key, which can be used as a search key for an index for a distinct view. For example, one could consider a date as a composite consisting of the data elements 1) year, 2) month and 3) day, or a data element if made from the number of days since a reference date. Both require another data element to properly identify any given day and provide uniqueness. In this case, a data element identifying which calendar is being used, which determines which day is used as a reference date. Therefore, the fully defined natural primary key for a date requires the calendar date in either form and the calendar id or code, and is consequently composite.

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

The next lesson describes all-key relations. ^[3]

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]record: A group of related data items treated as a single unit by an application is called a record.

[2]primary key: A field (or combination of fields) that uniquely identifies a record in a table.

[3]All-key relation: A relation in which every column in the table is a member of the relation's primary key.