Indexes and Constraints

Peewee can create indexes on single or multiple columns, optionally including aUNIQUE constraint. Peewee also supports user-defined constraints on bothmodels and fields.

Single-column indexes and constraints

Single column indexes are defined using field initialization parameters. Thefollowing example adds a unique index on the username field, and a normalindex on the email field:

class User(Model):
    username = CharField(unique=True)
    email = CharField(index=True)

To add a user-defined constraint on a column, you can pass it in using theconstraints parameter. You may wish to specify a default value as part ofthe schema, or add a CHECK constraint, for example:

class Product(Model):
    name = CharField(unique=True)
    price = DecimalField(constraints=[Check('price < 10000')])
    created = DateTimeField(
        constraints=[SQL("DEFAULT (datetime('now'))")])

Multi-column indexes

Multi-column indexes may be defined as Meta attributes using a nested tuple.Each database index is a 2-tuple, the first part of which is a tuple of thenames of the fields, the second part a boolean indicating whether the indexshould be unique.

class Transaction(Model):
    from_acct = CharField()
    to_acct = CharField()
    amount = DecimalField()
    date = DateTimeField()
 
    class Meta:
        indexes = (
            # create a unique on from/to/date
            (('from_acct', 'to_acct', 'date'), True),
 
            # create a non-unique on from/to
            (('from_acct', 'to_acct'), False),
        )

Note

Remember to add a trailing comma if your tuple of indexes contains only one item:

class Meta:
    indexes = (
        (('first_name', 'last_name'), True),  # Note the trailing comma!
    )

Advanced Index Creation

Peewee supports a more structured API for declaring indexes on a model usingthe Model.add_index() method or by directly using theModelIndex helper class.

Examples:

class Article(Model):
    name = TextField()
    timestamp = TimestampField()
    status = IntegerField()
    flags = IntegerField()
 
# Add an index on "name" and "timestamp" columns.
Article.add_index(Article.name, Article.timestamp)
 
# Add a partial index on name and timestamp where status = 1.
Article.add_index(Article.name, Article.timestamp,
                  where=(Article.status == 1))
 
# Create a unique index on timestamp desc, status & 4.
idx = Article.index(
    Article.timestamp.desc(),
    Article.flags.bin_and(4),
    unique=True)
Article.add_index(idx)

Warning

SQLite does not support parameterized CREATE INDEX queries. This meansthat when using SQLite to create an index that involves an expression orscalar value, you will need to declare the index using the SQLhelper:

# SQLite does not support parameterized CREATE INDEX queries, so
# we declare it manually.
Article.add_index(SQL('CREATE INDEX ...'))

See add_index() for details.

For more information, see:

• Model.add_index()
• Model.index()
• ModelIndex
• Index

Table constraints

Peewee allows you to add arbitrary constraints to your Model, thatwill be part of the table definition when the schema is created.

For instance, suppose you have a people table with a composite primary key oftwo columns, the person’s first and last name. You wish to have another tablerelate to the people table, and to do this, you will need to define a foreignkey constraint:

class Person(Model):
    first = CharField()
    last = CharField()
 
    class Meta:
        primary_key = CompositeKey('first', 'last')
 
class Pet(Model):
    owner_first = CharField()
    owner_last = CharField()
    pet_name = CharField()
 
    class Meta:
        constraints = [SQL('FOREIGN KEY(owner_first, owner_last) '
                           'REFERENCES person(first, last)')]

You can also implement CHECK constraints at the table level:

class Product(Model):
    name = CharField(unique=True)
    price = DecimalField()
 
    class Meta:
        constraints = [Check('price < 10000')]