Models and Fields - Indexes and Constraints - 《Peewee 3.14.4 Documentation》

Indexes and Constraints

Indexes and Constraints

Peewee can create indexes on single or multiple columns, optionally including a UNIQUE constraint. Peewee also supports user-defined constraints on both models and fields.

Single-column indexes and constraints

Single column indexes are defined using field initialization parameters. The following example adds a unique index on the username field, and a normal index 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 the constraints parameter. You may wish to specify a default value as part of the 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 the names of the fields, the second part a boolean indicating whether the index should 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 using the Model.add_index() method or by directly using the ModelIndex 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 means that when using SQLite to create an index that involves an expression or scalar value, you will need to declare the index using the SQL helper:

# 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:

Table constraints

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

For instance, suppose you have a people table with a composite primary key of two columns, the person’s first and last name. You wish to have another table relate to the people table, and to do this, you will need to define a foreign key 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')]