Skip to content

Click to use (opens in a new tab)

What is Candidate Key

Introduction to Candidate Key

A Candidate Key in database theory is a column or a combination of columns that can uniquely identify each row in a table. A candidate key must satisfy two properties:

  1. Uniqueness: No two rows can have the same combination of values for the candidate key.
  2. Minimality: The set of columns forming the candidate key should not contain any unnecessary columns; no proper subset of the columns can also be a candidate key.

Among all candidate keys, one is chosen as the Primary Key. All other candidate keys are known as Alternate Keys.

Characteristics of Candidate Keys

  • Uniqueness: Ensures that each tuple (row) in the relation (table) is distinct.
  • Minimality: Ensures that no attribute within the candidate key can be removed without losing its uniqueness property.

Example with Code

Creating a Table with Candidate Keys

Let's create a simple SQL table employees and identify the candidate keys.

CREATE TABLE employees (
    employee_id INT NOT NULL,
    ssn VARCHAR(11) NOT NULL,  -- Social Security Number
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    PRIMARY KEY (employee_id)
);

In this table:

  • employee_id is chosen as the primary key.
  • ssn could also serve as a candidate key because it uniquely identifies each employee.

Identifying Candidate Keys

To formally identify candidate keys, you would typically use functional dependencies and normalization techniques. However, in practical terms, you can inspect the table design and data constraints.

Scenario: Multiple Candidate Keys

Consider an extended version of the employees table where email is added as another unique identifier.

CREATE TABLE employees (
    employee_id INT NOT NULL,
    ssn VARCHAR(11) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    PRIMARY KEY (employee_id)
);

Here:

  • employee_id is the primary key.
  • Both ssn and email are alternate candidate keys since they uniquely identify each employee.

Conclusion

Understanding candidate keys is crucial for designing efficient and normalized databases. By ensuring that each table has properly defined candidate keys, you can maintain data integrity and facilitate effective data retrieval and management.


---

## Chat2DB - AI Text2SQL Tool for Easy Database Management

[![Click to use](/image/blog/chat2db.png)](https://app.chat2db.ai/)

## What can Chat2DB do?

- [AI SQL Editor](https://chat2db.ai/feature/AI%20SQL%20Editor)
- [AI SQL Query Generator](https://chat2db.ai/feature/AI%20SQL%20Query%20Generator)
- [Analyze Excel Data with AI](https://chat2db.ai/feature/Analyze%20Excel%20Data%20with%20AI)
- [Best AI Tool for Data Analysis](https://chat2db.ai/feature/Best%20AI%20Tool%20for%20Data%20Analysis)
- [AI Dashboard with Chat2DB](https://chat2db.ai/feature/AI%20Dashboard%20with%20Chat2DB)
- [AI Database Client](https://chat2db.ai/feature/AI%20Database%20Client)