Lesson 4: Relationships and Referential Integrity (How Tables Connect Safely)

Section 2 — Lesson 4: Relationships and Referential Integrity (How Tables Connect Safely)

Welcome back. In Lesson 3, you learned how keys and constraints prevent the most expensive database problems:
duplicates, orphan records, and invalid values.

Now we’re going to connect the dots—literally. Real systems are not one table. They are many tables connected together.
Understanding relationships and referential integrity is what turns you from “someone who can write SQL”
into someone who can design and protect a real production database.

Lesson description: You will learn the main relationship types (one-to-many, one-to-one, many-to-many),
how to model them in SQL Server, how referential integrity works, what cascade actions do (and why DBAs use them carefully),
and how to spot and prevent design mistakes that cause missing data, double-counting, and broken reports.

1) What Is a Relationship in a Database?

A relationship describes how rows in one table connect to rows in another table. In SQL Server, relationships are
typically enforced using foreign keys.

Simple way to think about it: A relationship answers questions like:

• Which bookings belong to this customer?
• Which order items belong to this order?
• Which permissions belong to this user?

DBA mindset: Relationships are not optional “nice-to-haves.” They are what makes the database consistent.
If you model relationships incorrectly, your application might still run—but your reports and operations will become unreliable.

2) The Three Relationship Types You Must Master

A) One-to-Many (1:N) — The Most Common Relationship

One row in Table A can relate to many rows in Table B.
This is the most common relationship in OLTP systems.

• Customer → Booking: one customer can have many bookings.
• Order → OrderItem: one order can have many line items.
• Department → Employee: one department can have many employees.

How it is modeled: The “many” table stores the foreign key.
Example: Booking.CustomerID references Customer.CustomerID.

B) One-to-One (1:1) — Use Carefully

One row in Table A relates to exactly one row in Table B.
This relationship is less common and is often used for separation of concerns:
security, privacy, optional details, or very large columns you don’t want in the main table.

• User → UserProfile: one user has one profile record.
• Employee → EmployeePrivateDetails: one employee has one private record (protected access).

How it is modeled: Table B references Table A, and you enforce uniqueness so it cannot relate to multiple rows.
In practice: a foreign key plus a UNIQUE constraint (or shared primary key design).

DBA caution: Many teams create 1:1 tables unnecessarily. If you can keep data in one table safely, do it.
Use 1:1 when there is a clear reason (security boundaries, optional-heavy columns, performance considerations).

C) Many-to-Many (M:N) — Always Use a Junction Table

Many rows in Table A relate to many rows in Table B.
SQL Server does not model this directly—you implement it using a junction table (also called a bridge table).

• Student ↔ Course: many students enroll in many courses.
• User ↔ Role: many users can have many roles.
• Product ↔ Category: many products can belong to many categories.

How it is modeled: Create a junction table that contains two foreign keys:
one to Table A and one to Table B. The junction table is the “relationship store.”

DBA best practice: Add a composite primary key or unique constraint on the two foreign key columns
to prevent duplicate relationships (e.g., the same user-role assignment stored twice).

3) Referential Integrity (The “Database Truth” Rule)

Referential integrity means: every foreign key value must reference an existing primary key value.
In other words, SQL Server ensures relationships remain valid.

If referential integrity is not enforced, you get:

• Orphan records: bookings without customers, order items without orders.
• Broken joins: reports that silently drop data when joining tables.
• Inconsistent totals: double-counting or missing counts because relationships are unreliable.
• Support emergencies: “Why is this booking not showing up?” “Why is this invoice missing items?”

DBA rule: Enforce referential integrity by default. Avoid exceptions unless you have a documented reason.

4) Cascading Actions (POWERFUL—Use Carefully)

SQL Server foreign keys can be configured with cascading behaviors. This controls what happens in the “child” table
when a row in the “parent” table is updated or deleted.

Common cascade options

• ON DELETE CASCADE: deleting a parent row automatically deletes related child rows.
• ON UPDATE CASCADE: updating a parent key updates related child keys.
• NO ACTION / RESTRICT (default behavior): SQL Server blocks deletes/updates that would break integrity.
• SET NULL: child foreign key becomes NULL when the parent is deleted (only if column allows NULL).

DBA caution (very important): Cascades can cause unexpected large deletes or updates and can impact performance.
In production systems, many DBAs prefer:
NO ACTION + explicit application logic (or stored procedures) for controlled deletes.

Professional standard: If cascades are used, they should be intentional, documented, and tested in non-production first.

5) Common Relationship Design Mistakes (And How to Avoid Them)

Mistake A: “Storing multiple values in one column”

Example: storing roles as 'Admin,Reporting,Support' in a single column.
This breaks relational design and makes queries, constraints, and indexing much harder.

Fix: Create a junction table (UserRole) and enforce relationships properly.

Mistake B: “Missing foreign keys because the app will handle it”

This creates silent data corruption over time. Application code changes; the database must stay correct.

Fix: Enforce foreign keys unless you have a proven exception and can accept the risk.

Mistake C: “Wrong cardinality assumptions”

Example: assuming one customer can only have one booking, then later discovering repeat customers exist.

Fix: Model relationships based on real business requirements and future growth, not a temporary assumption.

Mistake D: “Duplicate relationships in many-to-many tables”

Example: the same user-role assignment stored multiple times.

Fix: Use a composite primary key or UNIQUE constraint on (UserID, RoleID).

6) Evidence-First Verification (DBA Habit)

Relationships must be verified, not assumed. In a lab/test environment, DBAs validate that referential integrity
is truly enforced.

1. Create the parent and child tables with a foreign key.
2. Try inserting a child row that references a parent that does not exist.
3. Confirm SQL Server blocks it with a foreign key violation error.
4. Document what this prevents (orphan records and broken reporting).

This evidence-first discipline will become your signature behavior as a professional DBA.

7) Practice (Write This in Your DBA Notebook)

1. Write one example of a one-to-many relationship in a real business system.
2. Write one example of a many-to-many relationship and name the junction table you would create.
3. Explain what referential integrity prevents in one paragraph.
4. Decide whether you would use ON DELETE CASCADE for your example—and explain your reason.

Lesson Summary

• Most systems use one-to-many relationships; many-to-many requires a junction table.
• Referential integrity ensures foreign keys always point to real parent rows.
• Cascading actions are powerful but must be used intentionally and documented.
• DBAs verify relationships with evidence to prevent silent data corruption.

Next Lesson: Transactions and ACID in Practice (And What Breaks It)

In the next lesson, we will study transactions: how SQL Server guarantees “all-or-nothing” reliability,
what ACID really means in production, and how mistakes (long transactions, poor isolation choices, bad error handling)
can cause blocking, deadlocks, and inconsistent business results.