SQL SQL In Depth
0%
Guided SQL & Engine Coding Lab

E-Commerce Schema with Referential Integrity & Upsert Pipeline

An in-memory DDL constraint validator and idempotent UPSERT engine enforcing PRIMARY KEY, UNIQUE, NOT NULL, CHECK predicates, and cascading FOREIGN KEY referential integrity actions.

~35 min Intermediate Lessons: Hands-On Build: E-Commerce Schema with Referential Integrity & Upsert Pipeline, CREATE TABLE, ALTER TABLE, DROP, TRUNCATE vs DELETE & Catalogs, Primary Keys: Composite Keys, IDENTITY & UUIDv4 vs UUIDv7 Locality, Foreign Keys, Parent/Child Tables & Referential Actions (CASCADE / RESTRICT), Domain Integrity: CHECK, UNIQUE, NOT NULL, DEFAULT & Generated Columns, Single & Multi-Row INSERT, INSERT ... SELECT, COPY & RETURNING
Guided Algorithm Coding Lab 0/4 Steps Completed

Code & Verify SQL & Storage Engine Invariants in the Browser Workbench

Complete 4 progressive algorithmic steps with guided hints in C++20 (WebAssembly), TypeScript, or JavaScript against automated unit test assertions — zero local setup required.

Architectural Overview

An in-memory DDL constraint validator and idempotent UPSERT engine enforcing PRIMARY KEY, UNIQUE, NOT NULL, CHECK predicates, and cascading FOREIGN KEY referential integrity actions.

In production relational and analytical database systems—spanning PostgreSQL, SQLite, MySQL/InnoDB, and DuckDB—understanding how query clauses, schema constraints, index structures, and storage engines execute under the hood separates surface-level query authors from staff-level database engineers. This capstone build lab guides you through implementing E-Commerce Schema with Referential Integrity & Upsert Pipeline from first principles with deterministic self-test invariants.

Guided Implementation Walkthrough (4 Progressive Steps)

Step 1: Table Registration & NOT NULL / CHECK Validation

  • Core Database Concept: Enforcing Domain Integrity Before Tuple Insertion
  • Target Module: index.js (SchemaConstraintEngine.validateRow)
  • Engineering Specification: Implement validateRow(tableName, row) to verify that every column in schema.notNull is non-null and every predicate in schema.checks returns true.
  • Implementation Hint: Check notNullCols for null/undefined and run every checkFn(row), throwing an Error on failure.
  • Verification Invariant: deterministic assertion verified in test.js step 1

Step 2: Foreign Key Parent Lookup Validation

  • Core Database Concept: Referential Integrity Enforcement on Child Insert
  • Target Module: index.js (SchemaConstraintEngine.validateForeignKeys)
  • Engineering Specification: Implement validateForeignKeys(tableName, row) so that any non-null foreign key attribute in row references an existing parent key in the target parent relation.
  • Implementation Hint: If row[fk.col] is non-null, verify parentTable has a row with parentRow[fk.refCol] === row[fk.col].
  • Verification Invariant: deterministic assertion verified in test.js step 2

Step 3: Atomic INSERT ... ON CONFLICT DO UPDATE (UPSERT)

  • Core Database Concept: Idempotent Conflict Resolution on Primary or Unique Keys
  • Target Module: index.js (SchemaConstraintEngine.upsert)
  • Engineering Specification: Implement upsert(tableName, row, conflictCol, updateFn) to atomically detect conflicts on conflictCol, merge attributes via updateFn(existing, incoming), validate constraints, and return the resulting tuple.
  • Implementation Hint: Find existing row matching conflictCol; if found, apply updateFn(existing, row) and revalidate; else insert.
  • Verification Invariant: deterministic assertion verified in test.js step 3

Step 4: Cascading Foreign Key Delete Engine (RESTRICT / CASCADE / SET NULL)

  • Core Database Concept: Graph Propagation of Referential Actions on Parent Deletion
  • Target Module: index.js (SchemaConstraintEngine.deleteByPk)
  • Engineering Specification: Implement deleteByPk(tableName, pkVal) to enforce child foreign-key actions (RESTRICT, CASCADE, SET NULL) before removing the parent tuple.
  • Implementation Hint: Inspect all child tables referencing tableName: throw on RESTRICT, delete child on CASCADE, or null column on SET NULL.
  • Verification Invariant: deterministic assertion verified in test.js step 4

Relational & Storage Engine Guarantees

  • Curriculum Domain: DDL & Schema Constraints
  • Difficulty & Time Budget: Intermediate (~35 minutes)
  • Key Architectural Highlights:
    • Strict NOT NULL, UNIQUE, and custom CHECK constraint validation prior to tuple commit
    • Referential integrity enforcement supporting ON DELETE RESTRICT, CASCADE, and SET NULL
    • Idempotent INSERT ... ON CONFLICT (key) DO UPDATE SET atomic upsert resolution
  • Core Primitives & Tags: DDL Constraints, Foreign Keys, Cascading Deletes, Upsert Pipeline, Referential Integrity

Related Curriculum Units

Production Database Context & Verification

This from-scratch relational engine component is engineered with zero external dependencies and deterministic verification harnesses. Every internal database invariant—from three-valued logic (TRUE / FALSE / UNKNOWN), referential integrity, and fan-out-free join pre-aggregation to B+Tree page splits, MVCC snapshot visibility (xmin / xmax), slotted-page compaction, and ARIES write-ahead log crash recovery—is asserted directly in test.js. Use the interactive browser workbench above to complete the 4 guided steps in Guided Lab mode or inspect and benchmark the complete reference architecture in Full Solution mode.