How do I fix 'FK creation fails on existing data'?

Likely cause: orphan rows already violate the relationship. Fix: find them with a LEFT JOIN ... WHERE parent.id IS NULL, clean up, then add the constraint.

How do I fix 'UNIQUE violated by concurrent inserts'?

Likely cause: two transactions inserted the same value simultaneously. Fix: the constraint still protects you; handle the error with an upsert (ON CONFLICT).

How do I fix 'CASCADE deleted more than expected'?

Likely cause: a chain of cascades propagated further than intended. Fix: prefer RESTRICT for destructive paths and delete explicitly in application order.

How do I fix 'CHECK blocks a legitimate edge case'?

Likely cause: the rule was stricter than the real domain. Fix: fix the rule, not the data: ALTER the constraint after confirming the business rule.

How do I fix 'constraint names are auto-generated gibberish'?

Likely cause: no explicit name was given. Fix: name constraints (uq_email, invoice_amount_check) so errors are readable.

SQL Constraints & Keys: Full Database Guide

I enforced data integrity at the database layer with primary, foreign, unique, and check constraints rather than trusting application code. Pushing validation into the schema guaranteed correctness even when multiple clients write concurrently.

Objective & Context

Constraints are the last line of defense for data correctness. This lab applies entity integrity (PK), referential integrity (FK with cascade rules), uniqueness, and domain validation (CHECK), so invalid data is rejected regardless of which client writes it.

Learning Objectives

Declare primary keys for row identity.
Enforce relationships with foreign keys and choose cascade behaviour.
Add CHECK constraints for domain rules.
Enforce uniqueness with UNIQUE constraints.
Verify rejection of invalid data with test inserts.

Environment & Prerequisites

Prerequisites

PostgreSQL 16 with a normalized schema
Test data including intentionally invalid rows
Understanding of cascade vs restrict behaviour
Assumed skill: CREATE TABLE basics

flowchart LR I[INSERT] --> PK{PK unique?} PK -->|no| R[Reject] PK -->|yes| FK{FK exists?} FK -->|no| R FK -->|yes| CK{CHECK passes?} CK -->|no| R CK -->|yes| OK[Commit]

Step-by-Step Execution

1. Define keys and a check constraint

CREATE TABLE invoice (id SERIAL PRIMARY KEY, amount NUMERIC CHECK (amount > 0), customer_id INT REFERENCES customer(id) ON DELETE RESTRICT);

Expected output:

CREATE TABLE

What this teaches: The PRIMARY KEY gives each row an identity, the CHECK encodes the business rule (amounts must be positive), and the FK ties invoices to real customers.

2. Add a uniqueness rule

ALTER TABLE customer ADD CONSTRAINT uq_email UNIQUE (email);

Expected output:

ALTER TABLE

What this teaches: Uniqueness on email moves a rule every app would reimplement into the one place that cannot be bypassed: the schema itself.

3. Test rejection of invalid data

INSERT INTO invoice (amount, customer_id) VALUES (-5, 1);

ERROR: new row violates check constraint "invoice_amount_check"

Expected output:

ERROR: new row violates check constraint "invoice_amount_check"

What this teaches: The database rejected the bad row before it was stored; this is integrity working as designed, and the error names the exact rule violated. Insert the parent customer row first (or confirm id 1 exists) so this test isolates the CHECK constraint instead of tripping the foreign key on a missing parent.

4. Test foreign key rejection

INSERT INTO invoice (amount, customer_id) VALUES (100, 9999);

Expected output:

ERROR:  insert or update on table "invoice" violates foreign key constraint "invoice_customer_id_fkey"
DETAIL:  Key (customer_id)=(9999) is not present in table "customer".

What this teaches: Use an id you have confirmed does not exist (9999 here); the amount passes the CHECK, so the foreign key is the layer that rejects the row. Watching each constraint reject its own class of bad data is how you verify the layers compose correctly.

How It Works

Constraints push validation into the schema, where every client is subject to them. Application code can be bypassed, forgotten, or duplicated across services; a CHECK constraint cannot be bypassed by any writer, which is why integrity belongs in the database.

Foreign keys add referential integrity: an invoice cannot reference a nonexistent customer, and the ON DELETE clause declares what happens to children when a parent goes away. RESTRICT blocks the delete, CASCADE propagates it; the choice encodes business policy.

Constraints are also documentation. Reading the DDL tells you the domain rules (amounts positive, emails unique, invoices belong to customers) without hunting through application code, which is why test inserts that provoke each error are part of the lab.

flowchart TD APP["application code
(bypassable)"] --> DB["database constraints
(unbypassable)"] DB --> PK["PRIMARY KEY
row identity"] DB --> FK["FOREIGN KEY
references hold"] DB --> CK["CHECK / UNIQUE
domain rules"]

Validation & Testing

Attempt inserts that violate each constraint type and confirm the database rejects them. Pass criteria: duplicate PK/UNIQUE rejected, orphan FK rejected, negative amount rejected by CHECK, and valid rows commit.

Advanced: Troubleshooting

FK insert fails: the referenced parent row must exist first.
Cascade surprises: choose ON DELETE RESTRICT vs CASCADE deliberately.
Slow constraint checks: index the FK columns to speed validation.

Troubleshooting

Symptom	Likely cause	Fix
FK creation fails on existing data	orphan rows already violate the relationship	find them with `SELECT c.* FROM child c LEFT JOIN parent p ON c.customer_id = p.id WHERE p.id IS NULL;`, clean up, then add the constraint
UNIQUE violated by concurrent inserts	two transactions inserted the same value simultaneously	the constraint still protects you; handle the error with an upsert (ON CONFLICT)
CASCADE deleted more than expected	a chain of cascades propagated further than intended	prefer RESTRICT for destructive paths and delete explicitly in application order
CHECK blocks a legitimate edge case	the rule was stricter than the real domain	fix the rule, not the data: ALTER the constraint after confirming the business rule
CHECK lets NULLs through	a CHECK expression that evaluates to NULL (unknown) passes	add NOT NULL alongside the CHECK when the column must always hold a value
constraint names are auto-generated gibberish	no explicit name was given	name constraints (uq_email, invoice_amount_check) so errors are readable

Key Results

Pushed 100% of integrity rules into the database layer.
Rejected invalid rows across PK, FK, UNIQUE, and CHECK in testing.
Guaranteed referential integrity with explicit cascade rules.
Removed reliance on application code for core validation.

Key Takeaways

Integrity belongs in the schema: constraints cannot be bypassed by any client.
Primary keys identify rows; foreign keys keep relationships honest.
CHECK and UNIQUE encode domain rules once, for all writers.
Choose ON DELETE behaviour deliberately: RESTRICT is the safe default.
Name constraints so violation errors are human-readable.

Practice Challenges

Challenge 1: Self-referencing table

Goal: Add an employees table where manager_id references employees(id), and decide the ON DELETE rule.

Hint: RESTRICT or SET NULL are the sane choices; CASCADE would delete whole org charts.

Challenge 2: Exclusion rule

Goal: Add a constraint that prevents overlapping booking date ranges for the same room.

Hint: PostgreSQL exclusion constraints with gist indexes solve this; a plain CHECK cannot see other rows.

SQL Constraints and Keys for Guaranteed Data Integrity

Objective & Context

Learning Objectives

Environment & Prerequisites

Prerequisites

Step-by-Step Execution

1. Define keys and a check constraint

2. Add a uniqueness rule

3. Test rejection of invalid data

4. Test foreign key rejection

How It Works

How It Works

Validation & Testing

Troubleshooting

Key Results

Key Takeaways

Practice Challenges

Challenge 1: Self-referencing table

Challenge 2: Exclusion rule

Related Labs