Candidate Key in SQL and DBMS

Introduction

A candidate key is a minimal set of one or more attributes capable of uniquely identifying every row in a relation. Two words define the concept: unique and minimal. Unique means no two rows share the complete key value. Minimal means that no participating attribute can be removed while preserving that uniqueness.

A table can have several candidate keys. An employee might be uniquely identified by employee_id, employee_number, or a guaranteed unique email address. Database design selects one candidate as the primary key. The unselected candidates remain alternate keys and should still be protected when their business uniqueness is real.

Candidate-key analysis is more demanding than looking at sample data. Names may happen to be unique today without any business guarantee. Email matching may be case-insensitive, values may change or be reused, and nullable UNIQUE constraints behave differently among database products. A sound candidate remains a complete identifier throughout the row's intended lifecycle.

What Is a Candidate Key?

A candidate key is an irreducible superkey. It determines one row and contains no unnecessary attributes. In an employees table, employee_id and employee_number can each be candidate keys if both are mandatory and guaranteed unique.

EMPLOYEES
employee_id | employee_number | email           | employee_name
------------+-----------------+-----------------+--------------
101         | E-9001          | john@test.com   | John
102         | E-9002          | alice@test.com  | Alice

Possible candidate keys:
{employee_id}
{employee_number}
{email}, only if its rules guarantee complete uniqueness

Each set can identify one employee without another column. A larger set containing employee_id and name is unique, but it is not minimal because employee_id alone is enough.

Why It Is Called a Candidate

Every candidate is eligible to become the selected primary key. Designers compare the candidates and choose one main identifier based on stability, size, simplicity, privacy, and how widely it will be referenced.

Candidate keys
    employee_id
    employee_number
    email
        |
        +-- choose employee_id as PRIMARY KEY
        +-- preserve the others as alternate keys

The primary key does not make the remaining candidate rules disappear. If duplicate employee numbers or emails are forbidden, those constraints must still be enforced.

The Uniqueness Requirement

A candidate-key value must identify at most one row. If employee_number E-9001 can occur twice, it cannot identify one employee and is not a candidate key under that rule. Database constraints protect this property under concurrent writes, where application-only duplicate checks can race.

Uniqueness is determined by the database comparison semantics and business definition. Collation, case sensitivity, whitespace normalization, punctuation, and canonical formatting can all change whether two apparent values are considered equal.

The Minimality Requirement

Minimality means that every attribute is necessary. Suppose (employee_id, email) is unique, but employee_id is already unique. Removing email leaves a valid unique identifier, so the pair is a superkey but not a candidate key.

Minimal does not mean shortest character length, smallest data type, or most convenient identifier. It means set inclusion: no proper subset of the key attributes is itself a superkey.

Candidate Key Versus Superkey

A superkey is any attribute set that uniquely identifies rows. Once a candidate key is known, adding any other attributes produces more superkeys. Candidate keys are the minimal members of that larger family.

Every candidate key is a superkey, but not every superkey is a candidate key.

Reducing a Superkey to a Candidate Key

Begin with a known unique set and remove one attribute. If the remainder still identifies every row under the business rules, the removed attribute was unnecessary. Continue until removing any remaining attribute destroys uniqueness.

{employee_id, email, employee_name}
    remove employee_name -- still unique
{employee_id, email}
    remove email -- still unique
{employee_id}
    cannot remove the final attribute

Result: {employee_id} is a candidate key.

This reasoning must use the domain guarantee, not only a query against current data.

Candidate Key Versus Primary Key

A table may have multiple candidate keys but selects one as its primary key. The primary key is therefore a candidate key with a distinguished implementation role. It becomes the table's main identity and is commonly the target of foreign keys.

The other candidate keys remain valid identifiers. They are often called alternate keys and are implemented through UNIQUE and NOT NULL constraints. A table does not have several primary keys merely because it has several ways to identify a row.

Candidate Key Versus Alternate Key

Alternate key describes a candidate that was not chosen as primary. If customer_id, customer_number, and email are all candidates and customer_id becomes primary, the other two are alternate keys.

The distinction is a selection status rather than a difference in uniqueness or minimality. All three remain candidate keys conceptually; one is primary and two are alternate.

Candidate Key Versus UNIQUE Constraint

A UNIQUE constraint is SQL syntax that enforces uniqueness. It does not automatically prove candidate-key status. The constrained columns may be nullable, may include unnecessary attributes, or may enforce a technical rule that is not a complete identifier.

To implement a non-primary candidate reliably in SQL, use an appropriate UNIQUE constraint and require every key component with NOT NULL. NULL behavior under UNIQUE varies among database products, making nullability explicit especially important.

SQL Has No CANDIDATE KEY Keyword

Standard table definitions normally express the selected candidate through PRIMARY KEY and other candidates through UNIQUE plus NOT NULL. There is no everyday CANDIDATE KEY constraint declaration.

CREATE TABLE employees (
    employee_id BIGINT PRIMARY KEY,
    employee_number VARCHAR(30) NOT NULL,
    email VARCHAR(150) NOT NULL,
    employee_name VARCHAR(100) NOT NULL,
    CONSTRAINT uq_employee_number UNIQUE (employee_number),
    CONSTRAINT uq_employee_email UNIQUE (email)
);

Conceptually, all three constrained identifiers may be candidates, while employee_id is the selected primary key.

Single-Column Candidate Keys

A candidate can contain one column. Product_id and SKU may each identify one product when both are mandatory and unique. User_id and username may similarly be independent candidates.

Do not assume every unique-looking single column is suitable. A phone number can be shared, recycled, reformatted, or absent. A government identifier may be unique but inappropriate because of privacy and lifecycle risks.

Composite Candidate Keys

A candidate can contain several attributes when no component identifies the row alone. In enrollment, student_id repeats and course_id repeats, while the pair may identify one enrollment.

CREATE TABLE enrollments (
    student_id BIGINT NOT NULL,
    course_id BIGINT NOT NULL,
    enrolled_on DATE NOT NULL,
    PRIMARY KEY (student_id, course_id)
);

The pair is minimal only if neither student_id nor course_id is unique alone. If students can retake a course, the row grain requires another component or a different identity.

Flight Seat Example

Seat 12A repeats across flights, and flight 100 contains many seats. The combination of flight_id and seat_number can identify one seat assignment for one flight.

FLIGHT_SEATS
flight_id | seat_number | passenger_id
----------+-------------+-------------
100       | 12A         | 501
100       | 12B         | 502
200       | 12A         | 503

If seats can be reassigned over time and history is stored in the same relation, assignment sequence or validity time may become part of the key. Business lifecycle determines the candidate.

Candidate Keys and NULL

Relational candidate keys provide complete row identity, so their attributes are expected to be non-null. In SQL, a nullable UNIQUE column may allow one or multiple NULL values depending on the DBMS and constraint semantics. Such a column cannot reliably identify every row.

email VARCHAR(150) NOT NULL,
CONSTRAINT uq_user_email UNIQUE (email)

If email is optional, it can still have a useful uniqueness rule for non-null values, but it is not a complete candidate key for every user. Distinguish conditional uniqueness from universal row identity.

Candidate Keys Depend on Business Rules

Database values alone cannot establish a candidate key. A query may show that every employee currently has a different name, but future employees can share names. The domain does not guarantee uniqueness, so name is not a candidate.

Document who assigns a value, its scope, whether it can change, whether it can be reused, and whether external systems agree on its format. A candidate key is a rule about all valid database states, not an observation about one snapshot.

Current Uniqueness Is Not Enough

Data profiling is valuable because it finds existing duplicates, but zero duplicates only shows that today's data satisfies the proposed rule. It does not prove tomorrow's inputs must satisfy it. Conversely, duplicates may reveal bad data or show that the proposed key does not match the real grain.

Use requirements, domain ownership, lifecycle analysis, and constraints together. A production key requires an enforceable guarantee.

Good Candidate-Key Characteristics

Every candidate must be unique, minimal, and complete. The candidate selected as primary should also be stable, reasonably narrow, easy to generate or obtain, and safe to propagate. These additional properties influence selection, not conceptual candidate status.

A mutable email can remain a candidate if the system enforces its current uniqueness and completeness, yet a stable generated ID may be a better primary key. A wide business code can be valid but expensive to repeat in child tables.

Natural Candidate Keys

A natural key comes from the business domain, such as a SKU, ISO-like code, employee number, or account number. It can improve readability and prevent duplicate business entities when its rules are trustworthy.

Natural values can change, acquire exceptions, differ among jurisdictions, or carry sensitive meaning. Evaluate the whole lifecycle before making one the main identity. A natural key can remain an alternate candidate even when a surrogate is primary.

Surrogate Candidate Keys

A surrogate identifier is created for database or application identity rather than taken from business facts. Sequence values, identity columns, and generated UUIDs are common examples. If the value is mandatory, unique, and minimal, it is a candidate key.

A surrogate does not by itself prevent duplicate business entities. Customer IDs 101 and 102 can still describe the same email or account number unless the business candidate is separately constrained.

Natural and Surrogate Candidates Together

Many production tables use an internal surrogate primary key and one or more natural alternate keys. Orders can use order_id internally and order_number externally. Products can use product_id as primary and SKU as a unique business identifier.

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    order_number VARCHAR(40) NOT NULL UNIQUE,
    customer_id BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL
);

This pattern separates technical reference stability from business-facing identity while preserving both rules.

Selecting the Primary Candidate

Compare stability, data type width, simplicity, privacy, generation, reference frequency, and distributed-system requirements. A primary key appears in indexes, foreign keys, logs, caches, messages, and application objects, so change cost matters.

Uniqueness alone does not make email a good primary key. It can change, contains personal information, and may be long. A narrow stable employee_id can serve as primary while email remains an alternate candidate.

Candidate Key Versus Foreign Key

A candidate key identifies rows in its own table. A foreign key references an eligible key in a parent table and may repeat in the child. These roles can intersect: a child's foreign key may also be part of its candidate or primary key.

Enrollment student_id and course_id are foreign keys individually and form a composite candidate key together. The foreign-key constraints enforce parent existence; the composite key enforces relationship identity.

Foreign Keys Can Reference Alternate Candidates

Foreign keys commonly reference a primary key, but SQL databases generally allow references to an eligible UNIQUE candidate key subject to product-specific rules. A child might reference an immutable account_number instead of account_id when that domain relationship is deliberate.

Referencing an alternate key propagates its type, width, and update lifecycle. Stable surrogate references are often simpler, but a business-key reference can be valid when its semantics and platform support are clear.

Candidate Key Versus Composite Key

Candidate describes a role: a minimal unique identifier. Composite describes structure: more than one column. A candidate can be simple or composite, and a composite set can be a candidate, primary key, UNIQUE key, foreign key, or merely a non-key index.

Keeping role and structure separate prevents terminology errors. (student_id, course_id) is a composite candidate when it is minimal and unique.

Functional Dependency

If K is a candidate key for relation R, then K functionally determines every attribute in R. Knowing one key value identifies one complete row. Symbolically, K determines all relation attributes.

employee_id -> employee_number, email, employee_name
email       -> employee_id, employee_number, employee_name

Each dependency is valid only when its determinant is truly a candidate. For a composite candidate, the full combination must determine the remaining attributes, and no proper subset may do so.

Candidate Keys and Normalization

Candidate keys help identify partial and transitive dependencies. In second normal form, non-prime attributes should not depend on only part of a composite candidate key. Third normal form and BCNF evaluate dependencies in relation to candidate keys and superkeys.

If course_name depends only on course_id inside an enrollment relation keyed by student_id plus course_id, storing course_name there creates a partial dependency. Moving course facts to a courses table reduces update anomalies while enrollment references the course.

Prime Attributes

A prime attribute belongs to at least one candidate key. When a relation has multiple candidate keys, an attribute can be prime even if it is not part of the selected primary key. This term is used in formal normalization definitions.

Primary-key column and prime attribute are therefore not synonyms. Prime status considers every candidate key.

Multiple Composite Candidate Keys

A relation can have several composite candidates. A timetable slot might be uniquely identified by (room_id, start_time) and also by (instructor_id, start_time) under strict business rules. Each combination must be independently minimal and unique.

Multiple candidates often reveal several business constraints worth preserving. Choosing one as primary should not remove enforcement of the others.

Email as a Candidate Key

Email is frequently described as unique, but implementation requires a precise equality rule. Are addresses compared case-insensitively? Is whitespace trimmed? Are Unicode domains normalized? Can one user own several addresses? Can an address be reused after account deletion?

Store a normalized comparison value or use an appropriate collation or functional index according to the DBMS and product policy. Keep the display form separately if normalization changes presentation. An undefined equality rule is not a reliable key.

Historical Reuse

A code can be unique among active rows yet reused later. Employee numbers, phone numbers, usernames, and registration identifiers may return to an available pool. If historical rows remain, a simple UNIQUE constraint prevents reuse; removing it can make the value unable to identify one historical row.

Decide whether the candidate identifies an entity for all time, only while active, or within a validity period. The key may need a version, organization, or effective-date component.

Soft Delete and Conditional Uniqueness

Soft deletion retains rows with a status or deleted timestamp. A normal UNIQUE constraint continues to include those rows, so an email cannot be reused. Some systems want that protection; others require uniqueness only among active accounts.

Filtered or partial unique indexes can enforce active-row uniqueness in databases that support them, while other platforms need a different model. Such a value is a conditional business key, not necessarily a universal candidate for the complete table containing history.

Candidate Keys Can Change With Requirements

A student-course pair is a candidate only while one enrollment per course is allowed. Introducing semesters or retakes changes the row grain. A globally unique SKU may become unique only within a vendor after marketplace expansion.

Schema reviews should revisit candidate assumptions when product rules change. Constraints that once protected integrity can later reject valid records, while removed constraints can allow duplicates.

Indexes and Candidate Keys

PRIMARY KEY and UNIQUE constraints are commonly supported by unique indexes, although logical constraints and physical indexes are distinct concepts. Index width and column order affect storage and query performance, particularly for composite candidates.

Do not add duplicate indexes automatically. Inspect what the DBMS creates for each constraint and whether query patterns need additional ordering. Performance does not justify leaving a real candidate unenforced.

Privacy and Security

A government identifier may be unique and minimal yet unsuitable as a widely propagated primary key. Repeating it in child tables, logs, URLs, events, and analytics increases privacy exposure. A surrogate can reduce propagation while a protected alternate constraint preserves uniqueness.

Unpredictable identifiers do not provide authorization. Whether an API uses a numeric ID, UUID, username, or another candidate, every request still needs access control.

Candidate Keys in API Design

Public resources can be addressed by a primary key, alternate candidate, or separate public identifier. /orders/ORD-2026-000145 may be easier for support teams than an internal sequence. The external value should remain stable if it becomes part of URLs and integrations.

Normalize lookup inputs consistently, avoid exposing sensitive candidates, and return clear conflict responses when a requested candidate already exists. Database and public identity do not need to be the same.

Adding a Candidate Constraint to Existing Data

Before adding UNIQUE and NOT NULL, profile duplicates, missing values, normalization collisions, and historical reuse. A case-sensitive query may miss duplicates that a new case-insensitive rule will detect.

SELECT normalized_email,
       COUNT(*) AS row_count
FROM users
GROUP BY normalized_email
HAVING COUNT(*) > 1;

Resolve each conflict from business evidence. Merging records, selecting an owner, archiving data, or expanding the candidate key can have downstream effects on foreign keys and integrations.

Changing Candidate Keys Safely

Replacing a primary candidate with another key requires staged schema and data migration. Add and populate the new key, enforce its rule, update references, deploy compatible application versions, and only then remove obsolete constraints if appropriate.

If an alternate candidate becomes mutable, retain a stable primary reference and update the alternate value transactionally. Audit logs may need both old and new forms for traceability.

Candidate Keys in Multi-Tenant and Distributed Systems

Modern systems often store records for many organizations in the same table. In that design, a value may be unique inside one tenant without being unique across the entire platform. A username such as alex, an employee number such as E1024, or a product code such as SKU-10 may legitimately appear under several customers. The candidate key must therefore include the tenant boundary, for example (tenant_id, username). Declaring only username as UNIQUE would encode the wrong business rule, while omitting the composite constraint would allow duplicates within a tenant.

Distributed services add another distinction between local identity and global identity. An order service may use order_id as its internal primary key, while an external marketplace supplies (marketplace_id, external_order_number) as an alternate candidate. Both rules can identify an order, but they serve different ownership boundaries. Preserving the external candidate prevents the same marketplace event from creating duplicate orders and supports idempotent message processing.

Teams should also decide where uniqueness can be enforced authoritatively. A database constraint is reliable when all competing writes reach one database. When records are partitioned across regions or services, global uniqueness may require coordinated identifier generation, a central registry, or conflict detection after synchronization. Application checks alone remain vulnerable to concurrent requests. The architecture must state whether a candidate is globally unique, tenant-scoped, service-scoped, or only unique during a defined period.

Data integration exposes weak assumptions quickly. Two source systems may both claim that an account number is unique, yet their values collide after consolidation. Import jobs should retain source-system identity and use a candidate such as (source_system, source_account_number) until a governed enterprise identifier exists. This approach avoids accidental merges and makes lineage traceable. Candidate-key analysis therefore belongs not only in table design but also in event schemas, migration plans, master-data management, and reconciliation tests.

Common Candidate-Key Mistakes

Common errors treat candidate and primary key as synonyms, assume only single columns qualify, or call every superkey a candidate. Another error uses current uniqueness as proof without a domain guarantee.

Designers also ignore NULL, create a non-minimal composite set, choose a mutable or sensitive candidate as primary without evaluating consequences, or add a surrogate and forget the natural UNIQUE rule. Each mistake weakens either the concept or its SQL enforcement.

Testing a Single-Column Candidate

Tests should insert a valid value, attempt a duplicate, attempt NULL, and exercise case and normalization variants according to the business rule. Concurrent insert tests prove that the database constraint, not only application validation, resolves races.

INSERT INTO users (user_id, email)
VALUES (1, 'john@test.com');

INSERT INTO users (user_id, email)
VALUES (2, 'john@test.com'); -- should fail

Also test updates from one valid value to another and to a value already owned by another row.

Testing a Composite Candidate

Each individual component should be allowed to repeat when the model permits it, while the complete combination must reject duplicates. Every required component should reject NULL. Remove each component conceptually and confirm that the remaining subset is not unique under valid business data.

101 | 10  -- valid
101 | 20  -- repeated student, valid
102 | 10  -- repeated course, valid
101 | 10  -- duplicate pair, rejected

Include lifecycle cases such as semesters, attempts, soft deletion, and tenant scope so the tested candidate matches future valid states.

A Candidate-Key Design Workflow

Define the row grain and list attributes or combinations that can identify it. Reject sets based only on accidental current uniqueness. Test completeness, domain-wide uniqueness, and lifecycle behavior. Remove unnecessary components until each remaining unique set is minimal.

Compare candidates for primary-key selection using stability, size, privacy, generation, and reference cost. Implement the primary and alternate constraints, then test duplicates, NULL, normalization, concurrency, changes, and historical cases.

Define one row
    -> list guaranteed unique sets
    -> require complete values
    -> remove unnecessary attributes
    -> identify candidate keys
    -> select the best primary candidate
    -> enforce alternate candidates
    -> test lifecycle and concurrency

Key Hierarchy

Complete Customer Example

CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    customer_number VARCHAR(30) NOT NULL,
    email VARCHAR(150) NOT NULL,
    customer_name VARCHAR(100) NOT NULL,
    CONSTRAINT uq_customer_number UNIQUE (customer_number),
    CONSTRAINT uq_customer_email UNIQUE (email)
);

If the business guarantees all three identifiers, the relation has three candidate keys. Customer_id is selected as primary; customer_number and email are alternate candidates. Sets such as (customer_id, customer_name) are superkeys but not candidates because they include an unnecessary attribute.

Interview Questions and Answers

What is a candidate key? A minimal attribute set that uniquely identifies every row.

Can a table have multiple candidate keys? Yes. One is selected as primary and the others are alternate keys.

What is the difference between a superkey and candidate key? Both are unique, but only the candidate is minimal.

Can a candidate key be composite? Yes. Multiple columns can form one minimal unique key.

Does SQL have a CANDIDATE KEY keyword? Normally no. Use PRIMARY KEY or UNIQUE with NOT NULL to implement the candidates.

Can a candidate key contain NULL? A complete relational identifier should not. In SQL, declare non-primary candidate columns NOT NULL because UNIQUE NULL behavior varies.

Is every UNIQUE constraint a candidate key? No. It may permit NULL or contain a non-minimal column set.

Practical Review Checklist

• Define exactly what one row represents.
• Require a business guarantee, not accidental current uniqueness.
• Confirm that every candidate identifies all valid rows.
• Remove every unnecessary attribute to verify minimality.
• Use NOT NULL with alternate candidate constraints.
• Define normalization, collation, and comparison semantics.
• Consider change, reuse, soft deletion, and historical scope.
• Evaluate stability, width, privacy, and reference frequency.
• Preserve natural uniqueness when selecting a surrogate primary key.
• Profile and repair existing data before adding constraints.
• Test duplicates, NULL, updates, concurrency, and composite subsets.
• Revisit candidate assumptions when business rules change.

Key Takeaway

A candidate key is a minimal superkey: it uniquely identifies every row and contains no unnecessary attributes. A relation can have several candidates. One is selected as the primary key, while the remaining candidates become alternate keys and should remain enforced.

SUPERKEY
    + minimality
    = CANDIDATE KEY

CANDIDATE KEY
    + selected main role
    = PRIMARY KEY

Reliable candidate analysis goes beyond sample uniqueness. It defines NULL, comparison, stability, historical reuse, privacy, and lifecycle rules. Implement complete candidates with PRIMARY KEY or UNIQUE plus NOT NULL, choose the primary candidate deliberately, and preserve every business identifier whose uniqueness protects the data.