Database Design Fundamentals: Primary Keys, Foreign Keys, and Constraints Explained

Introduction

Welcome back to the Database Design Fundamentals series!

In the previous article, we explored entities, tables, and relationships — the building blocks of any database. Now, we’ll take the next step: understanding primary keys, foreign keys, and constraints.

These elements are the rules of the game. They ensure your database remains accurate, consistent, and reliable. By the end of this post, you’ll know what each of these terms means and how they work together.

What Is a Primary Key?

A primary key is a column (or set of columns) that uniquely identifies each row in a table.

• Example: In a Students table:

  • StudentID could be the primary key.

• Rules:

  • Must be unique (no duplicates).

  • Cannot be NULL (every row must have a value).

👉 Think of it like a passport number — no two people can share the same one.

What Is a Foreign Key?

A foreign key is a column in one table that points to a primary key in another table.

• Example:

  • In an Enrollments table:

    • StudentID → references Students(StudentID)

    • CourseID → references Courses(CourseID)

This creates a relationship between tables.

👉 Think of it like a reference in a library — it tells you where to find the related record.

What Are Constraints?

Constraints are rules enforced by the database to keep data valid.

Common Types of Constraints

1. PRIMARY KEY Constraint

   • Ensures uniqueness and non-null values.

2. FOREIGN KEY Constraint

   • Ensures values match an existing row in the referenced table.

3. UNIQUE Constraint

   • No duplicate values in a column.

   • Example: Email in a Users table.

4. NOT NULL Constraint

   • Column cannot be empty.

   • Example: Every student must have a Name.

5. CHECK Constraint

   • Adds a condition for valid data.

   • Example: Age >= 0 for a Students table.

Why Do They Matter?

Without keys and constraints, a database would quickly fall apart:

• Duplicate rows → two students with the same ID.

• Broken relationships → an enrollment pointing to a non-existent course.

• Invalid data → negative ages or empty emails.

Keys and constraints are the guardrails that keep your data clean and trustworthy.

Real-World Analogy

Imagine a university registration system:

• Primary Key: Each student has a unique student ID.

• Foreign Key: Each course enrollment points to a valid student and course.

• Constraints:

  • You can’t enroll in a course that doesn’t exist.

  • A student’s date of birth can’t be in the future.

Without these rules, the system would be chaotic — duplicate students, fake enrollments, and nonsense data everywhere.

Conclusion & Next Steps

Primary keys, foreign keys, and constraints form the rules of structure and consistency in databases. They ensure accuracy and prevent invalid or duplicate data.

In the next post, we’ll explore Normalization — specifically 1NF, 2NF, and 3NF — and how it helps organize your data for efficiency and reliability.