Keys, Constraints & Referential Integrity — SQL Course

What you'll learn

Protect identity and invariants with primary, unique, foreign, check, and not-null constraints. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

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

Apply Primary keys to a realistic data question
Apply Candidate keys to a realistic data question
Apply Foreign keys to a realistic data question
Apply ON DELETE actions to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Primary key	The chosen stable identity of a row	Keep it unique, non-null, and independent of mutable display values
Foreign key	A database-enforced reference	Use whenever orphaned references would be invalid
CHECK	A row-level Boolean invariant	Encode universal range and state rules close to the data

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the database invariant question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Protect inventory relationships

Named constraints make failures understandable and cascades are selected from lifecycle semantics.

inventory_constraints.sql

ALTER TABLE inventory.products
  ADD CONSTRAINT products_pkey PRIMARY KEY (id),
  ADD CONSTRAINT products_sku_key UNIQUE (sku),
  ADD CONSTRAINT products_price_nonnegative CHECK (price >= 0);

CREATE TABLE inventory.stock (
  product_id bigint PRIMARY KEY REFERENCES inventory.products(id) ON DELETE CASCADE,
  quantity integer NOT NULL CHECK (quantity >= 0)
);

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

Application-only uniqueness checks race under concurrency. Use a unique constraint and translate its error.

Independent workshop

Build a review-ready database invariant lab against the course commerce dataset.

Your finished workshop must include:

Primary keys
Candidate keys
Foreign keys
ON DELETE actions
CHECK
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Primary key: Keep it unique, non-null, and independent of mutable display values
Foreign key: Use whenever orphaned references would be invalid
CHECK: Encode universal range and state rules close to the data

Quick check

1. Which rule best applies to Primary key?

2. Which rule best applies to Foreign key?

3. Which rule best applies to CHECK?

Next: INSERT, Generated Values & Bulk Loading