Difference Between Super Key And Candidate Key

What is a Key in DBMS?

Key in DBMS is an attribute or set of attributes which helps you to identify a row(tuple) in a relation(table). They allow you to find the relation between two tables. Keys help you uniquely identify a row in a table by a combination of one or more columns in that table. Key is also helpful for finding unique record or row from the table. Database key is also helpful for finding unique record or row from the table.

What is Super Key?

Super key is a single key or a group of multiple keys that can uniquely identify tuples in a table. Super Key can contain multiple attributes that might not be able to independently identify tuples in a table, but when grouped with certain keys, they can identify tuples uniquely. All super keys can’t be candidate keys but its reverse is true. In a relation, number of super keys are more than number of candidate keys.

Example

As an example, a table used to store customer master details could contain columns such as:

Customer name
Customer ID
Social Security number (SSN)
Address
Date of birth

A certain set of columns may be extracted and guaranteed unique to each customer. Examples of superkeys are as follows:

Name+SSN+Birthdate
ID+Name+SSN

However, this process may be further reduced. It can be assumed that the customer ID is unique to each customer. Therefore, the superkey may be reduced to just one field, customer ID, which is the candidate key. However, to ensure absolute uniqueness, a composite candidate key may be formed by combining customer ID with SSN.

What you need to know about Super key

A super key is any combination of columns that uniquely identifies a row in a table.
A super key is any set of attributes for which the values are guaranteed to be unique for all possible sets of tuples in a table at all times.
Super key is an attribute (or set of attributes) that is used to uniquely identify all attributes in a relation.
Various Super keys combined together creates the criteria to select the candidate Keys.
All super keys can’t be candidate keys.
Super key’s attributes can contain NULL values.
In a relation, number of super keys are more than number of candidate keys.

What is Candidate Key?

A candidate key, or simply a key, of a relational database is a minimal superkey. In other words, it is any set of columns that have a unique combination of values in each row, with the additional constraint that removing any column would possibly produce duplicate rows.

They are single column in that they are not made by combining multiple columns. From this set of candidate keys, a primary key can be chosen, and doing this can prove difficult. Candidate keys are a subset of the super key superset.

The candidate key helps in determining the prime and non-prime attributes of a table and ensures the integrity of the data by preventing duplicate data. Hence, the candidate key is an important concept for designing of the schema of a database.

What you need to know about Candidate Key

A candidate key is a super key which cannot have any columns removed from it without losing the unique identification property.
A candidate key is simply the ‘’shortest’’ super key. Candidate Key are individual columns in a table that qualifies for uniqueness of each row/tuple. Every table must have at least one candidate key but at the same time can have several.
Various Candidate keys combined together creates criteria to select the primary keys.
All candidate keys are super keys.
While in a relation, number of candidate keys are less than number of super keys.