10. Constraints: rules the data must follow

A constraint is a rule attached to a column or table. The database enforces it on every insert and update, and rejects anything that breaks it. Constraints are how you stop bad data from ever getting in — far more reliable than hoping the app checks.

The core constraints

• NOT NULL — this column must always have a value.
• DEFAULT — a value to use when none is given.
• UNIQUE — no two rows may share this value.
• PRIMARY KEY — the row's unique identifier: UNIQUE and NOT NULL, one per table.
• CHECK — a condition every row must satisfy.

Here they are together:

CREATE TABLE account (
  id       INTEGER     PRIMARY KEY,
  email    VARCHAR     NOT NULL UNIQUE,
  balance  DECIMAL(10,2) NOT NULL DEFAULT 0,
  status   VARCHAR     NOT NULL DEFAULT 'active',
  CHECK (balance >= 0)
);

INSERT INTO account (id, email) VALUES (1, 'a@x.com');
INSERT INTO account (id, email, balance) VALUES (2, 'b@x.com', 50.00);

SELECT * FROM account ORDER BY id;

The first insert leaves balance and status out — their DEFAULTs fill in (0 and 'active'). That's the payoff: valid rows stay short, and the table guarantees the rest.

Constraints reject bad data

Given that account table, each of these inserts is refused with an error — the database names the exact constraint you broke, and the bad row never gets in:

INSERT INTO account (id, email) VALUES (3, NULL);        -- email is NOT NULL
INSERT INTO account (id, email) VALUES (1, 'c@x.com');   -- id 1 already exists (PRIMARY KEY)
INSERT INTO account (id, email, balance) VALUES (4, 'd@x.com', -5);  -- fails CHECK (balance >= 0)

That rejection is the whole point: a constraint turns "we hope the app validates this" into "the database guarantees it."

Try it

Create a table grade with id as INTEGER PRIMARY KEY and score as INTEGER NOT NULL with a CHECK that score is between 0 and 100. Insert (1, 90) and (2, 75), then select both ordered by score.

-- create grade with the constraints, insert two valid rows, select ordered by score

CREATE TABLE grade (
  id    INTEGER PRIMARY KEY,
  score INTEGER NOT NULL CHECK (score BETWEEN 0 AND 100)
);
INSERT INTO grade (id, score) VALUES (1, 90), (2, 75);
SELECT * FROM grade ORDER BY score;