Constraints — letting the database enforce correctness

Application code can forget to validate something. A constraint is a rule the database itself enforces on every write, from every source, with no way around it — the last line of defense against bad data.

Beginner

4 min read

The problem constraints solve: application code isn't the only writer

# Your Django app validates carefully...
def create_user(email):
    if not email:
        raise ValueError("email required")
    User.objects.create(email=email)

-- ...but a script, an admin using a database GUI, or a different service
-- entirely can write directly to the table, bypassing this check completely:
INSERT INTO users (email) VALUES (NULL);

Validation written in application code (Django's form validation, from its own lesson, or a plain if check) only runs when that specific code path is actually used — a raw SQL script, a database admin tool, a data migration, or a completely different service writing to the same table all bypass it entirely. A constraint is a rule enforced by the database itself, on every single write, regardless of what wrote it — the last line of defense that doesn't depend on every possible writer remembering to validate correctly.

NOT NULL: a column that can never be empty

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);
 
INSERT INTO users (id, email) VALUES (1, NULL);   -- REJECTED: violates NOT NULL

NOT NULL on a column means the database itself refuses any INSERT or UPDATE that would leave that column empty — not "the application should check this," but "this write cannot happen at all." Columns without NOT NULL are nullable by default in most databases, which is worth being deliberate about: a column that conceptually should always have a value (an order's total, a user's email) should almost always be NOT NULL, precisely so that a missing value is caught immediately at write time, not discovered later as a confusing None/null surfacing somewhere in application code.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);
 
INSERT INTO users (email) VALUES ('ada@example.com');
INSERT INTO users (email) VALUES ('ada@example.com');   -- REJECTED: violates UNIQUE

UNIQUE guarantees no two rows in the table can have the same value in that column — attempting to insert (or update to) a duplicate is rejected outright. This is the actual mechanism behind "email already registered" errors: rather than the application first querying "does this email already exist?" and then inserting (a race condition — two simultaneous signups could both pass the check before either insert completes), the database's UNIQUE constraint catches the duplicate atomically and reliably, no matter how the check-then-insert race plays out.

FOREIGN KEY: a reference that has to point somewhere real

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    amount INTEGER
);
 
INSERT INTO orders (customer_id, amount) VALUES (9999, 50);   -- REJECTED if
-- customer 9999 doesn't exist — the foreign key constraint enforces this

A FOREIGN KEY (declared here with REFERENCES customers(id)) guarantees that orders.customer_id can only ever hold a value that actually exists as an id in customers — it's the enforcement mechanism behind the joins lesson's foreign key relationships, making it structurally impossible to insert an order pointing at a customer that doesn't exist. Without this constraint, nothing stops an order from referencing customer_id = 9999 even if no such customer was ever created — exactly the kind of broken reference that silently drops rows from an INNER JOIN, covered in the joins lesson, and this constraint is what prevents that broken state from being created in the first place.

CHECK: an arbitrary rule on a column's value

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    amount INTEGER CHECK (amount > 0)
);
 
INSERT INTO orders (amount) VALUES (-50);   -- REJECTED: violates CHECK

CHECK enforces an arbitrary boolean condition on every row — amount > 0 here rejects any order with a zero or negative amount, a business rule that's arguably as fundamental to what a valid order even is as the column's data type. CHECK constraints let genuinely important invariants ("age must be non-negative," "a discount percentage must be between 0 and 100") live in the database schema itself, rather than depending entirely on every piece of code that ever writes to the table remembering to enforce them.

Why this matters even when your application already validates carefully

class Order(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE)
    amount = models.PositiveIntegerField()

Django's model fields (from the models lesson) — ForeignKey, PositiveIntegerField, unique=True — actually generate these exact SQL constraints underneath, at the database level, not just in Python. This is deliberate defense in depth: Django's form validation catches bad input early and produces a friendly error message, while the underlying database constraint catches anything that somehow gets past that — a bug, a bulk import script, a raw SQL migration — because the database itself refuses to store data that violates the rule, regardless of what tried to write it.