Candidate Keys and Superkeys in MySQL

Course: MySQL Training

Topic: Candidate Keys and Superkeys

Course Link: JustAcademy MySQL Training Course

Demo Link: Register for a Course Demo at JustAcademy

1. Introduction to Candidate Keys and Superkeys

In relational database management systems, keys are used to identify records uniquely and maintain data accuracy. When designing a MySQL database, it is important to determine which columns can uniquely identify each row in a table.

Two important concepts in database design are superkeys and candidate keys. A superkey is any set of one or more columns that uniquely identifies a row. A candidate key is a minimal superkey, meaning that no column can be removed from it without losing the ability to identify rows uniquely.

For example, consider a students table containing student_id, email, student_name, and phone. If both student_id and email are unique, each may be a candidate key. A combination such as (student_id, student_name) is also a superkey, but it is not a candidate key because student_id alone is sufficient.

These concepts are foundational to primary key selection, normalization, relational schema design, and the prevention of duplicate records.

1.1 Learning Objectives

• Understand the meaning of database keys.
• Define superkeys and candidate keys.
• Differentiate candidate keys, superkeys, and primary keys.
• Identify minimal and non-minimal unique column combinations.
• Use MySQL constraints to enforce uniqueness.
• Understand composite candidate keys.
• Identify candidate keys by analyzing sample data and functional dependencies.
• Apply key concepts to real-world database design.

2. Understanding Database Keys

A database key is a column or combination of columns used to identify records or establish relationships between tables. Different types of keys serve different purposes.

2.1 Common Types of Keys

A composite key can be a candidate key, primary key, or superkey depending on how its columns identify rows. These terms describe different aspects of a key and are not always mutually exclusive.

3. What Is a Superkey?

A superkey is a column or set of columns that uniquely identifies each row in a relational table. A superkey may contain more columns than necessary to achieve uniqueness.

If a table contains a unique student_id, then all of the following can be superkeys:

• student_id
• (student_id, student_name)
• (student_id, email)
• (student_id, student_name, email)

Each set includes student_id, which already uniquely identifies a student. Adding extra columns does not remove the uniqueness property.

3.1 Example Students Table

CREATE TABLE students (
    student_id INT NOT NULL,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    phone VARCHAR(20),
    PRIMARY KEY (student_id),
    UNIQUE (email)
);

Assume that the business rules require every student to have a unique student ID and a unique email address. The primary key and unique constraint enforce those rules in MySQL.

In this example, student_id uniquely identifies each row. Therefore, (student_id, student_name) also uniquely identifies each row, even though the name is not required for uniqueness.

3.2 Characteristics of a Superkey

• A superkey uniquely identifies each row.
• It may contain one column or multiple columns.
• It may contain unnecessary columns.
• Every candidate key is a superkey.
• Not every superkey is a candidate key.
• A superkey is defined by the table's valid data rules and functional dependencies, not merely by the values currently visible in a small sample.

3.3 Superkey Example

Suppose student_id is unique. Consider these column sets:

4. What Is a Candidate Key?

A candidate key is a minimal superkey. It uniquely identifies every row, and removing any column from it causes it to lose that uniqueness property.

Suppose a student table has a unique student_id and a unique, non-NULL email. Both columns may be candidate keys because each independently identifies a student.

However, (student_id, student_name) is not a candidate key when student_id alone is unique. The combination contains an unnecessary attribute and is therefore not minimal.

4.1 Characteristics of Candidate Keys

• A candidate key uniquely identifies each row.
• It is minimal: no proper subset of its columns is a superkey.
• A table can have multiple candidate keys.
• A candidate key may contain one column or multiple columns.
• A candidate key can be selected as the primary key.
• Candidate keys not selected as the primary key are commonly called alternate keys.

4.2 Candidate Key Example

Consider a table with these columns:

STUDENTS(
    student_id,
    email,
    student_name,
    phone
)

Assume that:

• student_id is unique and mandatory.
• email is unique and mandatory.
• student_name is not unique.
• phone is not guaranteed to be unique.

Under these rules, the candidate keys are:

1. {student_id}
2. {email}

The combination {student_id, email} is a superkey but not a candidate key because either column alone can identify the row.

5. Difference Between Superkeys and Candidate Keys

5.1 Easy Way to Remember

Superkey = uniqueness. It can contain extra columns.

Candidate key = uniqueness + minimality. Every column in the candidate key is necessary for that particular key to remain unique.

6. Relationship Between Superkeys, Candidate Keys, and Primary Keys

These concepts are connected but serve different purposes in database design.

1. Identify the columns or column combinations that uniquely identify rows.
2. Remove unnecessary columns from those combinations to identify candidate keys.
3. Choose one candidate key as the primary key.
4. Retain the other candidate keys as alternate keys and enforce their uniqueness when required.

For example, if both student_id and email are candidate keys, the database designer may choose student_id as the primary key and retain email as a unique alternate key.

CREATE TABLE students (
    student_id INT NOT NULL,
    email VARCHAR(150) NOT NULL,
    student_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (student_id),
    UNIQUE (email)
);

Here, student_id is the primary key. The email uniqueness constraint enforces a second candidate key, assuming email is mandatory and the business rules guarantee its uniqueness.

7. Identifying Candidate Keys from Functional Dependencies

In relational database theory, a functional dependency describes how one set of attributes determines another set of attributes.

A functional dependency written as X -> Y means that if two rows have the same values for attribute set X, they must have the same values for attribute set Y.

For example, if each student ID belongs to exactly one student name, then:

student_id -> student_name

If every student ID also determines the email address, then:

student_id -> student_name, email

To identify a candidate key, determine whether a set of attributes functionally determines every attribute in the relation. Then check whether any proper subset can do the same.

7.1 Attribute Closure

The closure of an attribute set X, written as X+, is the set of all attributes that can be determined from X using the given functional dependencies.

If X+ contains every attribute in a relation, X is a superkey. If no proper subset of X is a superkey, X is a candidate key.

7.2 Example of Attribute Closure

Suppose a table has the following attributes:

STUDENT(
    student_id,
    email,
    student_name,
    department_id
)

Assume the following functional dependencies:

student_id -> email, student_name, department_id
email -> student_id, student_name, department_id

For student_id, the closure is:

{student_id}+ =
{student_id, email, student_name, department_id}

Because the closure contains all attributes, {student_id} is a superkey. It is also minimal because it contains only one attribute, so it is a candidate key.

Similarly, the closure of {email} contains all attributes, making email another candidate key.

8. Composite Candidate Keys

Sometimes no single column can uniquely identify a row. In such cases, a combination of columns may form a candidate key. This is called a composite candidate key when the combination is minimal and unique.

8.1 Example: Student Course Enrollment

A student may enroll in several courses, and each course may contain several students. A table can record each enrollment using both student and course identifiers.

ENROLLMENTS(
    student_id,
    course_id,
    enrollment_date,
    grade
)

Assume that each student can enroll in a particular course only once. Neither student_id nor course_id is unique by itself, but their combination is unique.

CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    grade VARCHAR(5),
    PRIMARY KEY (student_id, course_id)
);

The combination (student_id, course_id) is a candidate key under the stated business rule and is selected as the primary key in this implementation.

8.2 Composite Key Example Data

Student 101 appears more than once because the student takes multiple courses. Course 501 also appears more than once because multiple students enroll in it. However, each student-course pair is unique.

8.3 Why Minimality Matters

The combination (student_id, course_id) is minimal if neither column alone uniquely identifies an enrollment. Adding enrollment_date creates a larger superkey, but it is not a candidate key because the first two columns already uniquely identify the row.

9. Candidate Keys in MySQL

MySQL does not have a special SQL keyword named CANDIDATE KEY. Candidate keys are a relational design concept. In practice, they are implemented by selecting one candidate key as the primary key and enforcing other candidate keys using suitable UNIQUE constraints and NOT NULL definitions.

9.1 Implement a Single-Column Candidate Key

CREATE TABLE employees (
    employee_id INT NOT NULL,
    employee_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (employee_id)
);

The employee ID is the chosen candidate key and primary key.

9.2 Implement an Alternate Candidate Key

CREATE TABLE employees (
    employee_id INT NOT NULL,
    work_email VARCHAR(150) NOT NULL,
    employee_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (employee_id),
    UNIQUE (work_email)
);

Here, both employee_id and work_email can be candidate keys if each is guaranteed to identify an employee. The primary key is the chosen key, while the email is enforced through a unique constraint.

9.3 Implement a Composite Candidate Key

CREATE TABLE inventory (
    warehouse_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    PRIMARY KEY (warehouse_id, product_id)
);

If each product has only one inventory record per warehouse, the combination (warehouse_id, product_id) can identify each row uniquely.

10. Superkeys and Candidate Keys in SQL Examples

Consider the following table definition:

CREATE TABLE employees (
    employee_id INT NOT NULL,
    national_id VARCHAR(30) NOT NULL,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    PRIMARY KEY (employee_id),
    UNIQUE (national_id)
);

Assume both employee ID and national ID are guaranteed to be unique and mandatory by the business rules.

The table shows that candidate keys are minimal superkeys. Larger sets containing an already unique key remain superkeys, but they are not minimal.

11. NULL Values and Unique Constraints in MySQL

When implementing candidate keys in MySQL, NULL values require special attention. A candidate key in relational theory must uniquely identify every tuple and cannot rely on an unknown value as a complete identifier. For an ordinary single-column candidate key, the column should be mandatory.

MySQL generally permits multiple NULL values in a UNIQUE index. Therefore, UNIQUE(email) alone does not ensure that every row has an email address or that only one row can have a NULL email.

11.1 Example of a Nullable Unique Column

CREATE TABLE users (
    user_id INT NOT NULL PRIMARY KEY,
    email VARCHAR(150) UNIQUE
);

This table allows multiple rows with NULL email values, although duplicate non-NULL email values are rejected. If email must identify every user, declare it NOT NULL:

CREATE TABLE users (
    user_id INT NOT NULL PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE
);

In the second definition, each row must have an email address, and non-NULL email values must be unique. This is a better implementation when email is intended to act as an alternate candidate key.

12. Choosing a Primary Key from Candidate Keys

A table may have several candidate keys, but it can have only one primary key constraint. The chosen key should be reliable, minimal, and suitable for referencing by other tables.

12.1 Factors to Consider

• Uniqueness: The key must uniquely identify every row.
• Minimality: Avoid unnecessary columns in the key.
• Stability: Prefer values that are unlikely to change.
• Required values: The key should not depend on missing values.
• Size: A compact key can make indexes and related foreign keys smaller.
• Meaning: A natural key may have business meaning, while a surrogate key is generated primarily for identification.
• Privacy: Avoid exposing sensitive personal identifiers unnecessarily.

12.2 Natural Key Example

A natural key is based on a real-world attribute, such as a guaranteed unique product code or an official business identifier.

CREATE TABLE products (
    product_code VARCHAR(30) NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (product_code)
);

This design is appropriate only if the product code is guaranteed to be unique, mandatory, and sufficiently stable.

12.3 Surrogate Key Example

A surrogate key is an artificial identifier created for database use.

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_code VARCHAR(30) NOT NULL UNIQUE,
    product_name VARCHAR(100) NOT NULL
);

Here, product_id is the primary key, while product_code acts as an alternate candidate key when product codes are unique and mandatory. The surrogate key can remain stable even if a business product code changes.

13. Common Mistakes When Identifying Keys

14. Finding Duplicate Values Before Defining a Candidate Key

Before adding a UNIQUE constraint, check whether the intended column contains duplicate values.

14.1 Find Duplicate Emails

SELECT
    email,
    COUNT(*) AS total
FROM employees
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;

This query identifies non-NULL email values that appear more than once. If the query returns rows, review and correct the duplicates before enforcing email uniqueness.

14.2 Find Duplicate Composite Key Combinations

SELECT
    warehouse_id,
    product_id,
    COUNT(*) AS total
FROM inventory
GROUP BY warehouse_id, product_id
HAVING COUNT(*) > 1;

This query checks whether a combination of warehouse and product appears more than once. If the business rule requires one inventory record per product per warehouse, the duplicate combinations must be resolved before enforcing the composite key.

14.3 Check Missing Values

SELECT *
FROM employees
WHERE national_id IS NULL
   OR national_id = '';

This query finds missing or empty national ID values. Whether empty strings are valid depends on the application's data rules; if a column is intended to be a mandatory candidate key, invalid or missing values should be handled consistently.

15. Adding Uniqueness Constraints to Existing Tables

If a table already exists, you can enforce uniqueness through a primary key or a UNIQUE constraint. Existing data must satisfy the new constraint.

15.1 Add a UNIQUE Constraint

ALTER TABLE employees
ADD CONSTRAINT uq_employees_national_id
UNIQUE (national_id);

This statement succeeds only if the existing non-NULL national ID values do not violate the unique constraint. If the column is intended to be a complete candidate key, also ensure it is defined as NOT NULL.

15.2 Add a Composite UNIQUE Constraint

ALTER TABLE inventory
ADD CONSTRAINT uq_inventory_warehouse_product
UNIQUE (warehouse_id, product_id);

This constraint prevents duplicate non-NULL warehouse-product combinations. If the pair must identify every inventory row, both columns should normally be declared NOT NULL.

15.3 Add a Primary Key

ALTER TABLE employees
ADD PRIMARY KEY (employee_id);

This operation requires that the column contain no duplicate values and no NULL values. The table must not already have a primary key. Check the existing schema and data before executing the statement.

16. Candidate Keys and Database Normalization

Candidate keys play an important role in normalization, the process of organizing database tables to reduce unnecessary duplication and avoid data anomalies.

16.1 First Normal Form (1NF)

First Normal Form requires each column to contain a single value from its defined domain and each row to be distinguishable. A proper key helps identify individual rows, but having a candidate key alone does not automatically guarantee that every normalization requirement is satisfied.

16.2 Second Normal Form (2NF)

Second Normal Form requires a relation to be in 1NF and every non-prime attribute to depend on the whole of every candidate key, not just part of a composite candidate key.

For example, if a table uses (student_id, course_id) as a candidate key but stores student_name, and student ID alone determines the student's name, then student name depends on only part of the composite key. Separating student details into a students table can remove that partial dependency.

16.3 Third Normal Form (3NF)

Third Normal Form addresses certain transitive dependencies. A relation in 3NF satisfies the formal condition that, for every non-trivial functional dependency, the determinant is a superkey or the dependent attribute is prime, where a prime attribute belongs to at least one candidate key.

Understanding candidate keys helps database designers analyze functional dependencies and determine whether a table is properly normalized.

17. Real-World Applications of Candidate Keys and Superkeys

17.1 Student Management System

Possible candidate keys include a mandatory student ID and a verified unique student email. Student name alone is normally not a reliable candidate key because names can be repeated.

17.2 E-Commerce System

A product table may use a generated product ID as its primary key and a unique SKU as an alternate candidate key, provided each SKU is mandatory and unique.

17.3 Banking System

An account ID can be a primary key, while a guaranteed unique account number may serve as an alternate candidate key. Sensitive customer identifiers should not be selected without considering privacy and operational requirements.

17.4 Employee Management System

An employee number may serve as the primary key, while a mandatory and unique work email can be another candidate key if the organization guarantees that it identifies one employee at a time.

17.5 Inventory Management System

The combination of warehouse ID and product ID can be a candidate key when the business permits only one inventory record for each product in each warehouse.

18. Practical Project: Designing Keys for a Course Registration Database

Project Objective: Design tables for students, courses, and registrations. Identify candidate keys and superkeys, and implement the appropriate constraints in MySQL.

18.1 Create the Students Table

CREATE TABLE students (
    student_id INT NOT NULL AUTO_INCREMENT,
    email VARCHAR(150) NOT NULL,
    student_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (student_id),
    CONSTRAINT uq_students_email UNIQUE (email)
) ENGINE = InnoDB;

The generated student ID is the primary key. Email is an alternate candidate key under the business rule that each student must have a unique email address.

18.2 Create the Courses Table

CREATE TABLE courses (
    course_id INT NOT NULL AUTO_INCREMENT,
    course_code VARCHAR(30) NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (course_id),
    CONSTRAINT uq_courses_code UNIQUE (course_code)
) ENGINE = InnoDB;

The course ID is the primary key, while the unique course code is an alternate candidate key if course codes are mandatory and unique.

18.3 Create the Registrations Table

CREATE TABLE registrations (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    registration_date DATE NOT NULL,
    PRIMARY KEY (student_id, course_id),
    CONSTRAINT fk_registrations_student
        FOREIGN KEY (student_id)
        REFERENCES students(student_id),
    CONSTRAINT fk_registrations_course
        FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
) ENGINE = InnoDB;

The combination of student ID and course ID is the primary key, assuming each student can register for a particular course only once. Each foreign key ensures that a registration refers to an existing student and course.

18.4 Insert Sample Data

INSERT INTO students (email, student_name)
VALUES
    ('[email protected]', 'Aarav Sharma'),
    ('[email protected]', 'Meera Patel');

INSERT INTO courses (course_code, course_name)
VALUES
    ('MYSQL101', 'MySQL Fundamentals'),
    ('SQL201', 'Advanced SQL');

INSERT INTO registrations
    (student_id, course_id, registration_date)
VALUES
    (1, 1, '2026-02-01'),
    (1, 2, '2026-02-02'),
    (2, 1, '2026-02-03');

18.5 Test the Candidate Key Constraints

-- Expected to fail: duplicate student email
INSERT INTO students (email, student_name)
VALUES ('[email protected]', 'Another Aarav');

-- Expected to fail: duplicate course code
INSERT INTO courses (course_code, course_name)
VALUES ('MYSQL101', 'Different Course');

-- Expected to fail: duplicate student-course pair
INSERT INTO registrations
    (student_id, course_id, registration_date)
VALUES
    (1, 1, '2026-02-10');

These statements violate the configured UNIQUE or PRIMARY KEY constraints. The constraints protect the business rules from accidental duplicate records.

18.6 Project Questions

• Identify all candidate keys in the students table.
• Explain why (student_id, email) is a superkey but not a candidate key.
• Explain why course ID and course code can both be candidate keys.
• Explain why the registration table uses a composite primary key.
• Determine what would happen if the business allowed a student to register for the same course multiple times.
• Describe how the schema would change if registration attempts needed to be stored separately.

19. Practice Exercises

1. Create an employees table with employee ID, work email, name, and department.
2. Identify which columns could be candidate keys under reasonable business rules.
3. List at least five possible superkeys if employee ID is unique.
4. Explain why a set containing employee ID and department is not minimal.
5. Create a table in which a composite candidate key consists of two columns.
6. Write a query to identify duplicate email addresses before adding a UNIQUE constraint.
7. Explain the difference between a candidate key and an alternate key.
8. Explain why a UNIQUE column that permits NULL is not necessarily a complete candidate key.
9. Describe how functional dependencies help identify candidate keys.
10. Choose a primary key for a product table and justify your decision.

20. Interview Questions and Answers

Q1. What is a superkey?

A superkey is a set of one or more attributes that uniquely identifies every row in a relation. It may contain unnecessary attributes.

Q2. What is a candidate key?

A candidate key is a minimal superkey. It uniquely identifies rows, and removing any attribute causes it to lose that property.

Q3. What is the main difference between a superkey and a candidate key?

A superkey does not need to be minimal, whereas a candidate key must be minimal. Every candidate key is a superkey, but not every superkey is a candidate key.

Q4. Can a table have multiple candidate keys?

Yes. A table can have several candidate keys if multiple minimal attribute sets uniquely identify its rows.

Q5. How many primary keys can a table have?

A table can have only one primary key constraint. That primary key can consist of one column or several columns.

Q6. What is an alternate key?

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

Q7. Is every primary key a candidate key?

Yes. A primary key is the candidate key selected to be the main identifier for a table.

Q8. Can a candidate key contain multiple columns?

Yes. When no proper subset of the columns uniquely identifies each row but the complete combination does, the combination is a composite candidate key.

Q9. Is a composite superkey always a candidate key?

No. It is a candidate key only if it is minimal. If one of its proper subsets is already a superkey, the larger set is not a candidate key.

Q10. Does MySQL have a CANDIDATE KEY keyword?

No. Candidate key is a relational database concept. In MySQL, candidate keys are commonly implemented using a primary key or a UNIQUE constraint together with appropriate NOT NULL definitions.

Q11. Can a name be a candidate key?

Only if the database's business rules guarantee that the name is unique and mandatory for every row. Personal names usually do not meet this requirement.

Q12. Can a table have a UNIQUE constraint and a primary key?

Yes. A table can have one primary key and multiple UNIQUE constraints. These can enforce alternate candidate keys or other uniqueness requirements.

Q13. How do functional dependencies help identify candidate keys?

They show which attributes determine other attributes. Attribute closure can be used to determine whether a set of attributes determines the entire relation; minimality is then checked to identify candidate keys.

Q14. Why is minimality important?

Minimality ensures that a candidate key contains no unnecessary attributes. This makes key definitions precise and helps identify the actual minimal identifiers of a relation.

Q15. What is the difference between a natural key and a surrogate key?

A natural key comes from meaningful real-world data, such as a guaranteed unique product code. A surrogate key is an artificial identifier created for database use, such as an auto-incrementing integer.

21. Quick Revision Checklist

• A superkey uniquely identifies each row.
• A candidate key is a minimal superkey.
• Every candidate key is a superkey.
• A superkey may contain unnecessary columns.
• A table can have multiple candidate keys.
• One candidate key is selected as the primary key.
• Other candidate keys can be implemented as alternate unique keys.
• A composite candidate key contains multiple necessary columns.
• Functional dependencies and attribute closure help identify candidate keys.
• MySQL commonly implements candidate key rules through PRIMARY KEY, UNIQUE, and NOT NULL constraints.
• MySQL permits multiple NULL values in many UNIQUE-index scenarios, so NOT NULL is important when the attribute must identify every row.
• Always validate business rules and existing data before adding uniqueness constraints.

22. Conclusion

Candidate keys and superkeys are fundamental concepts in relational database design. A superkey is any set of attributes that uniquely identifies a row, while a candidate key is a minimal superkey with no unnecessary attributes. A primary key is the candidate key selected as the main identifier, and other candidate keys can serve as alternate keys.

In MySQL, these concepts guide the use of PRIMARY KEY, UNIQUE, and NOT NULL constraints. Understanding minimality, composite keys, functional dependencies, and NULL behavior helps database designers create accurate, consistent, and maintainable schemas.

Learn More: Explore the JustAcademy MySQL Training Course for additional database learning opportunities, or register for a course demo.