Python SQL CHECK & DEFAULT: Business Rules at DB Layer
`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.
"`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
Common Beginner Traps & How to Fix Them
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.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
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.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.