Candidate Keys and Alternate Keys

Data Modeling

Candidate keys are all the minimal ways a row could be uniquely identified. The primary key is the one candidate key you choose as the table’s main identifier. Alternate keys are the remaining candidate keys that are still valid identifiers, even though they were not selected as primary.

Why the distinction matters

Key design is not just database decoration. It determines how tables join, how duplicates are detected, how changes are tracked, and how people decide whether two records describe the same thing.

A common modeling mistake is to treat the primary key as the only identifier that matters. In practice, a table may have several valid identifiers. One is chosen as the primary key because it is convenient, stable, or system-controlled. The others should not disappear from the model if the business still depends on them.

The useful mental model is simple: possible identifiers first, selected identifier second. Candidate keys describe what could identify a row. The primary key records which one you chose.

A small hypothetical account table

Use the following hypothetical account table. The goal is to model one row per billing account. The example is intentionally small, so the counterexamples are visible.

Do not infer a candidate key from the sample alone. A few rows can show why something is not a key, but they cannot prove that something will always be a key. Candidate keys require a business rule about the intended relation, not only a lucky sample of distinct values.

account_id	account_number	tax_id	email	legal_name	country
A100	ACME-US-001	US-TAX-77	[email protected]	Acme Inc	US
A101	BETA-US-002	US-TAX-88	[email protected]	Beta LLC	US
A102	NOVA-UK-003	UK-VAT-21	[email protected]	Nova Ltd	UK
A103	ACME-EU-004	DE-VAT-77	[email protected]	Acme GmbH	DE
A104	SOLO-US-005	NULL	[email protected]	Solo Co	US
A105	TEST-US-006	US-TAX-88	[email protected]	Beta Test Account	US

What makes a candidate key

A candidate key is a minimal set of one or more columns that uniquely identifies each row in a relation.

There are two parts to that definition:

• Uniqueness: no two valid rows can have the same values for the key columns.
• Minimality: no column can be removed while preserving uniqueness.

Because of minimality, every candidate key is a superkey, but not every superkey is a candidate key. A superkey can contain unnecessary columns. A candidate key cannot.

The phrase minimal superkey is another way to describe a candidate key. It means the column set is sufficient to identify a row, and it contains no extra columns.

Evaluate possible identifiers in the example

Assume the intended business rules are these: account_id is generated by the warehouse process and is never reused; account_number is issued by the billing system and is never reused; tax_id can be missing or shared in some operational cases; email is a contact attribute, not an account identity.

Under those assumptions, account_id and account_number are candidate keys. Each can identify one account, and neither needs another column to do so.

tax_id is not a candidate key in this table because it is missing for one row and repeated for another. email is not a candidate key because the same contact email can appear on more than one account. legal_name is not a candidate key because names are not governed as unique identifiers.

The pair account_id plus account_number is a superkey, because together they identify a row. But it is not a candidate key, because account_id alone is already enough and account_number alone is already enough. The pair contains unnecessary columns.

Possible identifier	Result	Reason
account_id	Candidate key	Unique, present on every row, and minimal.
account_number	Candidate key	Unique, present on every row, and minimal under the stated billing rule.
tax_id	Not a candidate key	It can be missing and can repeat.
email	Not a candidate key	The same email can be attached to more than one account.
legal_name	Not a candidate key	Names are descriptive attributes, not governed unique identifiers.
account_id plus account_number	Superkey, not candidate key	The pair is unique, but it is not minimal because each column alone is already enough.

The primary key is the selected candidate key

A primary key is not a different kind of fact from a candidate key. It is one candidate key selected to be the table’s main row identifier.

In the account example, you might choose account_id as the primary key because it is compact, system-generated, and insulated from billing-system formatting changes. That does not make account_number unimportant. It means account_number becomes an alternate key if it still uniquely identifies an account.

The practical rule is: choose one candidate key as primary, but keep the other candidate keys visible and protected. If a business identifier is valid and important, model it as an alternate key rather than treating it as an ordinary descriptive attribute.

What alternate keys are for

An alternate key is any candidate key that was not selected as the primary key.

Alternate keys matter because users, source systems, and downstream processes often identify entities by business identifiers rather than internal primary keys. An account manager may know account_number. A support system may send account_number. A warehouse table may use account_id for joins. Both can be valid identifiers, but they serve different roles.

In relational design, alternate keys are often enforced with uniqueness rules or unique constraints. The exact implementation depends on the database system and its treatment of nulls, but the modeling intention is durable: alternate keys should not silently accept duplicates if the business rule says they identify one row.

Counterexamples that prevent bad key choices

Small counterexamples are useful because they expose false identifiers before they become production assumptions.

• Observed uniqueness is not enough: legal_name may appear unique in today’s extract, but that does not mean the organization guarantees unique legal names forever.
• A nullable column is usually not a full row identifier: if tax_id is missing for valid accounts, it cannot identify every row by itself.
• A repeated value breaks uniqueness: if the same email can belong to two accounts, email is not a candidate key for accounts.
• Adding columns can hide the real key: account_id plus account_number is unique, but it is not minimal if either column alone is already unique.

These counterexamples are not edge-case trivia. They are how duplicate accounts, broken joins, and inconsistent dashboard counts enter a data model.

How to choose the primary key from candidate keys

Once you have identified the candidate keys, the next decision is operational: which one should be the primary key?

Good primary keys tend to be stable, compact, mandatory, and free from business meaning that may change. But this is a preference for maintainability, not a law of nature. A natural business key can be a good primary key when it is truly stable, governed, and used consistently. A surrogate key can be a good primary key when it is paired with constraints that still protect real business identifiers.

Ask these questions before choosing:

• Will this identifier ever be corrected, reformatted, merged, or reused?
• Is it always present at the moment the row is created?
• Does the organization control its meaning, or does another system?
• Will people use it to reconcile records across systems?
• Can downstream tables join on it without inheriting avoidable business volatility?

If two candidate keys are valid, choose the one that creates the least long-term ambiguity for joins and change handling. Then preserve the other as an alternate key.

Term	Meaning	In the example
Superkey	Any column set that uniquely identifies a row, including sets with extra columns.	account_id plus account_number
Candidate key	A minimal superkey.	account_id; account_number
Primary key	The candidate key selected as the main identifier.	account_id, if that is the design choice
Alternate key	A candidate key not selected as primary.	account_number, if account_id is primary

Common failure modes

Most key problems come from confusing convenience with identity.

• Using a descriptive attribute as a key: names, emails, labels, and statuses often change or repeat.
• Using a source-system identifier without checking reuse: some operational systems recycle identifiers, scope them by tenant, or change them during migrations.
• Creating a surrogate key and forgetting the business key: this prevents duplicate detection because the database can generate a new surrogate for every duplicate business entity.
• Treating a composite superkey as a candidate key: adding columns until records look unique does not prove minimality.
• Letting alternate keys become undocumented: downstream teams may then build their own conflicting uniqueness assumptions.

The cure is not to argue abstractly about natural keys versus surrogate keys. The cure is to list the candidate keys, test minimality, choose the primary key deliberately, and enforce the remaining valid identifiers where appropriate.

A practical checklist for modeling keys

Use this checklist when designing or reviewing a table:

1. Write the table grain in one sentence: one row represents exactly what?
2. List every column or column set that claims to identify one row.
3. Reject any option that can be missing for a valid row.
4. Reject any option that can repeat for two valid rows at the stated grain.
5. Remove unnecessary columns from each remaining option until it is minimal.
6. Call the remaining minimal identifiers candidate keys.
7. Choose one candidate key as the primary key.
8. Record the other candidate keys as alternate keys.
9. Enforce the primary and alternate key rules with constraints or data tests where the platform allows.
10. Document any identifier that is unique only within a scope, such as tenant, country, source system, or date range.

This process works because it separates semantics from implementation. First decide what identifies a row. Then decide how your database, transformation layer, or data quality checks should enforce that decision.

Key takeaways

• Candidate keys are all minimal identifiers that could uniquely identify a row.
• A primary key is the candidate key selected as the table’s main identifier.
• Alternate keys are valid candidate keys that were not selected as primary.
• A minimal superkey is another name for a candidate key.
• Observed uniqueness in a sample is not enough; key design requires a rule about the intended table grain.
• Surrogate primary keys do not remove the need to protect real business identifiers.

Next step

Pick one important table and write its grain in one sentence. List every possible identifier, remove any nullable or repeatable option, test minimality, then mark one candidate key as primary and the remaining valid identifiers as alternate keys.