01.2 Primary keys, foreign keys and relationships

01 | WHY A NAME IS NOT ENOUGH

Our Students table contains two students called Maya. If we record only “Maya joined Robotics”, we cannot tell which student is meant. Names can repeat and may change.

A primary key identifies each record uniquely. It can be a single field or a combination. In the intended relational design it must be unique and present for every record.

02 | USE A STABLE IDENTIFIER

student_id identifies the student, while first_name describes them. Student 101 remains the same student if their displayed name is corrected.

An identifier introduced for this purpose is often called a surrogate key. An existing meaningful value used as a key is a natural key. Choose carefully: meaningful values can change or turn out not to be unique.

A key does not need to be consecutive or reveal a person’s position in a list. Deleting student 102 does not mean student 103 should be renumbered.

03 | A FOREIGN KEY POINTS TO ANOTHER RECORD

A foreign key stores a reference to a key in another table, or sometimes the same table. Here a membership’s student_id refers to Students.student_id .

Foreign keys need not be unique. Student 101 can appear in several membership records because the same student can join more than one club. Matching column names alone do not create or enforce a relationship.

Use compatible representations for linked values. In our schema both sides of each link use integer identifiers.

04 | SEE THE THREE TABLES TOGETHER

Read the first membership: student 101 belongs to club 1 as a member. Look up 101 in Students to find Maya; look up 1 in Clubs to find Robotics.

The second row links that same student to Game Design as a leader. The role belongs to this particular membership, not to Maya in every context.

05 | ONE-TO-MANY RELATIONSHIPS

One student can have many memberships. Each membership refers to exactly one student in our design. This is a one-to-many relationship from Students to Memberships.

The foreign key goes on the many side: each membership stores its student reference. Clubs has a similar one-to-many relationship to Memberships.

06 | MANY-TO-MANY NEEDS A JUNCTION TABLE

At the business level, students and clubs have a many-to-many relationship. A student can join several clubs, and a club can have several students.

The junction table Memberships represents each student–club link as its own record. This turns the many-to-many design into two one-to-many links.

We do not store an expanding list of club IDs inside Students. Separate membership rows let us retrieve and change individual links clearly.

07 | A COMPOSITE KEY IDENTIFIES THE PAIR

Our rule allows one current membership per student per club. Neither student_id nor club_id is unique on its own in Memberships, but the pair must be unique.

A composite primary key uses the combination: (student_id, club_id) . It allows (101, 1) and (101, 2), but rejects a second (101, 1).

If we later store separate annual memberships, the rule changes. We may need a year as part of the unique combination or a different design. Keys follow the business rules.

08 | REFERENTIAL INTEGRITY

Referential integrity keeps enforced references valid. With the relevant foreign key active, a membership cannot point to a student ID that is absent from Students.

A foreign key can be optional in some designs, allowing NULL. Our membership references are required, so we combine their foreign keys with NOT NULL .

SQLite needs foreign-key enforcement enabled for each connection. Page 01.3 shows how to do this before running the schema; declaring REFERENCES alone is not enough if enforcement is off.

09 | WHAT ABOUT DELETING A STUDENT?

Removing a referenced student could leave memberships pointing nowhere. A database design must decide what should happen.

Our starter schema uses the default NO ACTION behaviour and ordinary immediate foreign keys: deleting a student with memberships fails while those references remain. A different design might explicitly cascade related deletions, but that must be a deliberate choice.

Do not remove constraints merely to silence the error. First decide what the application should do with the related data.

10 | ONE-TO-ONE AS A CONTRAST

In a one-to-one relationship, a record has at most one corresponding record on the other side under the stated rules. For example, an optional student profile could allow only one profile per student.

A foreign key alone does not impose that limit. The referencing student identifier would also need a uniqueness rule, or could itself serve as the profile’s primary key.

Our clubs project needs many-to-many membership, so a one-to-one design would solve the wrong problem.

11 | DRAW AND DEFEND YOUR DESIGN

Draw three boxes for Students, Clubs and Memberships. Label primary keys and foreign keys. Write “one student, many memberships” and “one club, many memberships” beside the appropriate links.

KEEP LEARNING

Previous guide 01.3 Creating tables and constraints

Reference: SQLite table constraints · SQLite data types · SQLite foreign keys · Python SQLite interface