Beginner level · Module 3: Keys, Relationships and Joins · Lesson 1 of 3
Primary and Foreign Keys
Identify each row uniquely and link tables safely with foreign keys.
In this lesson you will
- Choose primary keys
- Define foreign keys and explain referential integrity
Key concepts
primary keyforeign keyone-to-manyreferential integrity
The lesson
A primary key uniquely identifies each row. An automatically numbered id column is the usual choice, because real-world values such as names change and repeat.
A foreign key stores another table's primary key: payments.student_id REFERENCES students(id). It creates a one-to-many relationship — one student, many payments.
Referential integrity means every foreign key points to a row that exists. The database can enforce it, rejecting a payment for student 999 if there is no such student. In SQLite, switch this checking on with PRAGMA foreign_keys = ON.
Step by step
- Create students and payments tables with keys.
- Insert students and payments.
- Try to insert a payment for a missing student and observe the error.
- Draw the relationship.
Practice activity
Create the school fees tables with primary and foreign keys and test the integrity rules.
Expected result: Valid payments are accepted and payments for non-existent students are rejected.
Check your understanding
-
Why is an automatic id a good primary key?
Show answer
It never changes or repeats, unlike names.
-
What does a foreign key enforce?
Show answer
That the referenced row exists (referential integrity).
Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.