Lesson 3: Keys (PK/FK/UNIQUE/CHECK) — Why They Prevent Data Disasters

Section 2 — Lesson 3: Keys (PK/FK/UNIQUE/CHECK) — Why They Prevent Data Disasters

Welcome back. In the previous lesson, you learned how tables, rows, columns, and constraints create structure and reliability.
Now we’re going to focus on the rules that protect databases from the most expensive problems in the real world:
duplicates, orphan records, and invalid values.

Think of this lesson as your “DBA safety system” lesson. If you master keys and constraints, you will build databases that
applications can trust—even when users make mistakes or developers accidentally ship bugs.

Lesson description: You will learn exactly what primary keys, foreign keys, unique constraints, and check constraints do,
why they are essential in production, how to choose the right strategy (surrogate vs natural keys),
how to avoid common constraint mistakes, and how DBAs verify enforcement using evidence-first testing.

1) The Big Picture: Why Keys Matter to a DBA

A DBA’s goal is not only to keep SQL Server online. A DBA must keep the data correct.
Keys and constraints are how SQL Server enforces correctness at the database level.

Without these rules, the application might still “work” for a while—but over time it becomes unreliable.
That unreliability shows up as billing mistakes, missing records, broken reports, and incidents that cost money.

2) Primary Key (PK): The Identity of Every Row

A Primary Key uniquely identifies each row in a table. It answers the question:
“How do we point to exactly one row, every time, without confusion?”

What a Primary Key guarantees

• Uniqueness: no two rows share the same primary key value.
• Stability: the identity of a row does not change over time.
• Relationships: other tables can safely reference this row using foreign keys.

Common PK strategies in SQL Server

• INT IDENTITY (Surrogate Key): most common in OLTP systems.
  Why: fast, compact, efficient for indexing and joins.
• UNIQUEIDENTIFIER (GUID): common in distributed systems or when IDs must be generated outside SQL Server.
  Risk: can increase index fragmentation and storage; must be used carefully.
• Natural Key: a real-world value like Email or SSN.
  Risk: values can change; privacy concerns; not always stable.

DBA recommendation (industry standard)

In many production systems, the safest pattern is:
use a surrogate PK (like INT IDENTITY) for identity and joins,
and enforce real-world uniqueness using UNIQUE constraints (Email, InvoiceNumber, etc.).

3) Foreign Key (FK): The Rule That Prevents Orphan Records

A Foreign Key enforces relationships between tables.
It guarantees that when you store a reference (like CustomerID in an Order table),
that referenced Customer actually exists.

What an FK prevents

• Orphan records: orders that reference customers that do not exist.
• Broken reporting: joins that unexpectedly “lose data” because references are invalid.
• Data chaos: records that cannot be traced back to a real entity.

DBA real-world example

• Correct: every Booking must reference an existing Customer.
• Disaster: bookings exist with CustomerID values that do not match any customer.

DBA note: Some teams try to avoid foreign keys for “performance reasons.”
In most systems, missing foreign keys cost more in operational failures than they save in performance.
A professional DBA pushes for integrity unless there is a proven, documented exception.

4) UNIQUE Constraint: The Rule That Prevents Duplicates

A UNIQUE constraint ensures that a value cannot appear more than once in a column (or combination of columns).
This prevents business-breaking duplicates.

Common columns that should often be UNIQUE

• Email address (if your business rules require one account per email)
• Invoice number
• Booking confirmation code
• Employee badge number

Important DBA detail

SQL Server allows multiple NULLs in a UNIQUE constraint (because NULL means “unknown”).
If your system requires “value must be present and unique,” then enforce:
NOT NULL + UNIQUE together.

5) CHECK Constraint: The Rule That Blocks Impossible Values

A CHECK constraint enforces rules about allowed values or ranges.
This prevents the database from storing values that should never exist.

Common CHECK patterns

• Non-negative totals: TotalAmount >= 0
• Valid quantity: Quantity > 0
• Status validation: Status IN ('NEW','CONFIRMED','COMPLETED','CANCELED')
• Percent validation: DiscountPercent BETWEEN 0 AND 100

DBA mindset: CHECK constraints are one of the easiest ways to stop data disasters early.
They create guardrails so the application cannot accidentally store nonsense.

6) Putting It Together: The “Safe Table” Checklist

When you review a table as a DBA, you want to see these protections in place:

1. A Primary Key (every table should have one).
2. Foreign Keys wherever relationships must be enforced.
3. UNIQUE constraints on business identifiers that must not duplicate.
4. CHECK constraints to block impossible values.
5. NOT NULL discipline for required attributes.
6. Defaults for standard fields (CreatedAt, Status, etc.).

7) Evidence-First Verification (DBA Habit You Must Build)

In real DBA work, you never assume constraints are doing what you think.
You test them safely in a lab/test environment.

Verification approach (simple and professional)

1. Create the rule (PK/FK/UNIQUE/CHECK).
2. Attempt to insert invalid data that violates the rule.
3. Confirm SQL Server rejects it with an error.
4. Document what the rule prevented (business impact).

This is the same evidence-first discipline you’ll apply in backups, restores, performance tuning, and incident response.

8) Practice (Write This in Your DBA Notebook)

1. Pick a table: Customer, Booking, Order, Invoice, or Product.
2. Define a primary key strategy (INT IDENTITY or UNIQUEIDENTIFIER) and explain why.
3. Choose one column that should be UNIQUE and explain the business reason.
4. Write two CHECK constraints that would prevent impossible values.
5. List one relationship that should be enforced with a foreign key and explain what it prevents.

Lesson Summary

• PK gives every row a stable identity.
• FK enforces real relationships and prevents orphan records.
• UNIQUE prevents duplicates that break business operations.
• CHECK blocks impossible values and creates guardrails.
• A DBA verifies enforcement with evidence, not assumptions.

Next Lesson: Relationships and Referential Integrity

In the next lesson, we will zoom out and focus on how tables connect to each other:
one-to-many, one-to-one, and many-to-many relationships, junction tables, and how referential integrity
keeps the entire database consistent as systems grow.