LEARN. PRACTICE. GROW.Concept and mechanism
Constraints express requirements the database must enforce. CHECK rejects FALSE, but an UNKNOWN expression can pass; requiring a known positive amount also needs NOT NULL. UNIQUE on an optional column can accept several NULL rows, whereas a primary key requires presence and uniqueness. Foreign keys connect references to valid keys and expose mapping errors during loads. Do not disable them merely to obtain a green result. When a constraint fails, identify the violated rule and authorized correction flow. Changing key representation or adding a commit does not replace referential integrity. Use sample rows that exercise both missing and conflicting references during review.
Guided application
In a fictional platform, a sequence supplies technical keys but can leave gaps after rollback or loss of cached values. NOCACHE does not turn that mechanism into gap-free business numbering. Unsynchronized MAX(id)+1 creates collision risk between sessions. If the business requires specific sequential control, design it separately with concurrency and audit requirements. Indexes accelerate particular access paths but consume space and write effort; assess queries, selectivity, and observed plans. A nonunique index enforces neither positive values nor presence. For an updatable view, WITH CHECK OPTION can retain the filter for changes made through it, whereas WITH READ ONLY prevents DML. Confirm the mechanism matching the intended rule.
amount NUMBER NOT NULL CHECK (amount > 0) expresses presence and positivity; a simple index does not replace those rules.
Common pitfalls
CHECK as NOT NULL; UNIQUE as primary key; sequence as continuous ledger; index as cost-free benefit.
Related topics: Model, SELECT, and result population · Functions, conversions, and data contracts · Joins, grain, and aggregation
Connect each requirement to the mechanism actually enforcing it.
Reference: Integrity constraints · 1Z0-071 public objectives inspected 2026-09-30; revision date not published