Relational Integrity Constraints Lab (Interactive)
Insert broken rows, switch constraints off, and delete parents to see the damage. Attempt duplicate emails, orphan foreign keys, and negative totals against live UNIQUE, CHECK, and FOREIGN KEY rules, then disable constraints or pick a delete policy to see what integrity costs.
Integrity Constraint Lab: PK · FK · UNIQUE · CHECK
Attempt writes that violate each constraint and watch the engine accept or reject them.
Statement Log
How It Works Under the Hood
Constraints are declarative contracts enforced by the engine, not application code. A primary key guarantees row uniqueness, a foreign key guarantees every order points at an existing user, UNIQUE blocks duplicate emails, and CHECK encodes business rules like positive totals. Turn them off and the database silently becomes a junk drawer: orphan rows and duplicate identities accumulate with no error raised. ON DELETE policies — RESTRICT, CASCADE, SET NULL — decide whether deleting a parent is blocked, removes dependents, or leaves dangling references.
Core Architectural Principles
- PRIMARY KEY and UNIQUE enforce entity integrity and business uniqueness at the engine layer, rejecting violating inserts instantly.
- FOREIGN KEY enforces referential integrity; the ON DELETE clause chooses RESTRICT, CASCADE, or SET NULL semantics when a parent row disappears.
- CHECK constraints encode domain rules (total > 0), and disabling all constraints exposes silent corruption instead of errors.
Argue for constraints as the last line of defense: application validation can be bypassed by bugs or direct SQL, but the engine cannot. Mention that foreign key columns need explicit indexes, or cascading deletes and join performance degrade. Show you choose RESTRICT, CASCADE, or SET NULL deliberately per relationship rather than defaulting blindly.
Strict constraints guarantee clean data but reject rows your application must handle; lax ones keep writes flowing and corrupt data silently.