In this episode of the Database Series, we’ll explore two essential concepts in SQL: Primary Keys and Foreign Keys.
If you’ve ever wondered how data from different tables is linked together—this is how it’s done.
What Is a Primary Key?
A primary key is a column (or combination of columns) that uniquely identifies each row in a table.
Key Points:
- Every table should have one
- Values must be unique and not null
- Ensures data integrity by preventing duplicate records
Example:
Table: students
| student_id (PK) | name | course |
| 1 | Ali | Math |
| 2 | Ayesha | Physics |
| 3 | Zara | English |
Here, student_id is the primary key—it uniquely identifies each student.
What Is a Foreign Key?
A foreign key is a column in one table that refers to the primary key in another table. It’s used to connect data between tables.
Key Points:
- Creates a relationship between two tables
- Enforces referential integrity (prevents orphan data)
- Can have duplicate values (unlike primary keys)
Example:
Table: enrollments
| enrollment_id | student_id (FK) | subject |
| 101 | 1 | Physics |
| 102 | 2 | Chemistry |
| 103 | 1 | Biology |
Here, student_id is a foreign key that connects to the students table’s primary key.
One-to-Many Relationship
A one-to-many relationship means:
One record in the primary table can be linked to many records in another table.
Example:
- One student → can enroll in many subjects
- students.student_id → links to many enrollments.student_id
Visual Diagram
+—————-+ +——————–+
| students | | enrollments |
+—————-+ +——————–+
| student_id (PK)|<——–>| student_id (FK) |
| name | | subject |
+—————-+ +——————–+
This diagram shows how student_id connects both tables.
Summary: Primary vs Foreign Key
| Feature | Primary Key | Foreign Key |
| Purpose | Uniquely identifies a row | Connects to another table |
| Uniqueness | Must be unique | Can be duplicated |
| Null allowed? | No | Yes (in some cases) |
| Relationship | Defines the row | References primary key |
| Example Table | students | enrollments |
