Enforce rules with constraints

How to do it

Mark required columns NOT NULL. Add CHECK for allowed ranges and states, UNIQUE for natural identifiers such as email, and FOREIGN KEY for every reference. Make email uniqueness ignore case, with a unique index on lower(email) or a case-insensitive collation. SQLite leaves foreign keys off unless each connection runs PRAGMA foreign_keys = ON, though some drivers do it for you. MySQL enforces CHECK only from version 8.0.16. Document stores need a schema validator, such as MongoDB's $jsonSchema.

Example

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id),
  status      text   NOT NULL CHECK (status IN ('open', 'paid', 'shipped', 'cancelled')),
  total_cents bigint NOT NULL CHECK (total_cents >= 0),
  created_at  timestamptz NOT NULL DEFAULT now()
);
-- Ann@example.com and ann@example.com are one customer
CREATE UNIQUE INDEX customers_email_unique ON customers (lower(email));

How to check

Insert a row that breaks each rule straight into the database, bypassing the app. Every insert fails with a constraint error.