lesson 61 Database engineering · advanced

Constraints protect the data contract

Use constraints to reject data that breaks a rule.

the idea

Constraints are rules the database enforces on every write: NOT NULL forbids missing values, UNIQUE forbids duplicates, CHECK requires a condition, and FOREIGN KEY requires a matching parent row. A rejected write is the constraint working.

Dataset: Logistics

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Build a table with CHECK rules. Run this. Valid zone rows insert cleanly; values outside the CHECK range would be rejected—that is the database enforcing the rule on every write.the pattern

   example.sql Run

   sql›

   CREATE TABLE zone_codes (zone TEXT PRIMARY KEY, max_days INTEGER NOT NULL CHECK (max_days BETWEEN 0 AND 10));
   INSERT INTO zone_codes VALUES ('A', 1), ('B', 3);
   SELECT *
   FROM zone_codes;

2. 2

   See the parent service levels. service_levels is what shipments reference. These ids and codes are the parent keys your foreign keys must match.

   step-2.sql Run

   sql›

   SELECT service_level_id, service_code, promised_business_days
   FROM service_levels;

common mistake

A CHECK that allows NULL (weight_kg > 0) still lets a NULL weight in; add NOT NULL when the value is required.