Keys and types of keys

Definition

A key is an attribute or a group of attributes in a database table that is used to uniquely identify a record or to establish a relationship between two tables.

In simple words, a key acts like an identifier or reference label for data. It may consist of one column or multiple columns together. Depending on its purpose, a key can uniquely identify rows, enforce uniqueness, or link one table to another.

Example:
In a table of employees:

Here, EmpID can be used as a key because every employee has a different ID.

Main Content

1. Primary Key

Primary key

• is the main key used to uniquely identify each record in a table.
• It must be unique and cannot be NULL. This means no two rows can have the same primary key value, and every row must have a valid value.
• A table can have only one primary key, although that primary key may consist of multiple columns together. This is called a composite primary key.

Example:
In a Student table:

Here, RollNo is a primary key because each student has a unique roll number.

Why it is important:

• It prevents duplicate records.
• It provides a reliable way to identify each row.
• It is often used by other tables as a reference.

2. Candidate Key

• A candidate key is any attribute or set of attributes that can uniquely identify a record in a table.
• A table may have more than one candidate key.
• From all candidate keys, one is chosen as the primary key, and the others remain candidate keys.

Example:
In a Person table:

Here, AadhaarNo, PassportNo, and Email may all uniquely identify a person. Therefore, they are candidate keys.

Characteristics:

• Unique values
• Minimal attributes
• No unnecessary extra columns

Relationship with primary key:

• Every primary key is a candidate key.
• Not every candidate key becomes the primary key.

3. Foreign Key

• A foreign key is an attribute in one table that refers to the primary key of another table.
• It is used to establish a relationship between tables.
• Unlike a primary key, a foreign key may contain duplicate values and may also be NULL, depending on the design rules.

Example:
Department table:

Employee table:

Here, DeptID in the Employee table is a foreign key that refers to DeptID in the Department table.

Purpose:

• Links related tables
• Maintains referential integrity
• Prevents invalid references

Simple diagram for relationship:

Department Table
+--------+----------+
| DeptID | DeptName |
+--------+----------+
| 10     | HR       |
| 20     | IT       |
+--------+----------+

Employee Table
+-------+------+--------+
| EmpID | Name | DeptID |
+-------+------+--------+
| 1     | Raj  | 10     |
| 2     | Sim  | 20     |
| 3     | Ali  | 10     |
+-------+------+--------+

Working / Process

1. Identify the table and its purpose

First, determine what the table stores. For example, a Student table stores student details, while a Course table stores course information. This helps decide what should uniquely identify a record.

2. Select the appropriate key type

Choose the key based on need:

• Use a primary key to uniquely identify rows.
• Use a candidate key to find all possible unique identifiers.
• Use a foreign key to connect tables.

3. Apply constraints and enforce relationships

Set rules such as uniqueness, not-null, and referential integrity. For example, if EmpID is a primary key, the database will not allow duplicate or null values. If DeptID is a foreign key, the system will ensure it matches an existing department record.

Advantages / Applications

Ensures uniqueness of records

• and prevents duplicate data in tables.

Maintains data integrity

• by making sure the database stores valid and consistent information.

Helps build relationships between tables

• , which is essential for normalized relational databases.

Summary

• Keys are used to identify records and connect tables.
• Primary key uniquely identifies each row.
• Foreign key links one table to another.
• Important terms to remember: primary key, candidate key, foreign key, uniqueness, referential integrity