8.14 Constraints and indexes
CheckConstraint, UniqueConstraint and Index are the objects a model’s Meta lists under "constraints" and "indexes". Each describes one rule or one index of the model’s table, can give that description to a migration, and hands a schema editor its SQL; a constraint can also test an instance. A unique field, a foreign key and a positive integer put rules and indexes in the same table with no such object.
A table carries rules that the database enforces on every row, whatever program wrote the row, and indexes that let it find rows without reading them all. A model states these in two ways. Some belong to a single field, as an argument of the field or simply by its class: this field is unique, that one refers to another table. The rest involve several columns, or a condition, or an expression, and, but for the option unique_together, are objects of their own in the model’s Meta.
Those objects are descriptions. Each holds the arguments it was made with. None holds a model, a table or a connection: every method that needs one is handed it. And none of the SQL methods sends a statement: each returns a clause or a statement to a schema editor, and the editor decides what is sent. The one method that sends anything itself is a constraint’s validate.
A schema editor is the database backend’s object that turns a model, a field, a constraint or an index into statements that change the schema, and sends them. Its statements are sent as its with block runs, and those it has put off are sent when the block is left without an error (The schema editor: BaseDatabaseSchemaEditor).
The example is the ship of a shipping line, with one constraint of each kind and one index:
# harbour/fleet/models.py
class Named(models.Model):
name = models.CharField(max_length=60)
class Meta:
abstract = True
ordering = ["name"]
def __str__(self):
return self.name
class Ship(Named):
tonnage = models.PositiveIntegerField()
home = models.ForeignKey(Port, on_delete=models.PROTECT, related_name="ships")
captain = models.OneToOneField("crew.Sailor", null=True, blank=True, on_delete=models.SET_NULL, related_name="command")
class Meta(Named.Meta):
constraints = [
models.CheckConstraint(condition=models.Q(tonnage__gt=0), name="%(class)s_has_tonnage"),
models.UniqueConstraint(fields=["name", "home"], name="one_name_to_a_port"),
]
indexes = [models.Index(fields=["home", "tonnage"])]
What each can be asked
A constraint and an index answer the same few questions, each put by a different part of Django.
| what is asked for | who asks | a constraint’s method | an index’s method |
|---|---|---|---|
its clause inside "CREATE TABLE" |
who asks Base |
a constraint’s method constraint_sql, which may return nothing |
an index’s method none: an index is not part of that statement |
| the statement that adds it to a table | who asks Base, Base, and Base for the indexes of Meta and, where the table’s statement has parameters, the constraints |
a constraint’s method create_sql |
an index’s method create_sql |
| the statement that drops it | who asks Base and Base |
a constraint’s method remove_sql |
an index’s method remove_sql |
| whether one instance obeys it | who asks Model |
a constraint’s method validate |
an index’s method none |
| what is wrong with its own declaration | who asks the checks of its model | a constraint’s method check |
an index’s method check |
| the arguments it was made with | who asks migrations | a constraint’s method deconstruct |
an index’s method deconstruct |
| a second object like it | who asks the model’s options as they fill in its name, and a migration’s picture of the model | a constraint’s method clone |
an index’s method clone |
validate is how a constraint takes part in Model.full_clean: a CheckConstraint has the database evaluate its condition against the instance’s values, and a UniqueConstraint looks for another row with the same ones (Validating an instance). check is called by the system checks, once for each database being checked that the model is migrated on, and reports such things as a field that does not exist or a feature the database lacks (The model checks).
From Meta to the class
The lists a Meta gives as "constraints" and "indexes" reach the class through its options. Options copies each attribute of Meta that it knows onto the options object, these two among them, taking one that the Meta only inherits as readily as one it declares (The options: what a model keeps as _meta). For an abstract class the two lists are left as they came. For any other class both are then passed through Options.
That method fills in two placeholders a name may contain, "%(app_label)s" and "%(class)s", with the app’s label and the model’s name, both in lower case. It does not write the new name into the object it was given. It clones each object, formats the clone’s name, and returns a new list:
Ship._meta.original_attrs['constraints'][0].name, Ship._meta.constraints[0].name -> ('%(class)s_has_tonnage', 'ship_has_tonnage')
Ship._meta.original_attrs['constraints'][0] is Ship._meta.constraints[0] -> False
Options holds what the Meta said, and the object there still has its placeholder. A Meta is an ordinary class, and a class that inherits it, or inherits an abstract base that has one, is handed the very same list of the very same objects. And among the system checks is one that reports a constraint’s name, or an index’s, used by two models. The placeholders let one declaration on an abstract base give each inheriting class a name of its own, and the clone leaves the declaration unchanged for the next class.
An index is copied a second time, and this copy is made for every class, an abstract one included, which _format_names passed over. Near its end ModelBase replaces the options’ list of indexes with a deep copy of each, under a comment that says this is so that index names are unique when models extend an abstract model. And the name of an index that has none is not set until ModelBase, the last thing done to a class before it is registered. The comment there gives the reason for the delay: it cannot be done in Options because the fields have not been added to the model at that point. In the class statement of Ship, with the rest of what _prepare did left out:
ModelBase._prepare(Ship)
...
Index.set_name_with_model(Ship) -> the index is named 'fleet_ship_home_id_f9b8f2_idx'
The table of Ship, read line by line
A schema editor’s create_model is handed a model class and makes its table. This is what SQLite’s sent for Ship: the first statement at once, and the other two, which were put off, as the editor was left. The tables of the other models, made in between, are left out:
editor.create_model(Ship)
SQL CREATE TABLE "fleet_ship" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar(60) NOT NULL, "tonnage" integer unsigned NOT NULL CHECK ("tonnage" >= 0), "home_id" bigint NOT NULL REFERENCES "fleet_port" ("id") DEFERRABLE INITIALLY DEFERRED, "captain_id" bigint NULL UNIQUE REFERENCES "crew_sailor" ("id") DEFERRABLE INITIALLY DEFERRED, CONSTRAINT "ship_has_tonnage" CHECK ("tonnage" > 0), CONSTRAINT "one_name_to_a_port" UNIQUE ("name", "home_id"))
...
the editor is left
...
SQL CREATE INDEX "fleet_ship_home_id_12c4d51a" ON "fleet_ship" ("home_id")
SQL CREATE INDEX "fleet_ship_home_id_f9b8f2_idx" ON "fleet_ship" ("home_id", "tonnage")
| in the statements | what declared it | where the editor gets it |
|---|---|---|
"NOT NULL" on four columns, "NULL" on "captain_id" |
what declared it each field’s null, which is false unless the field is given null=True |
where the editor gets it the column’s own definition |
"PRIMARY KEY" on "id" |
what declared it the key that was added to the class, a field with primary_key=True |
where the editor gets it the column’s own definition |
the "CHECK" after "tonnage", that it is not below zero |
what declared it nothing but the field’s class, Positive |
where the editor gets it the field’s db_check, from a table the backend keeps by the kind of field |
"REFERENCES" on "home_id" and "captain_id" |
what declared it the two relation fields, each with db_constraint true |
where the editor gets it the editor’s template for a foreign key written in the column |
"UNIQUE" on "captain_id" |
what declared it OneToOneField, which sets unique=True on itself |
where the editor gets it the column’s own definition |
the constraint "ship_ |
what declared it the CheckConstraint of Meta |
where the editor gets it Check |
the constraint "one_ |
what declared it the UniqueConstraint of Meta |
where the editor gets it Unique |
the index "fleet_ |
what declared it the ForeignKey named "home", which sets db_index=True on itself unless told otherwise |
where the editor gets it Base |
the index "fleet_ |
what declared it the Index of Meta |
where the editor gets it Index.create_sql |
BaseDatabaseSchemaEditor writes the first statement. It goes through the model’s own fields and writes a column for each, and what a field brings with it is written into its column. The first "CHECK" is not a constraint object’s at all: Field.db_check looks the field’s class up in the backend’s data_type_check_constraints, where SQLite’s backend has an entry for each positive integer field, and the editor adds what it finds to the column.
After the columns come the constraints of Meta, in the order of the list: table_sql asks each for its constraint_sql and leaves out any that returned nothing. But if the statement so far has parameters, as it does where a column has a default that the backend takes as a parameter, each constraint is asked for its create_sql instead and that statement is put off. The comment explains: a constraint’s SQL has its values written into the text, and is unfit to be mixed with SQL whose values are to be passed separately.
The indexes are never in the first statement. create_model collects them afterwards from _model_indexes_sql, first an index for each field that should have one and then each Index of Meta, and adds them to BaseDatabaseSchemaEditor, the list the editor runs when it is left.
Which fields should have an index is a short rule in the base editor, a field whose db_index is true and which is not unique, and the editors of MySQL and Oracle narrow it. So "home_id", a foreign key’s column, has an index of its own, and "captain_id", a one-to-one field’s and therefore unique, has none: a unique column is indexed by the database already. A field’s index is named by the editor, from the table, the column and eight digits of a digest, a different scheme from an Index object’s.
BaseConstraint: a name, a message and a code
BaseConstraint settles what every constraint has: a name, and the message and the code of the error raised when an instance breaks it. Its arguments are keywords only. The name is required. The message, when none is given, is the class’s BaseConstraint, a sentence with a place for the name, which BaseConstraint fills in. The code, BaseConstraint, is None unless one is given.
What a constraint does with a table or an instance is left to the subclass: on BaseConstraint the methods constraint_sql, create_sql, remove_sql and validate each raise NotImplementedError.
CheckConstraint: a condition
CheckConstraint adds one argument, a condition, which must be a Q object or an expression whose value is true or false; anything else is refused with TypeError when the constraint is made.
Its SQL is made the way the "WHERE" of a query is made, by the same code (From QuerySet to SQL). CheckConstraint builds a Query for the model, has it turn the condition into the node a filter would become, and compiles that node with the schema editor’s connection. Two things differ from a query. The Query is made with alias_cols=False, so a column is written by its name alone and not after its table’s. And each parameter is turned into a literal by the editor’s quote_value and written into the text.
CheckConstraint and CheckConstraint put that text into the schema editor’s templates, and CheckConstraint needs only the name. Each line here is a method of Ship’s check constraint, called with the model and a schema editor of the SQLite backend, and what it returned:
ddl(Ship, Ship._meta.constraints[0], 'constraint_sql') -> CONSTRAINT "ship_has_tonnage" CHECK ("tonnage" > 0)
ddl(Ship, Ship._meta.constraints[0], 'create_sql') -> ALTER TABLE "fleet_ship" ADD CONSTRAINT "ship_has_tonnage" CHECK ("tonnage" > 0)
ddl(Ship, Ship._meta.constraints[0], 'remove_sql') -> ALTER TABLE "fleet_ship" DROP CONSTRAINT "ship_has_tonnage"
create_sql and remove_sql return nothing on a backend whose features say it has no check constraints on tables.
UniqueConstraint: fields or expressions, and what goes round them
A UniqueConstraint says that no two rows may agree on something. The something is either a tuple of field names, given as fields=, or one or more expressions, given positionally, among which a string is taken for an F of that name. It is kept as UniqueConstraint or as UniqueConstraint.
Five more arguments qualify the rule:
condition=, aQ: the rule holds only among the rows that meet it.deferrable=, which isDeferrableor.DEFERRED Deferrable: the constraint is declared.IMMEDIATE "DEFERRABLE INITIALLY DEFERRED", which is not enforced until the end of the transaction, or"DEFERRABLE INITIALLY IMMEDIATE", which is enforced after every statement.include=, names of fields whose columns are stored in the index beside the key without being part of the rule.opclasses=, the name of an operator class for each field, which only PostgreSQL’s schema editor writes into the statement (PostgreSQL indexes andExclusionConstrainthas the rest of what is PostgreSQL’s alone).nulls_distinct=, whether two nulls count as different values. Left atNone, the database’s own rule stands.
In the table’s statement or after it, as a constraint or as an index
A UniqueConstraint reaches the database at one of two moments, as a clause inside the table’s own statement or as a statement after it, and in one of two forms, a constraint or a unique index. For a table whose statement has no parameters, the schema editor settles the two questions in two methods, whose tests differ by one argument. The moment is settled by _unique_sql, which UniqueConstraint calls as the table’s statement is written: it returns a clause for that statement, or has a statement made, puts that on the editor’s list of statements to run later, and returns nothing for the table; the comment there says that databases support conditional, covering, functional and nulls-distinct unique constraints by way of a unique index. The form is settled by _create_unique_sql, which makes the statement in both cases: the one just put off, and the one UniqueConstraint returns.
UniqueConstraint: _unique_sql gives the second column, _create_unique_sql the third.| a unique constraint that | in the table’s statement | the template of its own statement |
|---|---|---|
| has fields and nothing more, or is deferrable besides | in the table’s statement the clause "CONSTRAINT … UNIQUE (…)", with the words for a deferrable constraint after it |
the template of its own statement Base |
| says something about nulls, and has nothing of the next row | in the table’s statement nothing: its statement is put off | the template of its own statement sql_ |
| has a condition, included columns, operator classes or expressions | in the table’s statement nothing: its statement is put off | the template of its own statement Base |
| needs a feature the backend lacks | in the table’s statement nothing | the template of its own statement none: no statement is made |
In the base editor sql_create_unique is "ALTER TABLE … ADD CONSTRAINT … UNIQUE". So a constraint that only says something about nulls is put off like the others, and what is put off for it is that "ALTER TABLE" and not an index. The four arguments that select the index are the four a deferrable constraint may not have: UniqueConstraint refuses each of them together with deferrable=.
ddl(Ship, Ship._meta.constraints[1], 'constraint_sql') -> CONSTRAINT "one_name_to_a_port" UNIQUE ("name", "home_id")
ddl(Ship, Ship._meta.constraints[1], 'create_sql') -> CREATE UNIQUE INDEX "one_name_to_a_port" ON "fleet_ship" ("name", "home_id")
ddl(Ship, models.UniqueConstraint(fields=['name'], condition=Q(tonnage__gt=1000), name='big_names'), 'constraint_sql') -> None
ddl(Ship, models.UniqueConstraint(fields=['name'], condition=Q(tonnage__gt=1000), name='big_names'), 'create_sql') -> CREATE UNIQUE INDEX "big_names" ON "fleet_ship" ("name") WHERE "tonnage" > 1000
ddl(Ship, models.UniqueConstraint(models.functions.Lower('name'), name='names_whatever_the_case'), 'create_sql') -> CREATE UNIQUE INDEX "names_whatever_the_case" ON "fleet_ship" ((LOWER("name")))
Ship._meta.total_unique_constraints -> [<UniqueConstraint: fields=('name', 'home') name='one_name_to_a_port'>]
The first two lines are Ship’s own constraint. The statement for adding it afterwards is a "CREATE UNIQUE INDEX" here, where the base template would have given an "ALTER TABLE", because SQLite’s schema editor replaces sql_create_unique with one of its own. The third and fourth lines are a constraint with a condition: nothing for the table’s statement, and a unique index with a "WHERE" clause to follow it. The fifth is a constraint over an expression.
The total unique constraints
Options, the last line of that recording, is a list other code relies on. It holds the model’s unique constraints that have neither a condition nor expressions: the ones that promise, as its docstring puts it, a set of fields guaranteed to be unique for all rows. A constraint with a condition makes its promise for some rows only, and one over expressions makes it about no set of fields. QuerySet.in_bulk accepts a field as its key when one of these constraints is over that field alone, and QuerySet takes an ordering that includes every field of one, none of them nullable, to be an ordering in which no two rows tie.
Index
An Index is built like a constraint and is not one: it shares no base class with them, has no validate, and no clause in a table’s own statement.
It is over fields or over expressions, never both. A field name with a minus sign before it is indexed in descending order, and Index holds each name with its order. Around them an index takes a condition, which makes it an index of some rows only, include= and opclasses= with the meanings they have for a unique constraint, and db_tablespace=.
An index is the one object here that may go without a name, and only in the plain case. Index.__init__ refuses an index with expressions, a condition, included fields or operator classes unless it is named, and one whose fields are not all strings.
Index.create_sql hands its arguments to the editor’s _create_index_sql, and Index.remove_sql returns a "DROP INDEX". The descending order is passed on only to a backend whose features say it supports ordered index columns. An expression, in an index or in a unique constraint, is first wrapped in an IndexExpression, which puts it in parentheses unless it resolves to a plain column.
ddl(Ship, Ship._meta.indexes[0], 'create_sql') -> CREATE INDEX "fleet_ship_home_id_f9b8f2_idx" ON "fleet_ship" ("home_id", "tonnage")
models.Index(fields=['name', '-tonnage']).fields_orders, models.Index(fields=['name', '-tonnage']).name -> ([('name', ''), ('tonnage', 'DESC')], '')
ddl(Ship, models.Index(fields=['name', '-tonnage'], name='by_name_and_size'), 'create_sql') -> CREATE INDEX "by_name_and_size" ON "fleet_ship" ("name", "tonnage" DESC)
ddl(Ship, models.Index(models.functions.Lower('name'), name='lower_names'), 'create_sql') -> CREATE INDEX "lower_names" ON "fleet_ship" ((LOWER("name")))
The name: Index.set_name_with_model
An index that was given no name holds the empty string in its place, as the second line shows, until Index makes one from the model it turns out to belong to. The name has three parts joined by underscores: the first eleven characters of the table’s name; the first seven of the first column’s name; and six hexadecimal digits followed by Index.suffix, which is "idx". The digits are the start of an MD5 digest of the table’s name, every column’s name, a descending one with its minus sign, and the suffix. So "fleet_ship_home_id_f9b8f2_idx" is the table, the column of the index’s first field, the digest and the suffix.
The parts are cut so that the whole is at most thirty characters, which is Index; the comment beside that attribute says the limit is there for compatibility with Oracle. A name that would begin with an underscore or a digit has its first character replaced by a "D". A name the author wrote is held to the same two rules by Index.check, which reports one that breaks either.
Adding a constraint to a table that exists
What create_sql returns is an offer. BaseDatabaseSchemaEditor takes it: it calls create_sql and sends the statement, if there is one. SQLite’s editor does not. Its add_constraint passes a UniqueConstraint with a condition, expressions, included fields or a deferrable setting to the base method, which for the first two sends the "CREATE UNIQUE INDEX" shown earlier and for the other two, which are features SQLite’s backend lacks, sends nothing. For every other constraint it makes the table again.
A schema editor of the SQLite backend was asked to add a second check constraint to the table of Ship. For the length of the call the constraint was also put on Ship’s own list, Options, where the model a migration hands the editor would have it:
editor.add_constraint(Ship, extra), where extra is a CheckConstraint named 'ship_not_too_big' that Ship._meta.constraints lists for this call
SQL CREATE TABLE "new__fleet_ship" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar(60) NOT NULL, "tonnage" integer unsigned NOT NULL CHECK ("tonnage" >= 0), "home_id" bigint NOT NULL REFERENCES "fleet_port" ("id") DEFERRABLE INITIALLY DEFERRED, "captain_id" bigint NULL UNIQUE REFERENCES "crew_sailor" ("id") DEFERRABLE INITIALLY DEFERRED, CONSTRAINT "ship_has_tonnage" CHECK ("tonnage" > 0), CONSTRAINT "one_name_to_a_port" UNIQUE ("name", "home_id"), CONSTRAINT "ship_not_too_big" CHECK ("tonnage" < 500000))
SQL INSERT INTO "new__fleet_ship" ("id", "name", "tonnage", "home_id", "captain_id") SELECT "id", "name", "tonnage", "home_id", "captain_id" FROM "fleet_ship"
SQL DROP TABLE "fleet_ship"
SQL ALTER TABLE "new__fleet_ship" RENAME TO "fleet_ship"
SQL CREATE INDEX "fleet_ship_home_id_12c4d51a" ON "fleet_ship" ("home_id")
SQL CREATE INDEX "fleet_ship_home_id_f9b8f2_idx" ON "fleet_ship" ("home_id", "tonnage")
No "ALTER TABLE … ADD CONSTRAINT" was sent, though that is what the constraint’s create_sql returns on this backend. The table was made again under another name, with the new constraint after the two the old one had, filled from the old table and renamed, and the indexes were made again.
The third constraint is in the new table because it was on the model’s list, not because it was handed to add_constraint. Where it remakes the table, SQLite’s method has looked at the constraint it was handed only to see what kind it is, and _remake_table works from the model: its fields, its unique_together, its indexes and its constraints. That is why the constraint had to be on the list for this call. In a migration the model handed to the editor comes from the state after the operation, to which the constraint has already been added (The field operations, and those on indexes and constraints).
unique_together
The option unique_together of a model’s Meta is the older way to say what a UniqueConstraint over fields says: a sequence of field names, or a sequence of such sequences, with no name and no other argument. Options normalises it to a tuple of tuples and keeps it as Options. It makes no object. The schema editor reads the option itself: table_sql makes a statement for each entry with _create_unique_sql, under a name the editor makes up from the table and the columns, and always puts it off. Validation reads the option too, as a unique check of several fields (Validating an instance).
Django uses it for a model it writes itself. A many-to-many field that names no through model has one made for it, and create_many_to_many_intermediary_model gives that model a Meta whose unique_together is its two foreign keys (Many-to-many relations). The harbour’s Voyage has such a field, "calls". Asked of that field, which the recording has under its own name:
calls.remote_field.through._meta.unique_together -> (('voyage', 'port'),)
And in the recorded tables, with the table of Voyage left out and the unique index among the statements put off:
editor.create_model(Voyage)
SQL CREATE TABLE "fleet_voyage_calls" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "voyage_id" bigint NOT NULL REFERENCES "fleet_voyage" ("id") DEFERRABLE INITIALLY DEFERRED, "port_id" bigint NOT NULL REFERENCES "fleet_port" ("id") DEFERRABLE INITIALLY DEFERRED)
...
SQL CREATE UNIQUE INDEX "fleet_voyage_calls_voyage_id_port_id_8c446735_uniq" ON "fleet_voyage_calls" ("voyage_id", "port_id")
A constraint a backend cannot make
A condition, a deferrable constraint, included columns and an index over expressions are each something not every database has, and the objects do not refuse to exist on one that lacks what they need. Each feature is a flag on the backend’s features object, such as BaseDatabaseFeatures for a condition or BaseDatabaseFeatures for a deferrable constraint, and the schema editor consults them.
A unique constraint that needs a missing feature is not made at all: _unique_supported answers no, each of the three methods that would have produced SQL returns nothing, and the base add_constraint sends nothing. SQLite’s features do not include deferrable unique constraints, and its editor has no statement to offer for one:
ddl(Ship, models.UniqueConstraint(fields=['name'], deferrable=models.Deferrable.DEFERRED, name='later'), 'create_sql') -> None
An index over expressions is likewise skipped, by add_index and when a table is made, and an index with included columns is made without them. None of these methods raises anything.
The place a missing feature is reported is the system checks, which warn of one unless the model’s Meta lists the feature in required_db_features (The model checks). The option has a second effect: Options, for a model that names no required_db_vendor, answers no for a connection that lacks a listed feature, and a migration operation that is told no leaves the model’s table alone on that database.
What a migration compares
To a migration a constraint or an index is a value: something to write into a file, to copy, and to compare with last time’s (Migrations). deconstruct returns the class’s path and the arguments that would make the object again. clone makes the object again from them, and the picture of a model that migrations keep is made of such clones, not of the model’s own objects. And __eq__ is what the autodetector’s test of whether an object is in the old list or the new comes down to: Index.__eq__ compares what two indexes of the same class deconstruct to, and the constraints compare their attributes one by one.
Ship._meta.constraints[0].deconstruct() -> ('django.db.models.CheckConstraint', (), {'name': 'ship_has_tonnage', 'condition': <Q: (AND: ('tonnage__gt', 0))>})
...
Ship._meta.indexes[0].deconstruct() -> ('django.db.models.Index', (), {'name': 'fleet_ship_home_id_f9b8f2_idx', 'fields': ['home', 'tonnage']})
The index was written without a name and deconstructs with one, the name it was given when the class was prepared: a migration records an index by name, and the operation that adds one refuses an index without. A constraint’s message and code, for their part, are in what it deconstructs to and are no part of the table. BaseConstraint names those two arguments, and the autodetector sets them aside when it decides whether a changed constraint has to be dropped and made again or only recorded as altered.