Part 5 of 5 · 3 min read
Exercises
Easy
Easy 1: Name the right constraint (15 minutes)
Name the correct constraint for: "every order must reference a real customer," "an employee's badge number must never repeat."
Done when: FOREIGN KEY and UNIQUE are correctly matched.
Hint
"Must reference a real customer" is a foreign key; "must never repeat" is UNIQUE.
Easy 2: Write a CHECK (15 minutes)
Write a CHECK constraint on orders.placed_on requiring it never be in the future relative to the current date.
Done when: a correct CHECK (placed_on <= CURRENT_DATE) is given.
Hint
ALTER TABLE orders ADD CONSTRAINT chk_not_future CHECK (placed_on <= CURRENT_DATE);
Easy 3: Predict the rejection (15 minutes)
State what happens to an INSERT into books with price = -50, given the CHECK constraint from part 2.
Done when: the insert is correctly stated as rejected.
Hint
The insert is rejected, since -50 violates CHECK (price >= 0).
Medium
Medium 1: UNIQUE on more than one column (25 minutes)
Write a UNIQUE constraint on order_items ensuring the same book cannot appear twice within the same order, as an alternative to a composite primary key.
Done when: a correct UNIQUE (order_id, book_id) constraint is given.
Hint
ALTER TABLE order_items ADD CONSTRAINT uq_order_book UNIQUE (order_id, book_id);
Medium 2: CHECK across two columns (25 minutes)
Write a CHECK constraint on orders requiring a delivered_on date, if present, to be on or after placed_on.
Done when: a correct CHECK (delivered_on IS NULL OR delivered_on >= placed_on) is given.
Hint
ALTER TABLE orders ADD CONSTRAINT chk_delivery_after_placed CHECK (delivered_on IS NULL OR delivered_on >= placed_on);
Medium 3: Why UNIQUE isn't always PRIMARY KEY (25 minutes)
Explain why a customer's email might be declared UNIQUE rather than made the primary key, even though it is also unique.
Done when: the explanation correctly notes that an email can change, while a stable surrogate key like customer_id should not, making it the better long-term identifier.
Hint
An email address can legitimately change over a customer's lifetime, while a surrogate key used by every foreign key elsewhere should stay fixed forever.
Hard
Hard 1: Constraint versus application check, concretely (30 minutes)
Describe a realistic scenario where an application-layer check for "price cannot be negative" gets bypassed, while a database-level CHECK constraint would not.
Done when: a concrete scenario is described, such as a second script or a direct database tool bypassing the application's own validation code entirely.
Hint
A direct database administration tool, or a separate maintenance script, bypassing the application entirely, would skip any check written only in the application's code, but not a database-level constraint.
Hard 2: A constraint that needs more than one table (35 minutes)
Explain why "a customer cannot place more than 10 orders per day" cannot be expressed as a CHECK constraint on orders alone, and name the mechanism from the previous module that could enforce it instead.
Done when: the explanation correctly notes CHECK only sees the row being written, and correctly names a trigger as the mechanism that could query other rows to enforce this.
Hint
CHECK constraints evaluate only the single row being written, with no visibility into how many other orders the same customer placed that day; a trigger can run a query counting them.
Hard 3: Loosening then tightening a constraint safely (40 minutes)
A discount column currently has CHECK (discount BETWEEN 0 AND 100). The business now wants to allow a temporary promotional discount up to 150 for a specific campaign, then revert to the old rule afterward. Describe a safe way to make and later undo this change.
Done when: a safe approach is described, such as dropping and recreating the constraint with the new bound, with a plan to revert it back to the original bound after the campaign ends.
Hint
Drop the existing CHECK constraint and add a new one with the higher bound for the campaign period, then drop that one and restore the original bound once the campaign ends.