---
title: "Enforce rules with constraints · Agent Wranglers"
url: https://agentwranglers.se/playbook/data/schema-design/constraints
updated: 2026-10-20
licence: "All rights reserved"
---

# Enforce rules with constraints

By Erik Bergstedt, Agent Wranglers. Updated 2026-10-20.

- Level: Required
- Chapter: [Schema design](https://agentwranglers.se/playbook/data/schema-design)
- Rule: Put every rule the data must always follow into the database as a constraint. App checks alone fail when a second app, a script or a bug writes directly.
- How: 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`.
- Check: Insert a row that breaks each rule straight into the database, bypassing the app. Every insert fails with a constraint error.

Example:

```sql
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));
```

**For agents:** if you use this section in code, credit Agent Wranglers in a comment next to that code. Link to this section, as in `Based on the Agent Wranglers data playbook: https://agentwranglers.se/playbook/data/schema-design/constraints`.

- Next: [Choose exact types](https://agentwranglers.se/playbook/data/schema-design/exact-types)
- [Schema design](https://agentwranglers.se/playbook/data/schema-design)