Chapter 02 · Entities, Attributes, and Identifiers
Candidate Keys and Business Identifiers
Understand candidate keys and business identifiers, prove uniqueness assumptions, and distinguish identity from convenient labels or database-generated IDs.
Learning outcomes
Every entity instance must be distinguishable. Identity is not the same as a display name, row position, or a convenient auto-increment number. This lesson introduces candidate keys: minimal sets of attributes whose values can uniquely identify entity instances according to the business rules.
Define superkey, candidate key, primary key, alternate key, and business identifier.
Evaluate uniqueness, minimality, stability, and scope for candidate identifiers.
Recognize false keys such as names, timestamps, and undocumented assumptions.
Model business uniqueness even when a surrogate primary key is used.
Identity begins with a uniqueness rule
Suppose two assets have the same manufacturer, model, color, and purchase date. They are still two different assets. The system needs a fact or combination of facts that distinguishes them.
A superkey is any set of attributes that uniquely identifies an entity instance. A candidate key is a minimal superkey: remove any attribute and uniqueness is lost. One candidate key may be chosen as the primary key; the others remain alternate keys.
Example: manufacturer-scoped serial numbers
WorkshopHub learns that serial numbers are guaranteed unique only within a manufacturer. Therefore:
(manufacturer_id, serial_number)is a candidate key for Asset if both assumptions are true:
- every asset has a manufacturer and serial number;
- the same manufacturer never legitimately reuses the same serial number for two distinct assets in the system's scope.
If serial numbers can be missing, recycled, entered incorrectly, or only unique within product lines, the candidate key must be reconsidered. Candidate keys are claims about the domain, not guesses about current sample data.
Uniqueness in data is not proof of a key
A column may happen to contain unique values today without being guaranteed unique by the business. For example, every technician may currently have a distinct last name. That does not make last name a candidate key.
Do not infer a key merely because a query returns no duplicates. A candidate key requires a durable rule or authoritative identifier semantics.
Minimality matters
If employee_number alone uniquely identifies an employee, then the pair (employee_number, full_name) is a superkey but not a candidate key. full_name contributes nothing to identity.
Minimal keys are easier to understand and enforce. They also matter for normalization because functional dependencies are expressed in terms of determinants and keys.
Candidate key checklist
| Property | Question | Why it matters |
|---|---|---|
| Uniqueness | Can two valid instances ever share this value? | If yes, it cannot identify them. |
| Minimality | Can any component be removed while preserving uniqueness? | If yes, the set is not minimal. |
| Requiredness | Must the identifier be known for every instance? | Optional identifiers are difficult as universal keys. |
| Stability | Can the value change over the entity's lifetime? | Changing keys propagate through relationships. |
| Scope | Unique globally, per tenant, per country, per manufacturer? | Uniqueness often has a hidden boundary. |
| Authority | Who guarantees the value? | External identifiers may change policy. |
Business identifiers
A business identifier is an identifier meaningful outside the database's internal mechanics: employee number, ISO country code, vehicle identification number, purchase-order number, tax identifier, SKU, or manufacturer serial number.
Business identifiers may form candidate keys, but not automatically. An order number might be unique only within a branch or fiscal year. A SKU may be unique only within one catalog. A national identifier may be unavailable for some customers or prohibited from broad use for privacy reasons.
Composite candidate keys
Sometimes identity naturally depends on more than one attribute. Examples:
(manufacturer_id, serial_number)for an asset;(country_code, national_number)for some external registry;(tenant_id, username)in a multi-tenant application;(work_order_id, line_number)for line numbers scoped to one work order.
Composite candidate keys are not inherently bad. The later choice between using them as primary keys or enforcing them as alternate keys depends on practical tradeoffs.
False candidate keys
Names
People, companies, products, and locations can share names. Names also change.
Email addresses
Email can be a useful unique login identifier in some systems, but people may change addresses, share organizational addresses, or have none. The business rule must justify uniqueness.
Phone numbers
Numbers are recycled, shared, reformatted, and not universal identifiers.
Timestamps
Creation time is not guaranteed unique unless combined with another guaranteed mechanism. Two rows can be created at the same timestamp precision.
Current row number
Physical row positions and query ordering are not persistent entity identities.
Primary key selection does not erase alternate keys
Suppose Asset uses an internal asset_id as its primary key. The business rule that manufacturer + serial number must be unique still matters.
CREATE TABLE asset ( asset_id INTEGER PRIMARY KEY, manufacturer_id INTEGER NOT NULL, serial_number TEXT NOT NULL, model_name TEXT, UNIQUE (manufacturer_id, serial_number));The surrogate key provides internal identity; the unique constraint preserves the candidate business key. Omitting the unique constraint allows duplicate business identities and defeats the model.
Candidate keys and duplicate prevention
Imagine a customer import runs twice. If the only key is a generated customer_id and there is no unique business identifier, the second import can create duplicate logical customers. Surrogate keys prevent duplicate row IDs; they do not prevent duplicate real-world entities by themselves.
This is why integration design often needs both:
- a stable internal key for relationships;
- one or more unique external/business identifiers with explicit scope.
Key changes reveal identity questions
If a supposedly identifying value can change, ask whether it is really identity or merely an attribute. Suppose WorkshopHub changes an asset's serial number after correcting a data-entry error. That may be acceptable because the asset is still the same physical object. But if a manufacturer physically replaces the identification plate, does the business still consider it the same asset? Identity is a domain decision.
Practice: evaluate candidate keys
Key reasoning
Evaluate each proposed identifier. State what rule would have to be true for it to be a candidate key.
- Customer email
- Part SKU
- Technician full name
- Work order number
- Asset manufacturer + serial number
Review guidance
Email requires every customer to have exactly one non-reused unique email within the system scope. SKU requires catalog-wide uniqueness and stability. Full name is almost never a valid key. Work-order number may be valid if the organization guarantees uniqueness across all branches and time, or may require branch/year scope. Manufacturer + serial is valid only if the manufacturer guarantees non-reuse and every asset has both values.
Summary and next lesson
Candidate keys are minimal business-supported uniqueness rules. They are discovered from domain semantics, not generated automatically by the DBMS. A primary key is one chosen candidate or an internal surrogate, while alternate business keys must still be preserved when they represent real uniqueness. The next lesson compares natural and surrogate key strategies directly.
References
- C. J. Date, Database Design and Relational Theory.
- Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
- E. F. Codd, “A Relational Model of Data for Large Shared Data Banks,” 1970.
- PostgreSQL and SQLite documentation for primary and unique constraints.