Primary and Foreign Keys

Students learn primary and foreign keys best when they build and test real database tables, not just listen to definitions. Active schema creation lets them experience why uniqueness matters and how relationships work, turning abstract rules into memorable insights.

• Secondary 4
• Computing
• 4 activities
• 20 min–45 min

Learning Objectives

1. Design a simple database schema for a school's student information system, correctly identifying and implementing primary and foreign keys.
2. Analyze a given database schema and identify potential data integrity issues arising from incorrect primary or foreign key usage.
3. Explain the role of a primary key in ensuring unique record identification and the function of a foreign key in establishing referential integrity.
4. Compare and contrast the characteristics and purposes of primary keys versus foreign keys in relational database design.

Case Study Analysis

⏱ 30 min·Pairs

Pair Design: School Database Schema

Pairs sketch tables for students, classes, and enrollments on paper. Identify primary keys for each table, then add foreign keys to link them. Discuss choices and refine based on peer feedback.

Prepare & details

Explain the importance of primary keys in uniquely identifying records.

Facilitation Tip: During Pair Design, circulate to ask each pair how they decided which field should be the primary key and how they plan to link tables.

Setup: Groups at tables with case materials

Materials: Case study packet (3-5 pages), Analysis framework worksheet, Presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

Case Study Analysis

⏱ 45 min·Small Groups

Small Groups: SQLite Table Creation

Groups create three tables in SQLite with primary and foreign keys. Insert sample data, test inserts that violate integrity, and query joined results. Share one successful join query with the class.

Prepare & details

Analyze how foreign keys establish relationships and maintain data integrity across tables.

Facilitation Tip: When groups create SQLite tables, insist they test their schema with invalid inserts to experience error messages firsthand.

Setup: Groups at tables with case materials

Materials: Case study packet (3-5 pages), Analysis framework worksheet, Presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

Case Study Analysis

⏱ 35 min·Whole Class

Whole Class: Error Hunt Challenge

Project flawed schemas on the board with missing or incorrect keys. Class votes on fixes, then tests in a shared database file. Tally correct identifications to review rules.

Prepare & details

Design a database schema that correctly uses primary and foreign keys to link related information.

Facilitation Tip: For the Error Hunt Challenge, provide intentionally flawed schemas so students practice identifying missing keys or broken relationships.

Setup: Groups at tables with case materials

Materials: Case study packet (3-5 pages), Analysis framework worksheet, Presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

Case Study Analysis

⏱ 20 min·Individual

Individual: Key Constraint Quiz

Students receive table descriptions and add primary/foreign keys in a worksheet. Validate by simulating data entry scenarios. Peer review follows to confirm designs.

Prepare & details

Explain the importance of primary keys in uniquely identifying records.

Facilitation Tip: After Key Constraint Quiz, review responses as a class to clarify any lingering misconceptions before moving on.

Setup: Groups at tables with case materials

Materials: Case study packet (3-5 pages), Analysis framework worksheet, Presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

Teaching This Topic

Teach primary and foreign keys through iterative design rather than lecture. Start with simple examples like student grades, then gradually add complexity to reveal edge cases. Avoid front-loading too much theory, as students grasp these concepts through trial, error, and correction. Research shows hands-on schema building strengthens retention compared to passive note-taking.

What to Expect

By the end of these activities, students will confidently design tables with correct primary and foreign keys, explain referential integrity, and debug common schema errors without prompting. They will also articulate why keys are essential for data consistency.

Watch Out for These Misconceptions

Common Misconception

During Pair Design, watch for students who plan to use a name or email as a primary key without considering duplicates.

What to Teach Instead

Ask pairs to insert two records with the same name or email and observe the error. Then guide them to revise their schema to use an auto-incrementing ID instead.

Common Misconception

During Small Groups: SQLite Table Creation, watch for students who assume foreign keys must be unique within their table.

What to Teach Instead

Have groups insert multiple records with the same foreign key value and watch how the database handles it. Ask them to explain why uniqueness isn’t required for foreign keys in this context.

Common Misconception

During Whole Class: Error Hunt Challenge, watch for students who treat any unique field as a suitable primary key without considering stability.

What to Teach Instead

Present a schema where a field like 'student_name' is used as a primary key, then demonstrate how changing a name would break the relationship. Guide students to identify stable alternatives like IDs.

Assessment Ideas

Quick Check

After Pair Design, present two tables ('Students' and 'Enrollments') and ask students to identify the primary keys and the foreign key relationship, explaining their reasoning in 2-3 sentences.

Exit Ticket

During Small Groups: SQLite Table Creation, collect each student’s table schema and have them write one reason why primary keys prevent data corruption and one scenario where a foreign key would maintain integrity in their design.

Discussion Prompt

After Error Hunt Challenge, facilitate a class discussion using the prompt: 'What errors did you find in the provided schemas? How would fixing the keys improve the database’s reliability?'

Extensions & Scaffolding

• Challenge students to design a library database with at least four tables and multiple foreign key relationships, then test it with edge-case queries.
• For students struggling, provide partially completed schemas with missing keys and have them fill in the blanks and explain their choices.
• Deeper exploration: Introduce composite keys in a scenario where single fields cannot guarantee uniqueness, such as tracking class enrollments by student ID and course section.

Key Vocabulary

Primary Key	A column or set of columns that uniquely identifies each row in a database table. It ensures no two rows have the same identifier.
Foreign Key	A column or set of columns in one table that refers to the primary key in another table. It links the two tables and enforces relationships.
Referential Integrity	A database concept that ensures relationships between tables remain consistent. It prevents actions that would destroy links, such as deleting a record that is referenced by a foreign key.
Data Integrity	The overall accuracy, completeness, and consistency of data. Primary and foreign keys are crucial for maintaining data integrity in relational databases.