1 Definition and basic properties
A candidate key is a minimal collection of attributes that can uniquely identify each tuple in a relation. In practical terms, it is the smallest set of columns that still guarantees one-and-only-one match for every row. Because of this uniqueness, candidate keys are central to identifying records unambiguously in a relational schema.
A relation may contain more than one candidate key. Each one is individually sufficient to identify tuples, but no candidate key contains unnecessary attributes. This minimality distinguishes candidate keys from larger unique attribute sets and makes them especially useful in formal database design.
1.1 Relational model background
In the relational model, a relation is a set of tuples with attributes defined by a schema. Since tuples are treated as distinct records in a mathematical sense, the schema must provide a way to distinguish them when needed. Candidate keys supply that distinguishing mechanism by enforcing a unique pattern of attribute values across the relation.
The concept is rooted in the theory of relations rather than in implementation details. A candidate key is not merely a convenient label; it is a structural property of the relation and its constraints.
1.2 Uniqueness
Uniqueness means that no two tuples in the relation share the same values for all attributes in the key. If a candidate key consists of a single attribute, that attribute alone must be enough to separate every row. If it consists of several attributes, the combination must be unique as a whole.
This property is what allows candidate keys to function as reliable identifiers. Even if many rows share some individual attribute values, the complete key values remain distinct.
1.3 Minimality
Minimality requires that removing any attribute from a candidate key destroys its uniqueness. If a subset of the key still uniquely identifies tuples, then the original set was not minimal and therefore was not a candidate key.
This condition prevents unnecessary complexity. A key should contain only the attributes truly needed for identification, which keeps schemas clearer and often easier to maintain.
1.4 Comparison with superkeys
A superkey is any set of attributes that uniquely identifies tuples, whether or not it is minimal. Every candidate key is a superkey, but not every superkey is a candidate key. In other words, candidate keys are the minimal members of the larger family of superkeys.
This distinction matters because a relation can have many superkeys, especially when extra attributes are added to a candidate key. Candidate keys identify the essential unique structures within that larger set.
2 Examples
Examples make the idea of candidate keys easier to see. In a table of people, an identification number might uniquely identify each person, while in a table of course registrations, a combination of student and course identifiers may be needed.
2.1 Single-attribute candidate keys
A single attribute can be a candidate key when its values are unique across the entire relation. Common examples include a serial number, employee number, or product code, provided that no duplicates exist and the attribute is never empty in a way that would break identification.
In such cases, the key is straightforward to use because it requires only one column. This simplicity often makes single-attribute candidate keys attractive in database design.
2.2 Composite candidate keys
A composite candidate key uses two or more attributes together to achieve uniqueness. For example, a table recording course enrollments might need both a student identifier and a course identifier to distinguish one enrollment from another.
Composite keys are common when no single attribute is sufficient on its own. Their structure reflects the underlying business rule that only the full combination has identifying power.
2.3 Multiple candidate keys in one relation
A relation can have several candidate keys if more than one attribute set uniquely identifies tuples. For instance, a person table might allow both a government-issued identifier and a separately assigned internal number to serve as unique identifiers.
When this happens, one candidate key is usually chosen as the primary key, while the others remain as alternate ways to identify the same tuples. The presence of multiple candidate keys indicates that the relation has more than one valid notion of identity.
3 Role in database design
Candidate keys influence how tables are structured, constrained, and connected. They help database designers preserve meaning, avoid duplication, and establish stable identifiers for stored data.
3.1 Selection of the primary key
From the set of candidate keys, one is selected as the primary key. This chosen key becomes the main official identifier for the table in the database implementation. The other candidate keys remain valid, but they are not used as the table’s principal reference.
The choice of primary key is often based on stability, simplicity, and practicality. Designers usually prefer a key that is short, consistent, and unlikely to change.
3.2 Normalization and redundancy reduction
Candidate keys are closely tied to normalization, the process of organizing data to reduce redundancy and prevent anomalies. By identifying the minimal unique attributes, designers can better determine how data should be separated into related relations.
When candidate keys are understood correctly, it becomes easier to avoid storing repeated facts in multiple places. This helps reduce update inconsistencies and makes the schema more logically compact.
3.3 Integrity constraints
Candidate keys support integrity constraints by ensuring that each tuple can be reliably distinguished from all others. Uniqueness constraints derived from candidate keys help prevent duplicate rows that would otherwise blur record identity.
They also reinforce data consistency. If a relation is defined with key constraints, the database system can reject insertions or updates that would violate uniqueness.
4 Mathematical and formal aspects
Candidate keys can be studied formally using the language of functional dependencies and attribute sets. This makes them useful not only in practical schema design but also in theoretical analysis.
4.1 Functional dependency interpretation
In dependency theory, a set of attributes is a candidate key if it functionally determines all attributes in the relation and is minimal with that property. This means that knowing the key values allows one to infer every other attribute in the tuple.
This interpretation connects candidate keys with logical inference. A key is not just a label; it is a set of attributes with maximal determining power.
4.2 Attribute closure
Attribute closure is the set of attributes that can be derived from a given attribute set using a collection of functional dependencies. If the closure of a set includes all attributes of the relation, then the set is a superkey.
To be a candidate key, the set must also be minimal. Closure calculations are therefore a standard tool for testing whether an attribute set qualifies as a candidate key.
4.3 Determining candidate keys from dependencies
Candidate keys can be identified by examining the functional dependencies of a schema. One begins with attribute sets that appear capable of determining the whole relation, then checks whether any smaller subset does the same job.
This process often requires systematic testing. Dependencies reveal how attributes relate to one another, and candidate keys emerge from the smallest combinations that still determine everything else.
4.4 Relation to key sets in schema theory
In schema theory, candidate keys belong to the broader class of key sets used to study relation structure. These sets describe how tuples are distinguished and how information flows through a schema.
The formal perspective is useful because it separates the notion of identity from physical storage. It treats keys as properties of the schema’s logic rather than of any particular database engine.
5 Algorithms and computation
Finding candidate keys can be computationally demanding, especially in schemas with many attributes and complex dependencies. Various methods are used to search for them or infer them efficiently.
5.1 Exhaustive search methods
A direct approach is to examine combinations of attributes and test whether each combination is a superkey and whether it is minimal. This is conceptually simple but quickly becomes expensive as the number of attributes grows.
Exhaustive search is mainly practical for small schemas or for educational purposes. It provides a clear demonstration of how candidate keys are defined.
5.2 Dependency-based inference
More efficient methods use functional dependencies to narrow the search space. Attributes that must appear in every key, as well as attributes that can be derived from others, help reduce the number of combinations that need to be tested.
These inference methods are widely used in schema analysis tools and theoretical algorithms. They rely on the structure of the dependency set rather than on blind enumeration.
5.3 Complexity considerations
The general problem of finding all candidate keys can be computationally difficult. As the number of attributes increases, the number of possible subsets grows rapidly, making the task expensive in the worst case.
For this reason, database design often combines theoretical reasoning with practical heuristics. Designers may focus on likely keys first and confirm them through dependency analysis.
6 Related concepts
Candidate keys are closely connected to several other key types used in relational databases. These concepts differ in scope and purpose, but they often appear together in schema design.
6.1 Superkey
A superkey is any attribute set that uniquely identifies tuples. It may contain extra attributes beyond those needed for uniqueness. Candidate keys are the minimal superkeys within a relation.
6.2 Primary key
The primary key is the candidate key chosen to serve as the main identifier for a table. It is the key most often used in references, indexing, and database operations.
6.3 Alternate key
An alternate key is a candidate key that was not selected as the primary key. It remains a valid unique identifier and may still be enforced by a uniqueness constraint.
6.4 Foreign key
A foreign key is an attribute or set of attributes in one relation that references a candidate key, usually the primary key, in another relation. It is used to represent relationships between tables.
6.5 Prime attribute
A prime attribute is any attribute that appears in at least one candidate key. Such attributes are important in normalization because they help distinguish key-related from non-key-related data.
7 Practical database considerations
In actual database systems, candidate keys affect usability, maintenance, and performance. Their design has consequences beyond formal correctness.
7.1 Natural versus surrogate keys
A natural key is based on real-world data, such as an identification number or code that already has meaning outside the database. A surrogate key is an artificial identifier created solely for database use.
Candidate keys may be natural or, less commonly, surrogate depending on the schema. Designers often balance semantic clarity against stability and ease of use.
7.2 Composite key design tradeoffs
Composite candidate keys can express genuine business rules, but they may also make references longer and queries more cumbersome. They can be perfectly appropriate when the combined values naturally identify the record.
However, wider keys may complicate joins, indexing, and foreign key references. For that reason, schema designers often evaluate whether a composite key is necessary or whether a simpler identifier would be preferable.
7.3 Indexing and performance implications
Because candidate keys enforce uniqueness, they are often supported by indexes. These indexes can speed up lookups and help maintain constraint checking during inserts and updates.
Performance effects depend on key size, data distribution, and how often the key changes. A compact, stable candidate key is usually easier to index efficiently than a large or frequently modified one.