Candidate Key vs Primary Key vs Super Key vs Foreign Key: Difference, Examples and Types in DBMS
Candidate Key vs Primary Key vs Super Key vs Foreign Key is an important topic in DBMS, RDBMS, SQL, relational database design and computer science. Database keys are used to identify records, maintain uniqueness and establish relationships between tables.
Although these four keys are related, they do not perform exactly the same function. A Super Key can uniquely identify a record, a Candidate Key is a minimal super key, a Primary Key is the candidate key selected to identify records, and a Foreign Key is used to establish a relationship between tables.
Understanding the difference between candidate key, primary key, super key and foreign key is especially useful for BCA, B.Tech, MCA, computer science and database management students preparing for examinations, interviews and SQL programming.
In this detailed guide, we will explain all four database keys with simple examples, characteristics, SQL examples, real-world applications, advantages, limitations and a detailed parameter-wise comparison.
Previous Article: Primary Key vs Foreign Key: Difference, Examples, Types and Uses in DBMS
Also Read: What is DBMS? Database Management System
Related Topic: Database Normalization
What is a Database Key in DBMS?
A database key is an attribute or a combination of attributes used to identify records, enforce uniqueness or establish relationships between tables in a relational database.
Keys are fundamental components of relational database management systems. They help databases organize information and maintain data integrity.
Common types of database keys include:
- Super Key
- Candidate Key
- Primary Key
- Foreign Key
- Alternate Key
- Composite Key
- Unique Key
- Surrogate Key
What is a Super Key in DBMS?
A Super Key is a column or combination of columns that can uniquely identify each record in a database table.
A super key may contain additional attributes that are not necessary for unique identification.
For example, consider a Student table:
| Student_ID | Student_Name | Course | |
|---|---|---|---|
| 101 | Rahul | rahul@example.com | BCA |
| 102 | Priya | priya@example.com | BCA |
| 103 | Amit | amit@example.com | BCA |
If Student_ID is unique, then Student_ID alone can be a super key.
The combination Student_ID + Student_Name can also be a super key because Student_ID already uniquely identifies the record.
Similarly, if Email is guaranteed to be unique, Email can also be a super key.
Characteristics of a Super Key
- A super key uniquely identifies a record.
- It can contain one or more attributes.
- It may contain unnecessary attributes.
- Every candidate key is a super key.
- A primary key is also a super key.
- A table can have many possible super keys.
What is a Candidate Key in DBMS?
A Candidate Key is a minimal super key that uniquely identifies each record in a database table.
The word minimal is important. A candidate key must uniquely identify a record, but none of its attributes can be removed without losing that uniqueness.
For example, suppose both Student_ID and Email are unique:
- Student_ID → Candidate Key
- Email → Candidate Key
- Student_ID + Student_Name → Super Key, but not a candidate key because Student_Name is unnecessary.
Characteristics of a Candidate Key
- It uniquely identifies each record.
- It is minimal.
- It cannot contain unnecessary attributes.
- A table can have more than one candidate key.
- One candidate key is normally selected as the primary key.
- Other candidate keys can become alternate keys.
What is a Primary Key in DBMS?
A Primary Key is the candidate key selected by the database designer to uniquely identify records in a table.
A table has one primary key constraint, although the primary key itself can contain multiple columns.
For example, if a Student table has both Student_ID and Email as candidate keys, the database designer may select Student_ID as the primary key.
Characteristics of a Primary Key
- It uniquely identifies each record.
- Duplicate primary key values are not permitted.
- A primary key cannot contain NULL values.
- There is one primary key constraint per table.
- It may consist of one or multiple columns.
- It can be referenced by foreign keys.
- It supports entity integrity.
What is a Foreign Key in DBMS?
A Foreign Key is a column or combination of columns in one table that references a primary key or suitable unique key in another table.
The main purpose of a foreign key is to establish a relationship between tables and support referential integrity.
For example, consider the following Customer table:
| Customer_ID | Customer_Name |
|---|---|
| 101 | Rahul |
| 102 | Priya |
An Orders table can contain Customer_ID as a foreign key:
| Order_ID | Customer_ID | Amount |
|---|---|---|
| 501 | 101 | 1500 |
| 502 | 101 | 2500 |
| 503 | 102 | 900 |
Here, Customer_ID in the Customer table is the primary key, while Customer_ID in Orders is the foreign key.
Relationship Between Super Key, Candidate Key and Primary Key
The relationship between these keys can be understood using a simple hierarchy:
SUPER KEY
Contains attributes that can uniquely identify a record.
↓
CANDIDATE KEY
A minimal super key.
↓
PRIMARY KEY
One candidate key selected as the main identifier.
Therefore, every primary key is a candidate key, every candidate key is a super key, but every super key is not necessarily a candidate key.
Candidate Key vs Primary Key vs Super Key vs Foreign Key: Detailed Difference
The following comparison explains the difference between super key, candidate key, primary key and foreign key using important DBMS parameters.
| Parameter | Super Key | Candidate Key | Primary Key | Foreign Key |
|---|---|---|---|---|
| 1. Basic Meaning | A set of attributes that can uniquely identify a record. | A minimal super key that uniquely identifies a record. | A candidate key selected as the main identifier of records. | An attribute or set of attributes used to reference a key in another table. |
| 2. Main Purpose | Provides a possible way of uniquely identifying records. | Provides a minimal unique identifier that can be selected as the primary key. | Uniquely identifies every record in the table. | Connects records between related tables. |
| 3. Uniqueness | Must uniquely identify records. | Must uniquely identify records. | Must uniquely identify records. | Does not normally need to be unique. |
| 4. Minimality | Not necessarily minimal. | Must be minimal. | Must be a candidate key and therefore minimal. | Minimality depends on the relationship and referenced key. |
| 5. Number in a Table | There can be many possible super keys. | There can be multiple candidate keys. | There is one primary key constraint per table. | A table can have multiple foreign key constraints. |
| 6. NULL Values | Depends on whether it is being considered as a unique identifying key. | Candidate key attributes are expected to provide unique identification and are generally treated as non-null. | NULL values are not permitted. | NULL may be allowed when the relationship is optional and the column permits NULL. |
| 7. Duplicate Values | Cannot produce duplicate complete key combinations. | Cannot produce duplicate complete key combinations. | Duplicate values are not allowed. | Duplicate values are normally allowed. |
| 8. Selection | It is a broad set of possible unique identifiers. | Candidate keys are selected from the available super keys. | One candidate key is selected as the primary key. | It is defined to reference a key in another table. |
| 9. Attributes | Can contain unnecessary attributes. | Contains only necessary attributes required for uniqueness. | Contains the attributes of the selected candidate key. | Contains attributes matching the referenced key's structure. |
| 10. Example | Student_ID + Student_Name. | Student_ID or Email. | Student_ID. | Student_ID in an Enrollment table. |
| 11. Table Relationship | Does not itself establish a relationship between tables. | Does not itself establish a relationship between tables. | Can be referenced by another table. | Directly represents a reference to another table. |
| 12. Database Integrity | Supports the concept of unique identification. | Supports uniqueness and entity identification. | Supports entity integrity. | Supports referential integrity. |
| 13. SQL Constraint | It is primarily a relational concept rather than a specific SQL constraint named SUPER KEY. | It is primarily a database design concept rather than a standard SQL constraint named CANDIDATE KEY. | Implemented using PRIMARY KEY. | Implemented using FOREIGN KEY and REFERENCES. |
| 14. Parent Table | May exist in a table without relationships. | May exist in a parent table. | Often identifies records in a parent table. | Normally resides in the child table. |
| 15. Child Table | Not specifically associated with child tables. | Not specifically associated with child tables. | Can be present in either parent or independent tables. | Normally exists in the child or referencing table. |
| 16. Composite Key | Can consist of multiple attributes. | Can be a combination of multiple attributes. | Can be a composite primary key. | Can be a composite foreign key. |
| 17. Relationship to Other Keys | Superset concept for unique identifiers. | Minimal subset of super keys. | Selected candidate key. | References a key in another table. |
| 18. SQL JOIN Usage | Can theoretically identify rows used in joins. | Can be used as a unique identifying attribute set. | Frequently used in joins. | Frequently used to connect tables in joins. |
| 19. Real-World Example | Employee_ID + Employee_Name. | Employee_ID or unique Email. | Employee_ID. | Employee_ID in Payroll. |
| 20. Simple Meaning | Can uniquely identify. | Minimal unique identifier. | Chosen unique identifier. | Connects tables. |
Super Key vs Candidate Key
The most important difference between a super key and candidate key is minimality.
Super Key
A super key can contain extra attributes that are not necessary for uniquely identifying a record.
Example:
Student_ID + Student_Name
Candidate Key
A candidate key contains only the attributes necessary to uniquely identify a record.
Example:
Student_ID
Therefore, every candidate key is a super key, but every super key is not necessarily a candidate key.
Candidate Key vs Primary Key
A database table can have multiple candidate keys, but only one of them is selected as the primary key.
For example, suppose a Student table contains:
| Student_ID | Student_Name | |
|---|---|---|
| 101 | rahul@example.com | Rahul |
| 102 | priya@example.com | Priya |
If both Student_ID and Email are guaranteed to be unique:
- Student_ID → Candidate Key
- Email → Candidate Key
- Student_ID + Email → Super Key but not a candidate key because it is not minimal.
If the designer selects Student_ID as the main identifier:
Student_ID → Primary Key
The other candidate key may be treated as an alternate key.
Primary Key vs Foreign Key
A primary key and foreign key have different roles even though they work together in relational database design.
Primary Key
- Uniquely identifies records.
- Belongs to its own table.
- Does not allow NULL.
- Does not allow duplicate values.
- Can be referenced by foreign keys.
Foreign Key
- Connects related tables.
- Normally exists in a child table.
- May allow NULL.
- Can contain duplicate values.
- References a primary or suitable unique key.
Example of All Four Keys Using a Student Table
Consider this Student table:
| Student_ID | Student_Name | Course | |
|---|---|---|---|
| 101 | rahul@example.com | Rahul | BCA |
| 102 | priya@example.com | Priya | BCA |
| 103 | amit@example.com | Amit | BCA |
Assume both Student_ID and Email are unique.
- Super Key: Student_ID
- Super Key: Email
- Super Key: Student_ID + Student_Name
- Candidate Key: Student_ID
- Candidate Key: Email
- Primary Key: Student_ID, if selected by the designer
Now suppose an Enrollment table contains Student_ID. In that table:
Student_ID → Foreign Key
This foreign key references Student(Student_ID).
SQL Example: Primary Key and Foreign Key
The following SQL example demonstrates how a primary key and foreign key can be implemented:
CREATE TABLE Student (
Student_ID INT PRIMARY KEY,
Student_Name VARCHAR(100),
Email VARCHAR(150) UNIQUE,
Course VARCHAR(50)
);
CREATE TABLE Enrollment (
Enrollment_ID INT PRIMARY KEY,
Student_ID INT,
Subject VARCHAR(100),
FOREIGN KEY (Student_ID)
REFERENCES Student(Student_ID)
);In this SQL example:
- Student_ID is the primary key of Student.
- Email is protected by a UNIQUE constraint.
- Enrollment_ID is the primary key of Enrollment.
- Student_ID in Enrollment is a foreign key.
- The foreign key references Student(Student_ID).
Can Candidate Key Be Created Directly in SQL?
SQL does not generally provide a universal CANDIDATE KEY constraint with that exact name. Instead, candidate-key properties are normally implemented using constraints such as PRIMARY KEY and UNIQUE, along with appropriate NOT NULL requirements.
For example:
CREATE TABLE Student (
Student_ID INT PRIMARY KEY,
Email VARCHAR(150) UNIQUE NOT NULL,
Student_Name VARCHAR(100)
);Here, Student_ID can be selected as the primary key, while Email can also uniquely identify a student and therefore can represent another candidate key when its business rules guarantee uniqueness.
Composite Keys and Database Keys
A composite key contains two or more columns that together identify a record uniquely.
For example, consider an Enrollment table:
| Student_ID | Subject_ID | Semester |
|---|---|---|
| 101 | CS101 | 5 |
| 101 | CS102 | 5 |
| 102 | CS101 | 5 |
If the combination of Student_ID and Subject_ID uniquely identifies each enrollment, then:
(Student_ID, Subject_ID) → Composite Candidate Key
If selected as the main identifier, the same combination can become a Composite Primary Key.
Primary Key, Candidate Key and Alternate Key
When a table has multiple candidate keys, one is selected as the primary key. The remaining candidate keys are commonly called alternate keys.
| Key Type | Example | Explanation |
|---|---|---|
| Candidate Key 1 | Student_ID | Uniquely identifies a student. |
| Candidate Key 2 | Also uniquely identifies a student. | |
| Primary Key | Student_ID | Selected candidate key used as the main identifier. |
| Alternate Key | Candidate key not selected as the primary key. |
Real-World Applications of Database Keys
1. College Management System
Student_ID can be the primary key in the Student table. Course registration tables can use Student_ID as a foreign key.
2. E-Commerce Website
Product_ID can uniquely identify products. Order_Details can use Product_ID as a foreign key to connect products with orders.
3. Banking System
Account_ID can identify accounts, while transactions can reference Account_ID using a foreign key.
4. Hospital Management System
Patient_ID can identify patients. Appointment records can use Patient_ID as a foreign key.
5. Employee Management System
Employee_ID can be a primary key in an Employee table, while Payroll and Attendance tables can reference Employee_ID.
Advantages of Using Database Keys
- Provide unique identification of records.
- Reduce ambiguity in database records.
- Help maintain data integrity.
- Support relationships between tables.
- Improve relational database organization.
- Help SQL JOIN operations.
- Support normalization and structured database design.
- Reduce unnecessary duplication of related information.
Common Mistakes About Database Keys
Mistake 1: Thinking Candidate Key and Primary Key Are Always the Same
A table can have multiple candidate keys, but only one candidate key is normally selected as the primary key.
Mistake 2: Thinking Every Super Key Is a Candidate Key
This is incorrect. A super key may contain extra attributes. A candidate key must be minimal.
Mistake 3: Thinking Foreign Keys Must Be Unique
Foreign keys normally do not have to be unique. Multiple child records can reference the same parent record.
Mistake 4: Thinking Foreign Key Always Means Primary Key
A foreign key references a primary key or another suitable unique key according to the database system's rules.
Mistake 5: Confusing Unique Key and Primary Key
A UNIQUE constraint enforces uniqueness, while a primary key is the designated main identifier for records in a table. NULL behavior for UNIQUE constraints can vary between database systems.
Easy Trick to Remember the Four Keys
SUPER KEY → CAN IDENTIFY
CANDIDATE KEY → MINIMAL IDENTIFIER
PRIMARY KEY → CHOSEN IDENTIFIER
FOREIGN KEY → CONNECTS TABLES
Frequently Asked Questions
What is a super key in DBMS?
A super key is a column or combination of columns that can uniquely identify each record in a table.
What is a candidate key in DBMS?
A candidate key is a minimal super key that uniquely identifies each record without containing unnecessary attributes.
What is a primary key in DBMS?
A primary key is the candidate key selected to uniquely identify records in a table.
What is a foreign key in DBMS?
A foreign key is a column or combination of columns that references a key in another table and establishes a relationship between the tables.
What is the difference between super key and candidate key?
A super key can contain unnecessary attributes, whereas a candidate key is a minimal super key.
What is the difference between candidate key and primary key?
A table can have multiple candidate keys, but one candidate key is selected as the primary key.
Can a table have multiple candidate keys?
Yes. A table can have multiple candidate keys if different attributes or combinations of attributes can uniquely identify its records.
Can a table have multiple primary keys?
A table has one primary key constraint. However, that primary key can contain multiple columns and is then called a composite primary key.
Can a foreign key have duplicate values?
Yes. Duplicate foreign key values are normally allowed because multiple child records can refer to the same parent record.
Can a primary key also be a foreign key?
Yes. In certain database designs, a column can simultaneously form the primary key of a table and reference a key in another table as a foreign key.
What is the relationship between candidate key and super key?
Every candidate key is a super key, but a super key is not necessarily a candidate key because it may contain unnecessary attributes.
What is the easiest way to remember database keys?
Remember: Super Key can identify, Candidate Key is minimal, Primary Key is selected, and Foreign Key connects tables.
Conclusion
The difference between Super Key, Candidate Key, Primary Key and Foreign Key is fundamental to understanding relational database design.
A Super Key is any set of attributes that can uniquely identify a record. A Candidate Key is a minimal super key. A Primary Key is the candidate key selected as the main identifier of records. A Foreign Key connects records between related tables.
The easiest way to remember the concept is:
Super Key → Can Identify
Candidate Key → Minimal Identifier
Primary Key → Chosen Identifier
Foreign Key → Connects Tables
Understanding these database keys makes it easier to learn SQL, relational database design, normalization, database relationships, SQL JOINs, entity integrity and referential integrity.
Next Recommended Article: Candidate Key vs Alternate Key vs Unique Key vs Primary Key — Complete Difference in DBMS.
No comments:
Post a Comment