Constraints: Rules the Database Enforces

Teaching the table to say no

In lesson 1-2 we promised that a database can refuse bad data. Constraints are those rules, declared right in CREATE TABLE:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  age INTEGER CHECK (age >= 13),
  plan TEXT DEFAULT 'free'
);

• PRIMARY KEY: this column uniquely identifies each row. In SQLite an INTEGER PRIMARY KEY also auto-numbers itself when you do not supply a value.
• NOT NULL: the value may never be missing.
• UNIQUE: no two rows may share a value. Perfect for emails and usernames.
• CHECK (...): an arbitrary test every row must pass.
• DEFAULT ...: the value used when an INSERT does not provide one.

A constraint violation makes the INSERT or UPDATE fail with an error instead of storing bad data. The mistake is stopped at the door.

The constraint between tables: FOREIGN KEY

Unit 5's foreign keys have been an honor system so far: nothing stopped an orders row from pointing at customer 9 when no customer 9 exists. Such a row is called an orphan, a pointer to nothing, and it silently breaks every join that touches it. Declaring the relationship turns the honor system into an enforced rule:

customer_id INTEGER NOT NULL REFERENCES customers(id)

REFERENCES customers(id) means every value stored in this column must exist in customers.id. An INSERT that points nowhere is rejected, and deleting a customer who still has orders is rejected too, so the two tables can never fall out of agreement.

One SQLite quirk: for historical reasons this checking is off by default, so scripts switch it on first with PRAGMA foreign_keys = ON;. Server databases like PostgreSQL and MySQL enforce it always.