| Aspect | Primary Key | Foreign Key |
|---|
| Definition | Uniquely identifies each record in a table. | Creates a relationship between two tables. |
| Uniqueness | Must be unique for each record. | Can have duplicate values. |
| NULL Values | NULLs are NOT allowed. | NULLs are allowed (if no relation exists). |
| Number per Table | Only one primary key per table. | A table can have multiple foreign keys. |
| Purpose | Ensures that each record is unique and identifiable. | Ensures referential integrity between tables. |
| Index | Automatically creates a unique index. | Index is not automatically created (optional). |
| Relation | Does not refer to any other table. | References a primary key in another table. |
| Example | student_id in Students table. | dept_id in Employees table referring to Departments table. |
Example to Understand
1. Primary Key Example
CREATE TABLE Students (
student_id INT PRIMARY KEY,
name VARCHAR(100),
age INT
);
- Here,
student_id is the Primary Key. - It uniquely identifies each student.
2. Foreign Key Example
CREATE TABLE Departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);
- In
Employees table: emp_id is the Primary Key.dept_id is the Foreign Key that references the dept_id in the Departments table.- It links each employee to a valid department.
Key Points to Remember
- Primary Key ensures row uniqueness inside a table.
- Foreign Key ensures data consistency across two tables.
- Primary Key values must exist; Foreign Key values may or may not exist (if NULL is allowed).
- A Primary Key is mandatory for record identity; a Foreign Key is used when linking tables.
Quick Real-Life Analogy
| Real-World Example | Database Concept |
|---|
| Aadhaar Number (unique for every citizen) | Primary Key |
| Passport having Aadhaar number linked (referring Aadhaar database) | Foreign Key |
Related
