Constraints: the Database Says No

The bouncer you met at INSERT — here's his rulebook. Back in Chapter 5, every new row got auditioned at the door: types checked, keys checked, pointers resolved. This page names the rules that audition enforces. They are called constraints: rules declared in the schema itself, alongside the columns, so the database can refuse any row that breaks them.

Constraints are the difference between a database that hopes its data is right and one that guarantees it. The previous page showed that a table's shape can make contradictions impossible to store; constraints finish the job by making individual bad values impossible to store. Together they are how Chapter 1's word "trustworthy" stops being a wish and becomes a property.

The Roster

Four constraints join the two you already know. NOT NULL says a value must be present: a film without a title is not a film, so title refuses to be empty-handed. UNIQUE says no twins: two customers may share a name, but not an email address, so customers.email carries UNIQUE. DEFAULT fills a value in when the writer doesn't supply one: leave out booked_at and the database stamps the current moment itself.

The fourth is the most general. CHECK attaches an arbitrary true-or-false test to a column, in exactly the WHERE-clause style you already read: CHECK (runtime_min > 0) means no zero-minute or negative-minute films, ever, no matter who does the inserting. If a rule about a single row can be written as a condition, CHECK can enforce it.

And here is the reframe that completes the family: PRIMARY KEY and FOREIGN KEY were constraints all along. PRIMARY KEY is a bundle — present, unique, the stable name of the row. REFERENCES is the rule "this value must exist as a key over there". You have been living with constraints since Chapter 2; this page just introduces the rest of the household.

Declared Once, Enforced Forever

The location of these rules is the whole point. A constraint lives with the table, not inside any program that writes to the table — and that distinction earns its keep the day the second program shows up. The Marquee's website inserts bookings. So does the CSV import from Chapter 5. So does Lora, typing at eleven at night. Three writers, three different chances to forget a rule.

Except nobody gets to forget, because the rule is not theirs to remember. The website's carefully validated form, the bulk import's 800 rows, and Lora's tired typo all pass through the same door and meet the same wall. Declare runtime_min > 0 once, and it holds against every writer that will ever exist, including the ones nobody has written yet.

The Marquee's Guardrails, Annotated

Here is the schema you have followed all book, now printed in its final, fully guarded form — the version this course considers canonical from here on. Read the comments; each one is a rule with a reason.

CREATE TABLE films (
  film_id     INTEGER PRIMARY KEY,
  title       TEXT NOT NULL,               -- a film without a title is not a film
  runtime_min INTEGER NOT NULL
              CHECK (runtime_min > 0),     -- no zero-minute films, ever
  rating      TEXT NOT NULL
);

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  email       TEXT UNIQUE,                 -- no twins; may be absent (some regulars never gave one)
  joined_on   DATE NOT NULL
              DEFAULT CURRENT_DATE         -- stamped automatically at signup
);

CREATE TABLE screenings (
  screening_id INTEGER PRIMARY KEY,
  film_id      INTEGER NOT NULL
               REFERENCES films(film_id),  -- a screening of a film that exists
  screen       INTEGER NOT NULL
               CHECK (screen IN (1, 2)),   -- the Marquee has exactly two rooms
  starts_at    TIMESTAMP NOT NULL,
  price        DECIMAL(6,2) NOT NULL
               CHECK (price >= 0)          -- free previews allowed; negative prices are not
);

CREATE TABLE bookings (
  booking_id   INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL
               REFERENCES customers(customer_id),
  screening_id INTEGER NOT NULL
               REFERENCES screenings(screening_id),
  seat         TEXT NOT NULL,
  booked_at    TIMESTAMP NOT NULL
               DEFAULT CURRENT_TIMESTAMP,  -- the moment of booking, stamped by the database
  UNIQUE (screening_id, seat)              -- this line is why two people can't buy G7
);

Walk the highlights in words. Every fact a row cannot sensibly lack is NOT NULL. The email is UNIQUE but deliberately allowed to be absent — Chapter 2's honest NULL, "we don't know", for the regulars who never gave one. The two CHECKs guard facts of physics and arithmetic: films have positive runtimes, the building has two screens, prices don't go below zero. The DEFAULTs stamp dates and times so no writer has to remember to.

And then there is the last line of bookings. UNIQUE (screening_id, seat) is uniqueness over a combination: the same seat may appear in many screenings, and a screening holds many seats, but the pair may occur only once. Read it as a sentence and it says: one seat, one screening, one buyer. That single line is the end of Chapter 1's Saturday disaster — and in Chapter 7 you will watch it hold the door against two simultaneous buyers in real time.

When the Database Says No, Someone's Day Was Saved

Watch the guardrails work. A typo tries to store a negative runtime, and a second buyer tries to take Anna's seat:

INSERT INTO films (title, runtime_min, rating)
VALUES ('The Glass Coast', -112, 'PG');
-- refused: CHECK (runtime_min > 0) fails

INSERT INTO bookings (customer_id, screening_id, seat)
VALUES (317, 88, 'G7');
-- refused: UNIQUE (screening_id, seat) — G7 is already booked for screening 88

Neither refusal is the database being difficult. The first is a typo caught at the door instead of surfacing months later in a report where The Glass Coast has an impossible runtime. The second is a double-sale that never happened — no apology, no free popcorn, no one even noticed. The cost of a constraint is feeling it at INSERT time; the payoff is never once hunting corrupted data at 2 a.m.

One honest boundary before the quiz. CHECK can test almost anything, which tempts people to encode everything. Resist it: constraints should guard invariants — things that are wrong in every possible world, like negative prices — not business policies that change monthly, like "no more than four tickets per customer during festival week". Policies live in the apps, where they can change without touching the schema. Invariants live in the schema, where nothing can dodge them.