Lesson 1, Topic 1

In Progress

Lesson 1.2 – Relations, Primary Keys and Foreign Keys

Lesson 1.2 – Relations, Primary Keys & Foreign Keys (T-SQL)

Learning Objectives

By the end of this lesson, you will be able to:

Identify the four building blocks of any relational table: rows, columns, primary keys, and foreign keys.
Explain the difference between a one-to-many and a many-to-many relationship, and recognize when a junction table is needed.
Choose appropriate SQL Server data types (INT, NVARCHAR, DATE) for different kinds of information.
Write a CREATE TABLE statement in T-SQL with an IDENTITY primary key and a FOREIGN KEY constraint.
Insert a few sample rows with INSERT INTO and verify them with SELECT.

📚 Key Vocabulary

Term	Definition	SchoolHub Example
Table (Relation)	A two-dimensional grid of rows and columns that stores data about one type of entity.	The `Students` table holds one row per student.
Row (Record)	One entry in a table — one complete set of values across all columns.	`(7, 'Alice', 'Wong', 10)` is one row in `Students`.
Column (Field)	A single named attribute with a defined data type. All rows share the same columns.	`GradeLevel` is an `INT` column in `Students`.
Primary Key (PK)	A column (or set of columns) whose value uniquely identifies each row. Cannot be `NULL`.	`StudentID` is the PK of `Students` — every student has a different value.
Foreign Key (FK)	A column that references the primary key of another table to link the two.	`Classes.TeacherID` references `Teachers.TeacherID`.
IDENTITY	A SQL Server column property that auto-generates sequential numbers — usually used for surrogate PKs.	`StudentID INT IDENTITY(1,1) PRIMARY KEY` — SQL Server fills in 1, 2, 3...
Junction Table	A table whose only job is to link two other tables in a many-to-many relationship.	`Enrollments` links `Students` and `Classes`.
Schema	The overall design of a database — its tables, columns, data types, and relationships.	The SchoolHub schema = 4 tables with their columns and FK links.

💡 Real-World Analogy

Think of a well-organized binder. Instead of cramming every paper into one section, you use dividers: homework in one, notes in another, handouts in a third. At the back you keep an index — a quick lookup that says "Chemistry homework is in section 3."

A relational database is the same idea. Each table is a section of the binder. Each row is one piece of paper. Each column is the same kind of detail on every paper (date, subject, score). The primary key is the page number that uniquely identifies that paper. And a foreign key is a sticky note that says "see section 2, page 14" — instead of photocopying that page into your binder, you just point to it.

SchoolHub will work exactly like this. A student record doesn't repeat all the class details — it just points to a ClassID in the Classes table.

🎓 The big idea: Tables hold the data. Keys hold the links. Together they let you store every fact in exactly one place and still answer complicated questions about how those facts connect.

🧱 Concept Explanation

The four pieces of every table

Every relational table — whether in SchoolHub, a banking system, or Spotify — is built from the same four pieces:

Rows — the actual data, one record per row.
Columns — the named attributes, each with a fixed data type.
A primary key — one (or more) columns that uniquely identify each row.
Zero or more foreign keys — columns that point to the primary key of another table.

Here's a tiny Students table:

Students
+-----------+-----------+----------+------------+
| StudentID | FirstName | LastName | GradeLevel |
+-----------+-----------+----------+------------+
|     1     |  Alice    |  Wong    |     10     |   ← row
|     2     |  Bob      |  Garcia  |     10     |   ← row
|     3     |  Carol    |  Patel   |     11     |   ← row
+-----------+-----------+----------+------------+
       ↑           ↑          ↑          ↑
      PK         column     column     column

StudentID is the primary key — no two students have the same ID. FirstName, LastName, and GradeLevel are columns. Each row is one student.

Primary keys — the uniqueness rule

A primary key has two strict rules:

Every row must have a value (no NULLs allowed).
No two rows can have the same value.

Two students can both be named "Alice Wong," but they can't both have StudentID = 1. The PK is what tells SQL Server which specific row you're talking about.

In SQL Server, the cleanest way to create a primary key is with IDENTITY — a built-in feature that auto-generates sequential numbers. You declare it once, and SQL Server takes care of the rest.

T-SQL: Create a table with an IDENTITY primary key

CREATE TABLE Students (
    StudentID   INT           IDENTITY(1,1) PRIMARY KEY,
    FirstName   NVARCHAR(50)  NOT NULL,
    LastName    NVARCHAR(50)  NOT NULL,
    GradeLevel  INT           NOT NULL
);

Let's decode this:

INT — an integer column.
IDENTITY(1,1) — auto-number starting at 1, incrementing by 1.
PRIMARY KEY — enforces uniqueness and not-null.
NVARCHAR(50) — text up to 50 characters, supporting Unicode (so names like "José" or "李" work).
NOT NULL — this column can't be left blank.

Foreign keys — the linking rule

A foreign key is a column in one table that holds a value matching the primary key of another table. It's how you say "this row over here is connected to that row over there."

Suppose you have a Teachers table and a Classes table. Each class is taught by exactly one teacher. The connection lives in Classes.TeacherID:

Teachers
+-----------+-----------+----------+
| TeacherID | FirstName | LastName |
+-----------+-----------+----------+
|    10     |   John    |  Smith   |
|    11     |   Maria   |  Lee     |
+-----------+-----------+----------+
                     ↑
            (referenced by foreign key)
 
Classes
+---------+-----------+-----------+
| ClassID | ClassName | TeacherID |  ← FK pointing to Teachers
+---------+-----------+-----------+
|   101   |   Math    |    10     |  ← taught by Smith
|   102   |   History |    11     |  ← taught by Lee
|   103   |   Algebra |    10     |  ← also taught by Smith
+---------+-----------+-----------+

Notice "Mr. Smith" is stored once, in the Teachers table. The Classes table only stores his ID. If Smith changes his last name, you update one row — and every class that points to him is instantly correct.

T-SQL: A foreign key linking Classes to Teachers

CREATE TABLE Teachers (
    TeacherID   INT           IDENTITY(1,1) PRIMARY KEY,
    FirstName   NVARCHAR(50)  NOT NULL,
    LastName    NVARCHAR(50)  NOT NULL
);
GO
 
CREATE TABLE Classes (
    ClassID     INT           IDENTITY(1,1) PRIMARY KEY,
    ClassName   NVARCHAR(100) NOT NULL,
    TeacherID   INT           NOT NULL,
    FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID)
);
GO

The GO at the end isn't really T-SQL — it's a batch separator used by SQL Server tools. It tells the editor: "Send everything up to here as one batch, then start fresh." You'll see GO all the time in SQL Server scripts.

With this foreign key in place, SQL Server will refuse to let you insert a class with a TeacherID that doesn't exist in Teachers. That's called referential integrity, and it's the database protecting you from broken links in your data.

One-to-many vs. many-to-many

Two students can take the same class. One student can take many classes. That's a many-to-many relationship — and a single foreign key can't capture it.

The solution is a junction table (sometimes called a bridge or link table). It sits between the two and breaks the many-to-many into two one-to-many relationships:

  Students (1) ────── (∞) Enrollments (∞) ────── (1) Classes
              one student, many enrollments       one class, many enrollments

The Enrollments table holds nothing but the links: one StudentID, one ClassID, and maybe extra columns about the enrollment itself (like the grade earned).

T-SQL: A junction table with a composite primary key

CREATE TABLE Enrollments (
    StudentID   INT           NOT NULL,
    ClassID     INT           NOT NULL,
    Grade       NVARCHAR(2)   NULL,
    PRIMARY KEY (StudentID, ClassID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (ClassID)   REFERENCES Classes(ClassID)
);
GO

Notice the primary key here is (StudentID, ClassID) — a composite primary key. The combination must be unique: a student can only enroll in the same class once. This pattern (junction table with composite PK and two foreign keys) is one of the most useful in all of database design — you'll use it constantly.

SQL Server data types you'll meet in this unit

Data Type	Stores	Use For
`INT`	Whole numbers from about –2 billion to +2 billion.	IDs, counts, ages, grade levels.
`NVARCHAR(n)`	Variable-length Unicode text up to n characters.	Names, titles, emails, anything with letters.
`CHAR(n)`	Fixed-length text exactly n characters.	Codes that are always the same length (e.g., a 2-letter grade).
`DATE`	A calendar date with no time portion.	Birth dates, enrollment dates, due dates.
`DATETIME2`	A date and time, accurate to fractions of a second.	Timestamps (when a row was created or updated).
`DECIMAL(p,s)`	Exact numbers with fixed precision and scale.	Money, GPAs, percentages — anywhere rounding matters.
`BIT`	`0`, `1`, or `NULL`.	True/false flags (e.g., `IsActive`).

💡 NVARCHAR vs VARCHAR: NVARCHAR supports Unicode (international characters), VARCHAR only supports basic ASCII. For names and anything user-facing, default to NVARCHAR — it's the SQL Server best practice today.

✍️ Best Practices

🔑 Always Pick a Primary Key

Every table gets a PK — no exceptions. Without one, you can't reliably reference rows or build relationships.

🔢 Use IDENTITY for Surrogate Keys

When no natural unique field exists, use INT IDENTITY(1,1) PRIMARY KEY. SQL Server handles the numbering for you.

📝 Prefer NVARCHAR for Text

It supports Unicode out of the box. Pick a sensible length (50 for names, 100 for titles, 255 for descriptions).

🚫 Mark Required Columns NOT NULL

If a column must have a value, say so. NOT NULL is the database stopping bad data at the door.

🔗 Declare Foreign Keys Explicitly

Don't just store an ID column — add the FOREIGN KEY ... REFERENCES clause so SQL Server enforces the link.

🏷️ Match Names Across Tables

If the PK is StudentID in Students, call it StudentID in every table that references it — never StuID or S_ID.

🧪 Hands-On Practice

Warm-Up

Identify the Parts of a Table

1. Label the Pieces

Look at this small table. On paper or in your notes, identify the rows, the columns, and which column is the most likely primary key. Then circle the column that would probably be a foreign key if there were a Teachers table.

Classes

+---------+--------------+-----------+
| ClassID |  ClassName   | TeacherID |
+---------+--------------+-----------+
|   101   |  Math        |    10     |
|   102   |  History     |    11     |
|   103   |  Algebra II  |    10     |
+---------+--------------+-----------+

2. Choose the Right Data Type

For each piece of data below, write down the SQL Server data type you'd use and a quick reason:

A student's grade level (9, 10, 11, 12)
A student's first name
A student's date of birth
A student's GPA (like 3.75)
A yes/no flag for "currently enrolled"
A 2-character letter grade like A+, B−

3. One-to-Many or Many-to-Many?

For each pair, decide whether you'd use a single foreign key (one-to-many) or a junction table (many-to-many):

Authors and Books (assume each book has one author)
Authors and Books (a book can have multiple authors)
Customers and Orders (one customer has many orders)
Students and Clubs (a student can be in many clubs; a club has many members)
Cars and Owners (assume one car, one owner)

4. Sketch a Mini-Schema

You're designing a library database. Sketch (on paper) the tables you'd need to track which books are checked out by which library members over time. What are the tables? What are the keys? Is there a many-to-many relationship hiding in there?

💻 Coding Lab — Activity 1.2

Coding Challenge

Build Your First Two Linked Tables

Time to write real T-SQL. You'll create two tables — Teachers and Classes — with the Teachers table being referenced by a foreign key in Classes. Then you'll insert some sample rows and prove the link works by running a JOIN query.

Open the Knowledge Pillars Coding Lab and create a new file lesson1_2.sql.
Type the CREATE TABLE Teachers block exactly as shown below. Don't copy-paste — typing it builds the syntax into your memory.
Highlight just that block and press Execute (or F5). You should see "Commands completed successfully."
Below it, type the CREATE TABLE Classes block. Execute it.
Type and execute the INSERT INTO statements to add some real data.
Type and execute the final SELECT ... INNER JOIN query — that's the payoff: combined data from both tables.
Save your file as lesson1_2_(FirstName_LastName).sql.

lesson1_2.sql — Teachers table

-- Teachers: one row per teacher
CREATE TABLE Teachers (
    TeacherID   INT           IDENTITY(1,1) PRIMARY KEY,
    FirstName   NVARCHAR(50)  NOT NULL,
    LastName    NVARCHAR(50)  NOT NULL,
    Subject     NVARCHAR(50)  NULL
);
GO

lesson1_2.sql — Classes table with FK

-- Classes: each class is taught by one teacher
CREATE TABLE Classes (
    ClassID     INT           IDENTITY(1,1) PRIMARY KEY,
    ClassName   NVARCHAR(100) NOT NULL,
    TeacherID   INT           NOT NULL,
    FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID)
);
GO

lesson1_2.sql — Insert sample data

-- Add three teachers. Notice we don't list TeacherID — IDENTITY fills it in.
INSERT INTO Teachers (FirstName, LastName, Subject)
VALUES
    ('John',  'Smith', 'Math'),
    ('Maria', 'Lee',   'History'),
    ('David', 'Brown', 'Science');
GO
 
-- Add four classes. Match TeacherID to one of the rows above.
INSERT INTO Classes (ClassName, TeacherID)
VALUES
    ('Algebra I',     1),
    ('Algebra II',    1),
    ('World History', 2),
    ('Biology',       3);
GO

lesson1_2.sql — Prove the link with a JOIN

-- The payoff: combine columns from both tables
SELECT
    c.ClassName,
    t.FirstName,
    t.LastName
FROM Classes c
INNER JOIN Teachers t
    ON c.TeacherID = t.TeacherID
ORDER BY c.ClassName;

Expected Output

🔑 What just happened? The INNER JOIN ... ON clause matched each class's TeacherID to the corresponding teacher row, letting you see the teacher's name alongside the class name. This is the first taste of combining data — a skill you'll build deeply in Unit 7.

⚠️ Test the foreign key: Try inserting a class with TeacherID = 99 (a teacher that doesn't exist). SQL Server will refuse with a message about the FOREIGN KEY constraint. That's the database protecting the link!

🔁 Bonus Challenge

Add a 4th teacher and a 5th class taught by them. Re-run the SELECT ... INNER JOIN. Does the new class appear?
Run SELECT * FROM Teachers; by itself. What did IDENTITY assign as the TeacherIDs?
Try to drop the Teachers table with DROP TABLE Teachers;. What error do you get? (SQL Server protects you because Classes still references it.)

✅ Self-Check

I created a new file named lesson1_2.sql.

I successfully created the Teachers table with IDENTITY(1,1).

I created the Classes table with a FOREIGN KEY referencing Teachers.

I inserted at least 3 teachers and 4 classes.

My INNER JOIN query returned matching class + teacher rows.

I tested the FK by trying to insert a class with an invalid TeacherID.

I saved the file as lesson1_2_(FirstName_LastName).sql.

0 of 7 complete

🎤 Questions to Think About

Discuss with your class or jot down your answers:

Why do we use IDENTITY for primary keys instead of using something like a student's name?
If you had a Books table and an Authors table, what would the foreign key look like — and which table would have it?
What goes wrong if the same primary key value appears in two rows? Why does the database not allow this?
Can a column be both a primary key in one table and a foreign key in another? Give an example.
Why does SQL Server refuse to let you delete a teacher who still has classes pointing to them? Is that helpful or annoying?