Keys: Primary, Candidate, Alternate, Foreign

Keys in relational databases are abstract concepts that students often confuse without hands-on practice. Active learning helps students visualize why keys exist by letting them design schemas, identify constraints, and troubleshoot errors in real time. When students manipulate data directly, they build lasting understanding of how keys maintain integrity across tables.

• Class 12
• Computer Science
• 4 activities
• 20 min–40 min

Learning Objectives

1. Compare and contrast the properties and roles of primary, candidate, alternate, and foreign keys in a relational database schema.
2. Explain the mechanism by which a primary key ensures entity integrity and uniqueness of records.
3. Analyze how foreign keys enforce referential integrity and establish relationships between related tables.
4. Design a simple database schema for a school or library system, correctly identifying and implementing primary and foreign keys.

Decision Matrix

⏱ 30 min·Pairs

Pair Schema Design: School Database

Students in pairs sketch tables for students, classes, and marks. They select primary keys, list candidate and alternate keys, then add foreign keys for relationships. Pairs present one design to class for feedback.

Prepare & details

Differentiate between primary, candidate, alternate, and foreign keys.

Facilitation Tip: During Pair Schema Design, provide each pair with a partially completed schema so they must debate which columns qualify as primary or candidate keys before finalizing their design.

Setup: Works in standard classroom rows with individual worksheets; group comparison phase benefits from rearranging desks into clusters of 4–6. Wall space or the blackboard can display inter-group criteria comparisons during debrief.

Materials: Printed A4 matrix worksheets (individual scoring + group summary), Chit slips for anonymous criteria generation, Group role cards (Criteria Chair, Scorer, Evidence Finder, Presenter, Time-keeper), Blackboard or whiteboard for shared criteria display

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

Decision Matrix

⏱ 40 min·Small Groups

Group Puzzle: Key Hunt Challenge

Provide printed tables with sample data. Small groups identify primary, candidate, alternate, and foreign keys, then correct violations like duplicates. Groups share fixes on board.

Prepare & details

Explain the role of a primary key in uniquely identifying records.

Facilitation Tip: In Group Puzzle: Key Hunt Challenge, give each group a mix of valid and invalid key examples so they must justify their choices by testing each candidate against uniqueness and relationship rules.

Setup: Works in standard classroom rows with individual worksheets; group comparison phase benefits from rearranging desks into clusters of 4–6. Wall space or the blackboard can display inter-group criteria comparisons during debrief.

Materials: Printed A4 matrix worksheets (individual scoring + group summary), Chit slips for anonymous criteria generation, Group role cards (Criteria Chair, Scorer, Evidence Finder, Presenter, Time-keeper), Blackboard or whiteboard for shared criteria display

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

Decision Matrix

⏱ 25 min·Whole Class

Class Simulation: Data Integrity Demo

Use a projector to show linked tables. Whole class suggests inserts or deletes, observing foreign key constraint errors. Discuss resolutions collaboratively.

Prepare & details

Analyze how foreign keys establish relationships between different tables.

Facilitation Tip: For Class Simulation: Data Integrity Demo, deliberately introduce duplicate or null values to show students how primary keys react, making the error messages meaningful.

Setup: Works in standard classroom rows with individual worksheets; group comparison phase benefits from rearranging desks into clusters of 4–6. Wall space or the blackboard can display inter-group criteria comparisons during debrief.

Materials: Printed A4 matrix worksheets (individual scoring + group summary), Chit slips for anonymous criteria generation, Group role cards (Criteria Chair, Scorer, Evidence Finder, Presenter, Time-keeper), Blackboard or whiteboard for shared criteria display

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

Decision Matrix

⏱ 20 min·Individual

Individual Mapping: ER Diagram Practice

Students draw ER diagrams for a library system, labelling all key types. Submit for peer review next class.

Prepare & details

Differentiate between primary, candidate, alternate, and foreign keys.

Facilitation Tip: During Individual Mapping: ER Diagram Practice, require students to annotate their diagrams with key symbols (PK, FK, AK) to reinforce visual literacy alongside technical terms.

Setup: Works in standard classroom rows with individual worksheets; group comparison phase benefits from rearranging desks into clusters of 4–6. Wall space or the blackboard can display inter-group criteria comparisons during debrief.

Materials: Printed A4 matrix worksheets (individual scoring + group summary), Chit slips for anonymous criteria generation, Group role cards (Criteria Chair, Scorer, Evidence Finder, Presenter, Time-keeper), Blackboard or whiteboard for shared criteria display

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

Teaching This Topic

Teach keys by starting with real-world analogies students can relate to, like student IDs in schools or Aadhaar numbers in India. Avoid overwhelming them with theory; instead, let them discover key properties through trial and error in controlled activities. Use frequent quick-checks to correct misconceptions immediately, as these concepts build on each other and confusion early on can persist.

What to Expect

By the end of these activities, students will confidently distinguish between primary, candidate, alternate, and foreign keys in practical schemas. They will also explain how keys enforce uniqueness, relationships, and data integrity through clear reasoning. Group discussions and peer reviews will show their ability to apply these concepts to new scenarios.

Watch Out for These Misconceptions

Common Misconception

During Pair Schema Design, watch for students who allow nulls or duplicates in primary key columns. Redirect them by asking, 'How will the database stop two students from sharing the same roll number if nulls are allowed?'

What to Teach Instead

Remind them to test inserting sample data during their design review to see errors firsthand, reinforcing that primary keys must be unique and non-null.

Common Misconception

During Group Puzzle: Key Hunt Challenge, watch for students who think foreign keys must be primary keys in their own table. Redirect them by asking, 'If the foreign key is a primary key elsewhere, does it still need to enforce a relationship? How?'

What to Teach Instead

Have them trace a manual join between two tables using foreign keys to see that the constraint works without the local column being a primary key.

Common Misconception

During Individual Mapping: ER Diagram Practice, watch for students who dismiss alternate keys as unnecessary. Redirect them by asking, 'What if your primary key is a long composite of three columns? How would you speed up queries?'

What to Teach Instead

Encourage them to consider indexing strategies and performance trade-offs, showing how alternate keys can optimize searches.

Assessment Ideas

Quick Check

After Pair Schema Design, ask each pair to swap their schema with another group and identify one primary key, one candidate key, and one foreign key in the swapped design, explaining their choices aloud.

Exit Ticket

During Class Simulation: Data Integrity Demo, give students a table with intentional errors and ask them to correct the schema by redesigning keys and explaining their fixes in 2-3 sentences.

Discussion Prompt

After Individual Mapping: ER Diagram Practice, pose this scenario: 'A student suggests making both roll number and Aadhaar optional in the students table. How would this affect primary and alternate keys?' Let students debate and vote on the best approach before revealing the correct design.

Extensions & Scaffolding

• Challenge students to design a library database with composite primary keys (e.g., BookID + MemberID) and justify why alternate keys like ISBN or phone number might be used.
• Scaffolding: Provide a table with mixed key options and ask students to highlight primary, candidate, and foreign keys before designing relationships.
• Deeper: Have students research how database systems like MySQL or Oracle handle key constraints internally and present their findings in a short report.

Key Vocabulary

Primary Key	A column or set of columns that uniquely identifies each row in a table. It cannot contain NULL values and must be unique.
Candidate Key	A column or set of columns that could potentially serve as a primary key. It must be unique and non-NULL.
Alternate Key	A candidate key that was not chosen as the primary key. It still maintains uniqueness within the table.
Foreign Key	A column or set of columns in one table that refers to the primary key in another table. It establishes a link and enforces referential integrity.