Lesson 51 of 61

5 mins read

SQL CHECK & DEFAULT: Business Rules at DB Layer

In Plain English

`CHECK` constraints evaluate arbitrary boolean expressions on inserted or updated row values, rejecting values outside allowable domain ranges (e.g. positive prices, valid discount percentages, acceptable enum statuses). `DEFAULT` supplies automatic values whenever an insertion omits the column, standardizing initial states.

The Mental Model

"`CHECK` is a height requirement sign at a roller coaster: anyone shorter than 48 inches is stopped at the gate; `DEFAULT` is the automatic admission wristband given to everyone who enters."

Deep Dive: How It Works

CHECK Expressions: Can reference multiple columns in the same row (`CHECK(end_date >= start_date)`).

Emulating Enums: `CHECK(status IN ('draft', 'published', 'archived'))` provides lightweight enum validation without proprietary type creation.

Dynamic Defaults: `DEFAULT CURRENT_TIMESTAMP` or `DEFAULT (datetime('now'))` records insertion time automatically.

Syntax Blueprint

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  status TEXT DEFAULT 'active' CHECK(status IN ('active', 'closed')),
  capacity INTEGER CHECK(capacity > 0),
  discount REAL DEFAULT 0.0 CHECK(discount >= 0.0 AND discount <= 1.0)
);

Applies boolean range assertions and default values to columns.

Core Rules to Remember

Domain Validation: Guarantees numbers remain in range and strings match allowable status lists.

Multi-Column Invariants: Enforces temporal logic such as `end_date >= start_date`.

Automatic Fallbacks: Supplies standard initial values when columns are omitted in INSERTs.

Common Beginner Traps & How to Fix Them

Mistake:Using subqueries inside a `CHECK` constraint.

Why it happens: Standard SQL forbids subqueries inside CHECK expressions (CHECK only evaluates the current row).

How to fix: Use foreign keys or database triggers for cross-table validation.

Pro Tip: Combine `CHECK` with `LOWER()` to ensure case-insensitive enum validation: `CHECK(LOWER(status) IN ('active', 'archived'))`.

Live Interactive Example

Hit Run Code to see it live

Enforcing Business Domain Rules with CHECK and DEFAULT

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: The default status CONFIRMED was applied and nights/rates passed validation.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Apply CHECK and DEFAULT to Products

Create a table `products_v2` with `id INTEGER PRIMARY KEY`, `name TEXT NOT NULL`, `rating REAL DEFAULT 5.0 CHECK(rating >= 1.0 AND rating <= 5.0)`.
Insert a product ("Laptop") without specifying rating, then select all columns.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.