Keys (primary, candidate, foreign, super) in DBMS Theory - Full Explanation

Introduction

Imagine you have a big collection of information about people, products, or events. To find or connect specific pieces of information quickly and correctly, you need a way to identify and link them. Keys in databases solve this problem by uniquely identifying records and establishing relationships between tables.

Explanation

Primary Key

A primary key is a special column or set of columns in a table that uniquely identifies each record. No two records can have the same primary key value, and it cannot be empty. This ensures that every row is distinct and easy to find.

The primary key uniquely identifies each record in a table and cannot be null.

Candidate Key

Candidate keys are all the possible columns or sets of columns that could serve as a primary key because they uniquely identify records. Among these candidates, one is chosen as the primary key. The others remain as candidate keys.

Candidate keys are potential unique identifiers from which the primary key is selected.

Foreign Key

A foreign key is a column or set of columns in one table that refers to the primary key in another table. It creates a link between the two tables, allowing data to be connected and ensuring consistency across related records.

Foreign keys link records between tables by referring to primary keys in another table.

Super Key

A super key is any set of columns that can uniquely identify a record in a table. It may contain extra columns beyond what is necessary. All candidate keys and the primary key are types of super keys, but not all super keys are candidate keys.

Super keys uniquely identify records but may include unnecessary columns.

Real World Analogy

Think of a library where every book has a unique barcode (primary key). Some books might also have other unique identifiers like ISBN numbers (candidate keys). When you borrow a book, your library card links to the book's barcode (foreign key). A super key would be like using the barcode plus the book's shelf location together to identify it, even though the barcode alone is enough.

Primary Key → The unique barcode on each book that identifies it alone

Candidate Key → Other unique identifiers like ISBN numbers that could also identify the book

Foreign Key → Your library card linking to the book's barcode when you borrow it

Super Key → Using the barcode plus shelf location together to identify the book

Diagram

Diagram

┌─────────────┐       ┌─────────────┐
│   Table A   │       │   Table B   │
│─────────────│       │─────────────│
│ PK: ID      │◄──────│ FK: ID      │
│ Name        │       │ Description │
└─────────────┘       └─────────────┘

Candidate Keys: {ID, Email}
Super Key example: {ID, Name}

This diagram shows two tables where Table B uses a foreign key to refer to the primary key in Table A, with candidate and super keys noted.

Key Facts

Primary Key → A column or set of columns that uniquely identifies each record and cannot be null.

Candidate Key → A set of columns that can uniquely identify records and from which the primary key is chosen.

Foreign Key → A column or set of columns in one table that refers to the primary key in another table to link data.

Super Key → Any set of columns that uniquely identifies records, possibly including extra columns.

Code Example

DBMS Theory

CREATE TABLE Departments (
  DepartmentID INT PRIMARY KEY,
  DepartmentName VARCHAR(100)
);

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  Email VARCHAR(255) UNIQUE,
  DepartmentID INT,
  FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Common Confusions

Believing that a foreign key must be unique like a primary key.

Believing that a foreign key must be unique like a primary key. A foreign key can have duplicate values because it links many records to the same primary key in another table.

Thinking all super keys are candidate keys.

Thinking all super keys are candidate keys. Super keys may include unnecessary columns, while candidate keys are minimal sets that uniquely identify records.

Assuming the primary key is the only candidate key.

Assuming the primary key is the only candidate key. There can be multiple candidate keys, but only one is chosen as the primary key.

Summary

Keys help uniquely identify records and connect data across tables in a database.

Primary keys are chosen from candidate keys and must be unique and not null.

Foreign keys link tables by referring to primary keys, enabling relationships.