Can individual key columns repeat?

Yes. The combination must identify a row even when individual values repeat.

Is a composite key the same as a composite index?

No. A key defines identity or uniqueness; an index is an access structure that may support enforcement or performance.

Should every table use one?

No. The choice follows the model's identity rules and practical requirements, rather than a preference for more or fewer columns.

AI Glossary / Data engineering and architecture / Beginner

Composite key

Definition

A composite key identifies a database record using a combination of two or more attributes. For example, an order number and a line number together may identify one order line. Neither component needs to be unique by itself. What matters is the uniqueness of the combination within the records and business rules being modeled.

What it means

Many identifiers are meaningful only within a context. Room 12 may exist in several buildings, while BuildingID plus RoomNumber identifies one room in a particular system. A composite candidate key is a minimal combination that can uniquely identify rows: removing a necessary component loses that property. A composite primary key is the candidate key selected as the table's main identifier. A combination with unnecessary extra attributes may be a superkey without being a minimal candidate key.

How it works

Designers first establish the identity rule, then express it through database constraints. In SQL, a primary key may list multiple columns; its combination must be unique and its components cannot be null. Other tables can refer to that combination through a matching composite foreign key. The database may use an index to enforce the constraint, but the key's logical meaning and the supporting access structure are different ideas. Exact constraint syntax and null handling for other kinds of uniqueness vary by database.

Three practical examples

An order-line table uses OrderID and LineNumber because line 1 can occur on many orders.
A membership table uses GroupID and PersonID when one person can belong to many groups but should appear only once in each group.
An AI evaluation table uses ExperimentID and CaseID when each test case has one recorded result per experiment. If repeated runs are allowed, a RunID or another identity component may also be needed.

Think of it this way

Think of a seat identified by both its row and seat number. Seat 4 alone is ambiguous when every row has a seat 4. The analogy is limited because database keys also support formal constraints and relationships, and real identity rules can change when the model expands to include different venues, dates or performances.

Worked example

Consider order lines (OrderID 41, LineNumber 1, quantity 2), (41, 2, quantity 5) and (42, 1, quantity 3). The pair (OrderID, LineNumber) uniquely identifies all three rows. OrderID alone repeats at 41, while LineNumber alone repeats at 1. Adding another row with pair (41, 1) violates the proposed key even if its quantity differs. Adding (42, 2) does not. If the business later permits historical versions of a line in the same table, the existing key is no longer sufficient for that expanded representation; the design needs a deliberate versioning decision.

Illustrative case study

Fictional case: Willow Learning combines course enrollments from several departments. Staff initially use StudentID alone as the row identifier, accidentally assuming that each student takes only one course. The team instead documents the intended grain as one student enrolled in one course offering and uses the corresponding identifiers. It also asks whether a student may withdraw and re-enroll, because that determines whether the table represents current membership or a history of enrollment events. The fictional case illustrates modeling choices, not a universal education-system schema.

Common misconceptions and mistakes

Do not concatenate values casually to manufacture identity: the strings 1 plus 23 and 12 plus 3 can both become 123 without clear boundaries. Separate typed columns usually express the combination more safely. Do not add arbitrary columns merely to make observed duplicates disappear; first understand whether they are valid repeated events or data errors. Also, a surrogate ID does not remove the need to enforce a meaningful business uniqueness rule where one exists.

Business use

Composite keys help make the grain of operational and analytical data explicit. That is especially useful when joining datasets for AI, where a mistaken assumption about uniqueness can multiply rows or mix results from different experiments. Balance readability and natural identity against the width of references and the stability of the component values. Review downstream relationships before changing a key, because dependent tables, imports and APIs may rely on the existing combination.

Check your understanding

A table has a primary key on ProjectID and TaskNumber. Can Project 7 have tasks numbered 1 and 2 while Project 8 also has task 1?

Answer: Yes. All three pairs differ. A second row for Project 7, task 1 would conflict with that primary key.

Related terms

Learn next

State the grain of a small table in one sentence. List plausible identifiers, test whether each is unique under the business rules, and ask what happens if a component changes. Then examine the foreign-key relationships that would depend on the chosen key.

Foreign key

Sources

Primary reference: PostgreSQL 18 documentation, Constraints, https://www.postgresql.org/docs/18/ddl-constraints.html . Official documentation read for primary-key and foreign-key behaviour. Teaching examples are original and distinguish logical key design from product implementation. Editorial review remains pending.

Previous: Composite index (database) Next: Compositing equation