csmasters.dev

Primary Keys, Foreign Keys & Data Integrity

A primary key uniquely identifies each record in a table. Its values must not be duplicated or missing. A person's name is usually unsuitable because different people can share a name. A unique MemberID is more dependable.

Worked example

Two records have Name="Alex Lee" but MemberID values 104 and 105. The keys are unique even though the names match. A key constraint prevents duplicate key values; it does not prove the two records describe different real people.

Validation checks whether input meets a rule, not whether it is true. A presence check can require a name, a range check can limit age, and a format check can check the layout of an identifier. A validly formatted wrong date may still pass.

Independent design

Choose a primary key for a school library's books when several copies share a title. Explain why a separate copy identifier is needed. Choose a validation rule for a publication year and explain one incorrect value that could still pass it.

This course's core database tasks use a single table. Multiple-table relationships and advanced key taxonomy are optional later study.

AI Socratic Tutor

All-Access
Sign in or create an account. AI Tutor is an All-Access benefit.
Sign Up

Need guidance? Ask a question or use the hint below.

AI Socratic Tutor is an All-Access Benefit

Unlock step-by-step Socratic hints, algorithm trace table walkthroughs, and error diagnostics for every Cambridge exercise.

View All-Access Plans

You're chatting with an AI tutor, not a person. AI can make mistakes, so double-check its answers.

Exercise:
Task 1 Task 2 Task 3

Exam-Style Question: Select Primary Key and Validation Rule

+35 XP

A medical clinic database stores a Patients table with the following fields:

  • NationalID (a unique 10-digit citizen identifier)
  • Surname
  • DateOfBirth
  • BloodGroup (must be one of 'A', 'B', 'AB', 'O')
  1. The field that should be chosen as the primary key is: [Space 1]
  2. To ensure that BloodGroup only contains one of the four accepted blood types, the database should apply a [Space 2] check.
  3. To ensure that NationalID contains exactly 10 characters, the database should apply a [Space 3] check.
Word Bank / Available Cards (Drag into spaces or tap to place)

All-Access Required

Interactive exercises for Module 2 and beyond require an active plan.

Unlock All-Access ($30/mo) Already subscribed? Sign in