lesson 61 Database engineering · advanced

Constraints protect the data contract

Use constraints to reject data that breaks a rule.

the idea

PRIMARY KEY identifies rows; NOT NULL requires a value; UNIQUE prevents duplicate values; CHECK enforces a condition. SQLite CHECK passes NULL unless you also use NOT NULL. Foreign-key enforcement needs PRAGMA foreign_keys=ON; the lab enables it.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

CREATE TABLE inventory(sku TEXT PRIMARY KEY NOT NULL, quantity INTEGER NOT NULL CHECK(quantity>=0));
INSERT INTO inventory VALUES('A',2);
SELECT sku,quantity
FROM inventory;

Watch for: These output exercises are followed by engine-level validation of the requested constraints.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   See data that constraints will protect. Constraints reject invalid writes. Think about NOT NULL, PRIMARY KEY, and CHECK before creating inventory.

   step-1.sql Run

   sql›

   SELECT id, name, price
   FROM lab_items
   ORDER BY id;

2. 2

   Declare constraints on create. PRIMARY KEY, NOT NULL, and CHECK(quantity >= 0) encode the data contract at write time.

   step-2.sql Run

   sql›

   CREATE TABLE inventory(sku TEXT PRIMARY KEY NOT NULL, quantity INTEGER NOT NULL CHECK(quantity>=0));
   INSERT INTO inventory VALUES('A',2);
   SELECT sku,quantity
   FROM inventory;