Database constraints
On this page 8 topics
Overview
Topics
- Unique
- foreign keys
- check constraints
- indexes introduction
Learn first
2 h of practice (estimate)
Learning outcomes
- Enforce invariants in the database with unique, foreign-key, not-null, and check constraints.
- Translate constraint violations into meaningful API errors (e.g., 409 for duplicates).
- Explain why application-level checks alone fail under concurrency.
- Create indexes for foreign keys and common lookups, and verify use with
EXPLAIN.
Lessons
Lab and exercises
Lab
JNR-034-LAB1 · Race the duplicate
Outcomes: 1–4 · Time: ~2 h
- Implement product creation with an application check only (
existsBySkuthensave). - Write a concurrent test: two threads released together create the same SKU; show it can create two rows.
- Add a unique constraint through a Flyway migration.
- Map the violation to a 409 response with a problem-details body.
- Add an index on
order_line.order_id; show the plan withEXPLAIN.
Acceptance: the concurrent test reliably produces one success and one 409.
Exercises (assessment: add constraints and indexes to a schema from requirements)
| ID | Outcome | Task |
|---|---|---|
| JNR-034-EX01 | 1 | Add unique, foreign-key, not-null, and check constraints to a provided customer/orders schema. |
| JNR-034-EX02 | 1 | Write a composite unique constraint: one review per customer per product. |
| JNR-034-EX03 | 2 | Write an exception handler that maps a duplicate email to 409 and a check violation to 400. |
| JNR-034-EX04 | 3 | Explain with a timeline why existsBy then save can produce duplicates. |
| JNR-034-EX05 | 4 | Create indexes for foreign keys and a common lookup; explain why you did not index every column. |
| JNR-034-EX06 | 4 | Run EXPLAIN before and after an index and say what changed in the plan. |
Show solutions
- EX01
unique (email),foreign key (customer_id) references customer(id),not nullon required columns,check (total >= 0). - EX02
alter table review add constraint ux_review_customer_product unique (customer_id, product_id); - EX03
@ExceptionHandler(DataIntegrityViolationException.class)returningProblemDetailwithHttpStatus.CONFLICTfor the unique constraint name andBAD_REQUESTfor the check constraint name. - EX04 T1 checks: none. T2 checks: none. T1 inserts, T2 inserts. Both succeeded because neither saw the other's uncommitted insert.
- EX05 Index foreign keys (
order_line.order_id) and columns in frequentwhereandjoin; extra indexes slow writes and use space. - EX06 Before:
Seq Scanon the table; after (on a large enough table):Index Scanusing the new index, lower cost.
Quiz
Outcomes: O1 constraints · O2 violations to API errors · O3 concurrency and application checks · O4 indexes and EXPLAIN.
A self-check for you: nothing is graded, saved or sent anywhere. Choose an answer, then check it. You can try again as often as you like.
Practical part
Six exercises JNR-034-EX01 to EX06 plus the lab; the assessment is adding constraints and indexes to a schema from requirements (EX01, EX05). Pass: constraints and indexes correct, at least five of six exercises correct, and the concurrent test producing one success and one 409 every run.
Common mistakes
"check then insert" without a constraint; indexing every column.
Assessment
Add constraints and indexes to a schema from requirements.
Career relevance
Index internals (MID-012) and data integrity (SRC-014).