beginnerintermediateadvanced

Django · Section 28

Database Constraints

Level: advanced
Read: 16 min
Concepts: 2

Beyond UniqueConstraint/CheckConstraint/conditional uniqueness (already covered in §10's Meta.constraints and Meta.indexes) — functional indexes, which index an EXPRESSION's result rather than a raw field (needed for case-insensitive __iexact lookups and similar computed-value queries), and db_default, a genuinely database-computed default distinct from default=, applying to every write path including ones that bypass Django's ORM entirely. Both concepts tie back to the roadmap's own explicit principle: application validation (clean(), forms) improves user experience with a fast, friendly error, while database constraints protect data integrity against every write path — the two are complementary, not interchangeable.

This section

Django - Database Constraints | Unique Constraint | Check ConstraintBugBytes

What is true here

A functional index (Index(Lower("field"), name="...")) indexes an expression's result, not the raw column — it only accelerates a query using that exact same expression.
name= is required for any expression-based index — unlike a plain field index, Django cannot auto-generate a sensible name for an arbitrary expression.
db_default computes a value in the database itself (literals/database functions only, never a field reference) — it applies to every write path, unlike default=, which only runs through Django's own ORM code.
An unsaved instance with only db_default= (no default=) reads back a DatabaseDefault placeholder for that field — refresh_from_db() after save() is required to see the real value.
Application validation (clean()) improves user experience with a fast, friendly, field-specific error, but only runs on code paths that call it — database constraints are the only guarantee that holds against every write path, including ones application code never touches.

What you will be able to do

Add a functional index to accelerate a computed-value query like __iexact
Use db_default for a genuinely database-level default, and know when refresh_from_db() is needed
Apply the roadmap's own principle — use both application validation AND database constraints, deliberately

Functional indexes

Indexing an expression's result, not just a raw field — needed for case-insensitive and other computed-value queries.

Functional indexes

coreadvanced

Index(Lower("title"), name="...") indexes the RESULT of an expression, not the raw column — the database precomputes and indexes Lower(title) for every row, so a query filtering on Lower(title) (or __iexact) can actually use the index instead of computing the expression fresh for every row on every query.

Think of it as

A plain models.Index(fields=["title"]) speeds up queries that filter/sort on title AS STORED — but a query like filter(title__iexact="hello") has to lowercase EVERY row's title to compare, and no plain index on the raw column helps with that, since the index is built from the raw, not-lowercased values. A functional index flips this: it indexes Lower("title") directly, so the database already has the lowercased value precomputed and indexed — the moment a query's WHERE clause matches that same expression, the index becomes usable again, exactly like an ordinary column index would be.

python

class Meta:
    indexes = [
        models.Index(Lower("field_name"), name="required_name"),
    ]

What we're doing: Speed up case-insensitive email lookups (a very common auth pattern) with a functional index on the lowercased email.

accounts/models.pypython

from django.db.models.functions import Lower

class User(models.Model):
    email = models.EmailField()

    class Meta:
        indexes = [
            models.Index(Lower("email"), name="email_lower_idx"),
        ]

6: Lower("email") as a positional argument (not fields=["email"]) is what makes this a functional index — the database indexes the LOWERCASED value of every row's email.
7: name="email_lower_idx" is required here — a functional index cannot auto-generate a name the way a plain field index can.

Why this works: Without this index, User.objects.filter(email__iexact="Person@Example.com") has to lowercase every single row's email before comparing — with a large user table, that means a full table scan on every login attempt; the functional index lets the database use an actual index lookup instead, since the lowercased value is already precomputed and indexed.

Adding a plain field index and expecting it to accelerate a case-insensitive (__iexact) query

Wrong

python

class Meta:
    indexes = [models.Index(fields=["email"], name="email_idx")]   # plain, not functional

User.objects.filter(email__iexact="Person@Example.com")   # still a sequential scan

Better

python

class Meta:
    indexes = [models.Index(Lower("email"), name="email_lower_idx")]

User.objects.filter(email__iexact="Person@Example.com")   # now index-backed

What you see: EXPLAIN on the __iexact query still shows a sequential scan even with a plain index on the same column already in place — the index technically exists but the planner has no way to use it for this comparison.

Why: A plain index is built from the column's RAW stored values — a case-insensitive comparison needs to transform both sides (the stored value and the search term) before comparing, and a plain index has no transformed values to match against. Only a functional index built on the SAME transformation (Lower()) gives the planner something it can actually use for an __iexact lookup.

A functional index precomputes the expression, not the raw column

every row's email

"Person@Example.com"

Lower(email) precomputed

stored in the index itself

filter(email__iexact=...)

matches the same expression — index used

Plain index vs functional index

Plain index vs functional index
Index	Accelerates
Index(fields=["title"])	filter(title="exact value") — the raw stored value
Index(Lower("title"), name="...")	filter(title__iexact="value") — a case-insensitive match
Index(F("height") * F("weight"), name="...")	a query filtering/ordering on that exact computed expression

Together

python

from django.db.models.functions import Lower

class Meta:
    indexes = [
        models.Index(Lower("email"), name="email_lower_idx"),
    ]

# now index-backed:
User.objects.filter(email__iexact="Person@Example.com")

Remember: A functional index (Index(Lower("field"), name="...")) indexes an EXPRESSION's result, not the raw column — it only accelerates a query using that exact same expression (e.g. __iexact needs Lower(), not a plain field index). name= is required for any expression-based index.

db_default, and using both validation layers

A genuinely database-computed default, and the roadmap's own principle for why application validation and database constraints are complementary, not interchangeable.

db_default, and why both layers of validation matter

coreadvanced

db_default=Now() (or another database function/literal) sets a default computed BY THE DATABASE itself, applied even to rows inserted outside the ORM entirely — distinct from default=, which Django computes in Python and only applies through the ORM. On an unsaved instance, a db_default-only field reads back a placeholder DatabaseDefault object, not the real value — refresh_from_db() is needed to see what the database actually computed. The roadmap's own principle: application validation (clean(), form validation) improves user experience with a fast, friendly error; database constraints protect data integrity against every write path, including ones application code never touches.

Think of it as

default= only ever runs when Django's own Python code constructs an instance — a raw SQL INSERT, an external tool writing to the same table, or a migration's RunSQL step never sees it at all. db_default= moves that same default INTO the database schema itself, so it applies universally, regardless of what wrote the row. This is really the same "validation layer vs. constraint layer" distinction the roadmap's own principle names directly: application-level checks (clean(), default=) are fast and give a friendly error message, but they only ever run on paths that go through Django's Python code — database-level rules (CheckConstraint, db_default, foreign keys) are slower to author and give a less friendly raw database error, but they are the only guarantee that holds no matter what actually wrote the row.

python

from django.db.models.functions import Now
created = models.DateTimeField(db_default=Now())

What we're doing: Set a genuinely database-level default timestamp that applies even to rows inserted by a raw SQL migration or an external tool, not just Django's own ORM writes.

articles/models.pypython

from django.db.models.functions import Now

class Article(models.Model):
    title = models.CharField(max_length=200)
    created = models.DateTimeField(db_default=Now())

4: db_default=Now() means the DATABASE itself fills in the current timestamp on insert — a raw SQL INSERT INTO articles (title) VALUES (...) still gets a correct created value, since Django's ORM was never involved in computing it.

Why this works: A plain default=timezone.now works fine for the common case (creating an Article through Django's own code) but does nothing for a data migration's RunSQL step, a bulk import tool, or any other system writing directly to the articles table — db_default moves the same guarantee into the schema itself, so it holds regardless of what wrote the row.

Reading a db_default field's value immediately after construction, before it has actually been saved

Wrong

python

article = Article(title="Hello")
print(article.created)   # a DatabaseDefault placeholder object, not a real datetime

Better

python

article = Article(title="Hello")
article.save()
article.refresh_from_db()
print(article.created)   # the real, database-computed datetime

What you see: Code that reads a db_default-only field right after constructing (but before saving) an instance gets a strange DatabaseDefault object instead of the expected value, breaking any comparison or formatting logic that assumed a real datetime.

Why: db_default is computed by the DATABASE at insert time — Django has no way to know what value the database will actually compute until the row is genuinely written and re-read, so an unsaved instance can only hold a placeholder for that field. refresh_from_db() (after save()) is what actually pulls the real, database-computed value back into Python.

Two layers of validation — only one holds on every write path

clean() / form validation

fast, friendly — but only runs through Django's own code

CheckConstraint / db_default

less friendly, but enforced on EVERY write — raw SQL, bulk_create, migrations

default= vs db_default=

default= vs db_default=
Aspect	default=	db_default=
Computed by	Python, via Django's ORM	the database itself
Applies to	only writes going through Django's ORM	every write, including raw SQL / external tools
Can reference other fields?	yes (a callable can do anything)	no — literals and database functions/expressions only
Value on an unsaved instance	the real computed value immediately	a DatabaseDefault placeholder, until refresh_from_db()

Together

python

from django.db.models.functions import Now

class Post(models.Model):
    published_at = models.DateTimeField(default=timezone.now, db_default=Now())
    # default= used by Django's own ORM code; db_default= used by anything else writing this table

Remember: db_default computes a value in the database itself (literals/DB functions only, no field references) — applies to every write path, unlike default=, which only runs through Django's own ORM. An unsaved db_default-only field reads back a placeholder until refresh_from_db(). Application validation (clean()) improves UX; database constraints protect data integrity — use both, since only the constraint holds against every write path.