Must an all-key relation have several columns?

No. A one-attribute relation can also have its sole attribute as its key.

Does a surrogate identifier preserve the same structure?

Adding a separate identifier changes the attributes and key design; retain any required uniqueness rule for the original association.

Can two columns both repeat?

Yes. Their combined values can identify a tuple even when neither column is unique alone.

AI Glossary / Data engineering and architecture / Intermediate

All Key relation

Definition

An all-key relation is a relation whose key consists of all its attributes taken together. Every attribute participates in identifying the represented fact, with no separate descriptive attribute outside that key. A common example is a pure many-to-many association containing only the identifiers of the associated entities.

What it means

Consider a relation Membership(PersonID, GroupID). If a person can belong to many groups and each group can contain many people, neither attribute alone identifies a membership. The pair identifies one membership fact, and the relation contains no other attributes.

All-key does not mean that each column is individually unique. PersonID may repeat across groups and GroupID may repeat across people. It is their combination that must be unique under the intended rule.

In relational theory, a candidate key is minimal: removing any attribute destroys its ability to identify every tuple. Simply declaring every column as an SQL primary key does not establish that the resulting combination is minimal or that the business model needs every column for identity.

How it works

First define what one row means. Then identify the smallest set of attributes whose values distinguish every valid fact of that kind. If this set includes every attribute in the relation, it has the all-key structure described here.

For an SQL implementation, a composite primary key can enforce uniqueness and non-nullness of the combination. Foreign keys may additionally require the component identifiers to refer to existing entities. Those constraints serve different purposes: combination uniqueness prevents duplicate associations, while referential integrity checks their endpoints.

Adding a descriptive attribute can change the classification. Membership(PersonID, GroupID, JoinedDate) has a non-key attribute if the person-group pair still identifies the membership and determines its date.

All-key does not mean automatically free of every redundancy. Independent multivalued facts can create unnecessary combinations in a wider all-key relation. Such cases motivate fourth normal form and require dependency analysis, rather than assuming that the absence of non-key attributes completes the design.

Three practical examples

A library records which tags apply to which documents in a relation containing DocumentID and TagID, with each pair appearing once.
A project system records participant membership using PersonID and ProjectID when it needs no additional membership attributes.
A course catalog records allowed prerequisite pairs using CourseID and PrerequisiteID, with additional rules to govern which relationships are meaningful.

Think of it this way

Think of a card recording a connection between two named endpoints. The connection is identified by both names together; neither endpoint alone tells you which connection the card represents.

The analogy does not require every connection to be valid. A prerequisite relation may prohibit a course from requiring itself, and a membership may require permission. Those are additional constraints. An all-key structure describes identity, not every rule about whether a particular association should exist.

Worked example

Why both key parts matter

An invented membership relation contains (P1, G1), (P1, G2) and (P2, G1). PersonID is not a key because P1 appears twice. GroupID is not a key because G1 appears twice. If the business permits each person-group association only once, the pair is the candidate key.

Adding (P2, G2) is allowed by that rule. Adding another identical (P1, G1) is not. An SQL primary key on the pair would reject the duplicate, assuming ordinary non-null identifier values.

Now add JoinedDate. If each membership has one recorded joining date and repeats over time are outside the model, the pair still identifies the row. JoinedDate is then descriptive, so this expanded relation is no longer all-key in the same sense.

If people can leave and rejoin, the team must reconsider what one row represents. A membership episode may need its own identifier or another carefully defined key. Adding a date casually is not a substitute for settling that meaning.

Illustrative case study

A fictional volunteer directory stores VolunteerID, Language and Activity in one relation. It assumes that each volunteer's languages are independent of their eligible activities. A volunteer with two languages and three activities consequently requires six combinations.

The full triple identifies each combination, yet a language must be repeated for every activity. The team models volunteer-language and volunteer-activity facts separately and can reconstruct combinations when that question is actually needed.

This change is appropriate only because the independence assumption is true. If eligibility depends on a particular language-activity pairing, splitting the relation could invent combinations that were never valid. The all-key label alone cannot justify decomposition.

Common misconceptions and mistakes

All-key means every column is individually a key. The key may be the complete combination, while each component repeats.
Every table containing foreign keys is all-key. It may also contain descriptive columns or have a different identifying structure.
An all-key relation needs no further design analysis. Multivalued dependencies, meaningful constraints and the intended row identity still matter.

Business use

All-key relations often make relationships explicit instead of storing lists inside entity records. They can support clear queries such as which projects a person joins or which documents carry a tag.

Review the lifetime and identity of each association. Does the same pair represent one continuing relationship, or can several historical episodes exist? Do attributes such as role or effective date describe the association, or contribute to its identity? Answers change the key and possibly the schema.

For AI-assisted design, ask for the business rule that proves key minimality. Also test independent multivalued facts before accepting a wide table of combinations. A schema can look tidy while multiplying records that should have been represented separately.

Check your understanding

A table has only DocumentID and TagID, and each document may have many tags while each tag may describe many documents. Which combination identifies one tagging fact?

Answer: The pair DocumentID, TagID, provided the same tagging fact is allowed only once. Neither identifier alone distinguishes all associations.

Related terms

Learn next

Read candidate-key minimality and multivalued dependencies. Sketch one association table, identify its row meaning and test whether removing either key component would lose uniqueness.

Candidate key

Sources

Gordon College, Databases Intro: https://www.cs.gordon.edu/local/courses/cps211/lectures-2009/DatabasesIntro.pdf. Primary teaching notes explicitly describe all-key relations.

PostgreSQL, Constraints: https://www.postgresql.org/docs/16/ddl-constraints.html. Primary documentation of composite primary keys and foreign keys.

Silberschatz, Korth and Sudarshan, Advanced Relational Database Design: https://www.db-book.com/online-chapters-dir/28.pdf. Primary textbook treatment of multivalued dependencies. The membership and volunteer examples are original illustrations.

Previous: Alignment method Next: All nearest smaller values