AI Glossary / Data engineering and architecture / Intermediate

Candidate key

Definition

A candidate key is a minimal set of attributes that uniquely identifies a row in a relational data model. Minimal means that removing any attribute would lose that identifying guarantee. A table can have several candidate keys, with one selected as its primary key.

What it means

A key expresses a rule about the data the system is allowed to contain. It is not merely a column that happens to be unique in today's small sample. If future valid records can repeat a value, that value alone is not a dependable candidate key.

A candidate key may contain one column or several. Minimal does not mean that it has the fewest columns of every possible key. It means that none of its own columns is redundant for identification. A one-column identifier and a two-column business identifier can both be candidate keys of the same table.

A superkey also uniquely identifies rows but may contain unnecessary attributes. Candidate keys are the superkeys without that redundancy. Choosing a primary key does not make other valid candidate keys stop being unique or worth enforcing.

How it works

Designers begin by identifying the entity or relationship represented by each row. They establish which combinations of values must be unique according to the business model and whether those values are always available. They then test minimality by asking whether any included attribute can be removed while preserving the guarantee.

One candidate key is selected as the primary key. Other candidate keys are often called alternate keys and can be enforced through suitable database constraints. A generated identifier can be useful, but adding it does not automatically enforce separate business uniqueness requirements.

SQL details matter. A primary-key constraint enforces uniqueness and non-null values, while unique constraints have database-specific rules around nulls. For example, PostgreSQL normally permits multiple null values under a unique constraint unless configured otherwise. A nullable unique column should therefore not casually be equated with a complete, always-present identifier.

Three practical examples

• A staff table uses an internal employee identifier as its primary key while enforcing a separate, guaranteed-unique payroll number.
• A course-enrollment table identifies a permitted enrollment by the combination of learner, course offering and attempt number, according to its business rules.
• A source-data review distinguishes a currently unique email column from a formally guaranteed customer identifier.

Think of it this way

Think of finding a particular seat using a venue and a seat number. If seat numbers repeat across venues, neither value alone is enough, while the pair identifies the seat. Adding the seat color would not improve identification if the pair already guarantees it.

The analogy depends on the stated rules: if a venue reuses seat numbers across separate halls, another attribute may be needed. Keys are meaningful only when the entity and scope of uniqueness have been defined correctly.

Worked example

Consider a fictional table of storage bins with columns BinID, WarehouseCode, BinNumber and Colour. The organization guarantees that BinID is unique and always present. It also guarantees that each BinNumber is unique within a WarehouseCode, although different warehouses reuse the same numbers.

The candidate keys are BinID and the pair WarehouseCode plus BinNumber. The pair is minimal because WarehouseCode alone repeats for several bins and BinNumber alone can repeat across warehouses. BinID plus Colour is a superkey, but not a candidate key: Color can be removed without losing uniqueness.

The organization chooses BinID as the primary key and enforces the warehouse-number pair as an alternate key. If an import assigns a fresh BinID to a second row for the same warehouse and bin number, the primary key alone would not catch the business duplicate. The alternate constraint preserves that separate rule.

Illustrative case study

Illustrative case study: A fictional training provider initially uses email addresses to identify learners. Later it discovers shared household addresses and learners who change address. The team reviews the entity definition and adopts a stable internal learner identifier for references.

It does not assume that every matching name or address is a duplicate person. Migration includes review of ambiguous records and preserves links to existing enrollments. Separately, the provider defines and enforces which combinations make an enrollment unique.

This invented case illustrates why observed uniqueness and business identity need separate consideration. It does not claim that email can never be a key; that depends on the actual rules and the entity being represented, such as an email account rather than a person.

Common misconceptions and mistakes

• Calling any currently unique column a guaranteed candidate key.
• Interpreting minimal as the globally smallest number of columns.
• Assuming a surrogate primary key prevents every business duplicate.
• Treating a composite key as invalid merely because another candidate key has one column.
• Ignoring null handling, reuse of identifiers and the scope within which uniqueness applies.

Business use

For businesses, candidate keys affect whether systems can reliably join records, prevent duplicates and preserve references during change. Define identity with domain owners before choosing convenient columns. Consider whether identifiers can change or be reassigned, and distinguish technical row identity from real-world entity resolution.

For AI and analytics pipelines, unreliable keys can multiply rows during joins or merge unrelated entities. Record the intended uniqueness constraints and check incoming data against them. A data-profiling tool can suggest candidates from a sample, but the business rules must establish whether those candidates remain valid for future data.

People also ask

Can a table have several candidate keys?

Yes. Each must independently provide a minimal identifying set under the model's rules.

Is every superkey a candidate key?

No. A superkey containing redundant attributes is not minimal.

Does selecting a primary key remove the need to enforce alternate keys?

No. Other business uniqueness rules still need appropriate enforcement if they remain part of the model.

Check your understanding

BinID alone uniquely identifies every bin. Is BinID plus Colour a candidate key?

Answer: No. It is a superkey, but Colour is redundant. Removing Colour leaves BinID, which still identifies the row.

Related terms

• Primary key
• Superkey
• Composite key
• Surrogate key
• Natural key
• Functional dependency
• Alternate key
• Unique constraint
• Entity resolution

Learn next

For one table, write the real-world meaning of a row and list its guaranteed identifying combinations. Remove redundant attributes from each proposed key, then check how the database enforces uniqueness and missing values.

• Missing values

More in Data engineering and architecture

• Cache coherence - Cache coherence is the coordination that keeps cached copies of shared memory locations consistent with the system's rules for reads and writes.
• Cache-only memory architecture - Cache-only memory architecture, or COMA, is a shared-memory multiprocessor organization in which each node's main-memory capacity acts as a large cache of...
• Cache stampede - A cache stampede occurs when many requests encounter the same missing or expired cached value and independently try to fetch or recompute it.
• CAP theorem - The CAP theorem identifies a limit of distributed shared-data systems: when communication between parts of the system can fail, a service cannot guarantee...
• Cardinality (data modeling) - In data modeling, cardinality describes how many instances of one entity may or must be associated with an instance of another.
• Carrier cloud - A carrier cloud is cloud infrastructure or a cloud service associated with a telecommunications operator, often integrating computing and storage with the...

Sources

Primary sources: PostgreSQL 18, Constraints: https://www.postgresql.org/docs/18/ddl-constraints.html ; IBM, primary and candidate key distinctions: https://www.ibm.com/think/topics/primary-key ; Silberschatz, Korth and Sudarshan, relational-model teaching material hosted by Cornell, minimal-superkey definition: https://www.cs.cornell.edu/courses/cs230/2001sp/DSC_3.pdf . Relevant returned or indexed definitions reviewed. Storage-bin and training-provider examples are original illustrations.

Previous: Candidate generation Next: Candidate sampling