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.

Practice

1. Which key uniquely identifies each record in a database table and cannot have NULL values?

easy

A. Primary Key

B. Foreign Key

C. Candidate Key

D. Super Key

Solution

1. Step 1: Understand the role of Primary Key

   The primary key uniquely identifies each record and must not be NULL.

2. Step 2: Compare with other keys

   Foreign keys link tables, candidate keys are possible unique keys, super keys may have extra columns.

3. Final Answer:

   Primary Key -> Option A

4. Quick Check:

   Unique and not NULL = Primary Key [OK]

Hint: Primary key = unique + no NULL [OK]

Common Mistakes:

• Confusing foreign key with primary key
• Thinking candidate key is always primary
• Assuming super key must be minimal

2. Which of the following is the correct syntax to declare a primary key on column id in SQL?

easy

A. CREATE TABLE t (id INT SUPER KEY);

B. CREATE TABLE t (id INT FOREIGN KEY);

C. CREATE TABLE t (id INT CANDIDATE KEY);

D. CREATE TABLE t (id INT PRIMARY KEY);

Solution

1. Step 1: Recall SQL syntax for primary key

   Primary key is declared using PRIMARY KEY after the column definition.

2. Step 2: Check other options

   Foreign key requires reference clause, candidate and super key are not SQL keywords.

3. Final Answer:

   CREATE TABLE t (id INT PRIMARY KEY); -> Option D

4. Quick Check:

   Primary key syntax = column INT PRIMARY KEY [OK]

Hint: Primary key uses PRIMARY KEY keyword in SQL [OK]

Common Mistakes:

• Using FOREIGN KEY without REFERENCES
• Trying to declare candidate or super key directly
• Missing PRIMARY KEY keyword

3. Consider a table Orders with columns OrderID, CustomerID, and ProductID. If OrderID is the primary key and CustomerID is a foreign key referencing Customers table, what does this imply?

medium

A. OrderID uniquely identifies each order; CustomerID links to Customers table

B. CustomerID uniquely identifies each order; OrderID links to Customers table

C. ProductID is the primary key; CustomerID is a candidate key

D. OrderID and CustomerID together form a super key

Solution

1. Step 1: Understand primary key role

   OrderID uniquely identifies each order record.

2. Step 2: Understand foreign key role

   CustomerID links Orders to Customers table to maintain data consistency.

3. Final Answer:

   OrderID uniquely identifies each order; CustomerID links to Customers table -> Option A

4. Quick Check:

   Primary key = unique ID, foreign key = link [OK]

Hint: Primary key = unique; foreign key = link to another table [OK]

Common Mistakes:

• Mixing primary and foreign key roles
• Assuming foreign key is unique
• Confusing candidate key with foreign key

4. A table has columns EmpID, Email, and Phone. The EmpID is set as primary key, but duplicate emails exist. What is the problem and how to fix it?

medium

A. Phone should be primary key instead of EmpID

B. Duplicate emails violate candidate key uniqueness; add UNIQUE constraint on Email

C. Primary key allows duplicates; change EmpID to foreign key

D. No problem; duplicates in Email are allowed with primary key on EmpID

Solution

1. Step 1: Identify uniqueness requirements

   Primary key ensures EmpID is unique, but Email duplicates violate candidate key uniqueness if Email should be unique.

2. Step 2: Fix by adding UNIQUE constraint

   To prevent duplicate emails, add UNIQUE constraint on Email column.

3. Final Answer:

   Duplicate emails violate candidate key uniqueness; add UNIQUE constraint on Email -> Option B

4. Quick Check:

   Unique candidate keys need UNIQUE constraint [OK]

Hint: Add UNIQUE constraint to enforce candidate key uniqueness [OK]

Common Mistakes:

• Thinking primary key allows duplicates
• Changing primary key without reason
• Ignoring uniqueness of candidate keys

5. Given a table Students with columns StudentID, Email, and PhoneNumber, which of the following sets can be a super key?

hard

A. {StudentID, Email}

B. {Email, PhoneNumber}

C. All of the above

D. {StudentID}

Solution

1. Step 1: Define super key

   A super key is any set of columns that uniquely identifies a record, even if it has extra columns.

2. Step 2: Analyze each option

   StudentID alone uniquely identifies records; Email and PhoneNumber together can also uniquely identify; adding Email to StudentID still uniquely identifies (extra columns allowed).

3. Final Answer:

   All of the above -> Option C

4. Quick Check:

   Super key = unique set, extra columns allowed [OK]

Hint: Super key = unique columns, can include extras [OK]

Common Mistakes:

• Thinking super key must be minimal
• Confusing super key with candidate key
• Assuming single column only