Primary Keys and Foreign Keys in SQL Server: How Tables Actually Connect

Everything up to this chapter queried one table at a time. Real schemas are almost never one table — they’re a web of tables connected by keys, and understanding exactly how that connection is enforced (not just “it points to the other table”) is what makes JOINs in the next lesson click instead of feeling like memorized syntax.

Setup — a driver/trip example to follow along:

CREATE TABLE dbo.Driver (
    driver_id   INT IDENTITY(1,1) PRIMARY KEY,
    full_name   NVARCHAR(100) NOT NULL,
    city        NVARCHAR(50)  NOT NULL
);
CREATE TABLE dbo.Trip (
    trip_id       INT IDENTITY(1,1) PRIMARY KEY,
    driver_id     INT NOT NULL REFERENCES dbo.Driver(driver_id),
    distance_km   DECIMAL(6,2) NOT NULL,
    fare_usd      DECIMAL(8,2) NOT NULL
);

The Relationship, Visualized

A primary key (PK) uniquely identifies each row (driver_id in Driver) — SQL Server automatically creates a unique index behind every primary key, which is why lookups by PK are fast by default, before you’ve even thought about indexing. A foreign key (FK) is a column in one table that points to a primary key in another (Trip.driver_id → Driver.driver_id).

Watching the Constraint Actually Enforce Something

-- This fails — driver_id 999 doesn't exist in Driver:
INSERT INTO dbo.Trip (driver_id, distance_km, fare_usd) VALUES (999, 5.2, 12.50);
-- Msg 547: The INSERT statement conflicted with the FOREIGN KEY constraint ...

-- This fails too — you can't delete a driver who still has trips referencing them:
INSERT INTO dbo.Driver (full_name, city) VALUES ('Amir Khan', 'Austin');
INSERT INTO dbo.Trip (driver_id, distance_km, fare_usd) VALUES (1, 8.0, 18.00);
DELETE FROM dbo.Driver WHERE driver_id = 1;
-- Msg 547: The DELETE statement conflicted with the REFERENCE constraint ...

This is the entire point of a foreign key: SQL Server enforces that you can’t log a trip for a driver who doesn’t exist, and can’t delete a driver out from under existing trips — it rejects the operation outright, rather than silently leaving a trip pointing at nothing (an “orphaned row”). Without this constraint, that kind of data corruption is entirely possible and often goes unnoticed until a report breaks months later.

Three Relationship Shapes

A Many-to-Many Example, Concretely

CREATE TABLE dbo.Student (student_id INT IDENTITY PRIMARY KEY, name NVARCHAR(100) NOT NULL);
CREATE TABLE dbo.Course (course_id INT IDENTITY PRIMARY KEY, title NVARCHAR(100) NOT NULL);

-- The junction table: one row per student-course PAIR, with a composite primary key
CREATE TABLE dbo.Enrollment (
    student_id INT NOT NULL REFERENCES dbo.Student(student_id),
    course_id  INT NOT NULL REFERENCES dbo.Course(course_id),
    enrolled_on DATE NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY (student_id, course_id)
);

Neither Student nor Course has a foreign key pointing directly at the other — they can’t, since either side could relate to many rows on the other. The junction table’s composite primary key (both columns together) also does double duty: it prevents the same student from enrolling in the same course twice.