Primary Keys and Foreign Keys Explained

What is a Primary Key?

A Primary Key is a special field that uniquely identifies every single record in a table. Think of it as each record's personal ID badge — no two records in the same table can have the same Primary Key value!

The Golden Rules of Primary Keys

• Rule 1: No Nulls Allowed — Every record MUST have a Primary Key value. You cannot leave it empty!
• Rule 2: Must Be Unique — No duplicates allowed. Each value can appear only once in the table.
• Rule 3: One Per Table — A table can have only ONE Primary Key (though it can be made of multiple fields).

CSEC Example: Why Names Don't Work

Imagine using "Student_Name" as an identifier. What happens when you have two students named "Alex Smith"? The database can't tell them apart! That's why we use Student_ID instead — each student gets their own unique number that nobody else has.

What is a Foreign Key?

A Foreign Key is a field in ONE table that refers to (points to) the Primary Key of ANOTHER table. It creates a "bridge" or link between different tables without duplicating data!

The Purpose of Foreign Keys

Imagine a school with hundreds of students, each taking multiple courses. Instead of copying all student information into every course record, we simply use the Student_ID as a Foreign Key. This:

• Saves storage space
• Keeps data consistent (no conflicting addresses)
• Creates relationships between different subjects

Visualizing the Link

The Parent table contains the master information, while the Child table references it:

• Parent Table: Department_Table (PK: Dept_ID)
• Child Table: Teacher_Table (FK: Dept_ID)

One-to-One (1:1)

One record in Table A relates to exactly ONE record in Table B. Example: One person has one passport. Each passport belongs to one person.

One-to-Many (1:M) — The Most Common!

One record in Table A relates to MANY records in Table B. Example: One teacher has many students. But each student has only one homeroom teacher.

Many-to-Many (M:N)

Many records in Table A relate to MANY records in Table B. Example: Many students take many different subjects. This relationship usually requires a "junction table" in between.

What is Referential Integrity?

Referential Integrity is a set of rules that ensures the "bridge" between tables remains valid and consistent. It prevents "orphan" records — data in one table that points to something that doesn't exist in another table.

The "Orphan" Problem

Imagine deleting a student from the Students table, but their enrollment records still exist in a Grades table. Those Grade records are now "orphans" — they point to a Student_ID that doesn't exist! Referential integrity prevents this by:

• Preventing deletion of records that are referenced by other tables
• Ensuring Foreign Key values always point to valid Primary Keys
• Maintaining consistency across related tables

What is a Compound Key?

A Compound Key (also called a Composite Key) is when TWO or MORE fields are combined together to create a unique Primary Key. Neither field alone is unique, but together they create a unique combination!

CSEC Example: Class Attendance

Imagine tracking student attendance. If we use just Student_ID, the same student would appear multiple times (one for each day). If we use just Date, many students would have the same date. But Student_ID + Date together creates a unique record for each student's attendance on each day!

How to Draw an ER Diagram

For your SBA, you'll often need to show your database design visually. Follow these steps:

1. Draw each table as a rectangle
2. List all fields inside, marking the Primary Key (PK) and Foreign Keys (FK)
3. Draw lines between tables to show relationships
4. Use "crow's foot" notation (1, M, or N) to show relationship types

Pre-Submission Checklist

Before submitting your SBA, check that your relationships are correct:

Test your understanding of Primary Keys and Foreign Keys with these scenario-based questions!