Keys and relationships

Part of the free LogicWiz SQL & Databases course, Module 1: What a database is. The queries and practice run in the app on real Postgres.

The college has two students called Aarav Sharma. The exam cell prints marks "for Aarav Sharma" and the wrong Aarav gets a back paper. How does a database tell them apart?

You'll learn to

• Pick a primary key and say why it must be unique and never empty
• Follow a foreign key from one table to another
• Spot one-to-many and many-to-many relationships, and composite keys
• Read the college schema like a map

Learn

The primary key: a roll number for every row

A primary key is a column (or a set of columns) that names exactly one row.

• It is unique: no two rows share it.
• It is never NULL: every row has one.

In students that is student_id. Names repeat (two Aarav Sharmas), cities repeat, even CGPAs repeat. Roll number 101 never does. That is why the college gives you a roll number at all.

The key finds exactly one row

SELECT name, city, cgpa
FROM students
WHERE student_id = 107;

A search on the primary key returns at most one row, every time. A search on name could return two.

The database guards the key

INSERT INTO students VALUES
  (101, 'Aarav Sharma', 'M', 'Jaipur', 2, 1, 7.5, '[email protected]', '2025-07-21');

A second Aarav is welcome, but not with roll number 101. Postgres refuses with duplicate key value violates unique constraint. A spreadsheet would have happily saved it.

"Unique" is not enough for a primary key. email is UNIQUE in students, but three students have no email (NULL). A key that can be missing cannot name a row, so email cannot be the primary key.

The foreign key: a pointer to another table

students.dept_id holds a department number. It must be a dept_id that really exists in departments. That rule is a foreign key: a column whose values must match the primary key of another table.

So instead of typing "Computer Science" in 7 student rows (and someday misspelling it), each student stores 1, and the name lives once, in departments.

A foreign key may be NULL unless you forbid it: Manish (120) has no department yet.

Pointing at nothing is refused

INSERT INTO students VALUES
  (130, 'Kavya Menon', 'F', 'Kochi', 9, 1, 8.0, '[email protected]', '2025-07-21');

There is no department 9, so Postgres refuses with violates foreign key constraint. This is called referential integrity: no pointer may lead nowhere.

One-to-many and many-to-many

One-to-many: one department has many students, and each student has one department. The foreign key sits on the many side (students.dept_id).

Many-to-many: a student takes many courses, and a course has many students. No single column can hold that, so a third table, enrollments, stores one row per (student, course) pair. It is called a junction table.

Its primary key is the pair (student_id, course_id): a key made of two columns is a composite key. Student 101 can appear many times, and CS101 can appear many times, but the pair (101, CS101) only once.

The college schema, as a map

Read the arrows as "points at". To find which department Riya studies in, go from her students row along dept_id to departments. Module 5 does this in one query with a JOIN.

Key definitions exams love:

• Super key: any set of columns that identifies a row (student_id, or student_id + name).
• Candidate key: a minimal super key; drop any column and it stops being unique.
• Primary key: the candidate key you choose; unique and NOT NULL.
• Alternate key: the candidate keys you did not choose.
• Foreign key: columns that refer to another table's primary key.

In one line

A primary key is unique and never NULL; a foreign key must match another table's key (or be NULL); many-to-many needs a junction table with a composite key.