Debian Codegraph @

docs/ref/contrib/postgres/constraints.txt

raw
1 ========================================
2 PostgreSQL specific database constraints
3 ========================================
4
5 .. module:: django.contrib.postgres.constraints
6 :synopsis: PostgreSQL specific database constraint
7
8 PostgreSQL supports additional data integrity constraints available from the
9 ``django.contrib.postgres.constraints`` module. They are added in the model
10 :attr:`Meta.constraints <django.db.models.Options.constraints>` option.
11
12 ``ExclusionConstraint``
13 =======================
14
15 .. class:: ExclusionConstraint(*, name, expressions, index_type=None, condition=None, deferrable=None, include=None, violation_error_code=None, violation_error_message=None)
16
17 Creates an exclusion constraint in the database. Internally, PostgreSQL
18 implements exclusion constraints using indexes. The default index type is
19 `GiST <https://www.postgresql.org/docs/current/gist.html>`_. To use them,
20 you need to activate the `btree_gist extension
21 <https://www.postgresql.org/docs/current/btree-gist.html>`_ on PostgreSQL.
22 You can install it using the
23 :class:`~django.contrib.postgres.operations.BtreeGistExtension` migration
24 operation.
25
26 If you attempt to insert a new row that conflicts with an existing row, an
27 :exc:`~django.db.IntegrityError` is raised. Similarly, when update
28 conflicts with an existing row.
29
30 Exclusion constraints are checked during the :ref:`model validation
31 <validating-objects>`.
32
33 ``name``
34 --------
35
36 .. attribute:: ExclusionConstraint.name
37
38 See :attr:`.BaseConstraint.name`.
39
40 ``expressions``
41 ---------------
42
43 .. attribute:: ExclusionConstraint.expressions
44
45 An iterable of 2-tuples. The first element is an expression or string. The
46 second element is an SQL operator represented as a string. To avoid typos, you
47 may use :class:`~django.contrib.postgres.fields.RangeOperators` which maps the
48 operators with strings. For example::
49
50 expressions = [
51 ("timespan", RangeOperators.ADJACENT_TO),
52 (F("room"), RangeOperators.EQUAL),
53 ]
54
55 .. admonition:: Restrictions on operators.
56
57 Only commutative operators can be used in exclusion constraints.
58
59 The :class:`OpClass() <django.contrib.postgres.indexes.OpClass>` expression can
60 be used to specify a custom `operator class`_ for the constraint expressions.
61 For example::
62
63 expressions = [
64 (OpClass("circle", name="circle_ops"), RangeOperators.OVERLAPS),
65 ]
66
67 creates an exclusion constraint on ``circle`` using ``circle_ops``.
68
69 .. _operator class: https://www.postgresql.org/docs/current/indexes-opclass.html
70
71 ``index_type``
72 --------------
73
74 .. attribute:: ExclusionConstraint.index_type
75
76 The index type of the constraint. Accepted values are ``GIST`` or ``SPGIST``.
77 Matching is case insensitive. If not provided, the default index type is
78 ``GIST``.
79
80 ``condition``
81 -------------
82
83 .. attribute:: ExclusionConstraint.condition
84
85 A :class:`~django.db.models.Q` object that specifies the condition to restrict
86 a constraint to a subset of rows. For example,
87 ``condition=Q(cancelled=False)``.
88
89 These conditions have the same database restrictions as
90 :attr:`django.db.models.Index.condition`.
91
92 ``deferrable``
93 --------------
94
95 .. attribute:: ExclusionConstraint.deferrable
96
97 Set this parameter to create a deferrable exclusion constraint. Accepted values
98 are ``Deferrable.DEFERRED`` or ``Deferrable.IMMEDIATE``. For example::
99
100 from django.contrib.postgres.constraints import ExclusionConstraint
101 from django.contrib.postgres.fields import RangeOperators
102 from django.db.models import Deferrable
103
104 ExclusionConstraint(
105 name="exclude_overlapping_deferred",
106 expressions=[
107 ("timespan", RangeOperators.OVERLAPS),
108 ],
109 deferrable=Deferrable.DEFERRED,
110 )
111
112 By default constraints are not deferred. A deferred constraint will not be
113 enforced until the end of the transaction. An immediate constraint will be
114 enforced immediately after every command.
115
116 .. warning::
117
118 Deferred exclusion constraints may lead to a `performance penalty
119 <https://www.postgresql.org/docs/current/sql-createtable.html#id-1.9.3.85.9.4>`_.
120
121 ``include``
122 -----------
123
124 .. attribute:: ExclusionConstraint.include
125
126 A list or tuple of the names of the fields to be included in the covering
127 exclusion constraint as non-key columns. This allows index-only scans to be
128 used for queries that select only included fields
129 (:attr:`~ExclusionConstraint.include`) and filter only by indexed fields
130 (:attr:`~ExclusionConstraint.expressions`).
131
132 ``include`` is supported for GiST indexes. PostgreSQL 14+ also supports
133 ``include`` for SP-GiST indexes.
134
135 ``violation_error_code``
136 ------------------------
137
138 .. attribute:: ExclusionConstraint.violation_error_code
139
140 The error code used when ``ValidationError`` is raised during
141 :ref:`model validation <validating-objects>`. Defaults to ``None``.
142
143 ``violation_error_message``
144 ---------------------------
145
146 The error message used when ``ValidationError`` is raised during
147 :ref:`model validation <validating-objects>`. Defaults to
148 :attr:`.BaseConstraint.violation_error_message`.
149
150 Examples
151 --------
152
153 The following example restricts overlapping reservations in the same room, not
154 taking canceled reservations into account::
155
156 from django.contrib.postgres.constraints import ExclusionConstraint
157 from django.contrib.postgres.fields import DateTimeRangeField, RangeOperators
158 from django.db import models
159 from django.db.models import Q
160
161
162 class Room(models.Model):
163 number = models.IntegerField()
164
165
166 class Reservation(models.Model):
167 room = models.ForeignKey("Room", on_delete=models.CASCADE)
168 timespan = DateTimeRangeField()
169 cancelled = models.BooleanField(default=False)
170
171 class Meta:
172 constraints = [
173 ExclusionConstraint(
174 name="exclude_overlapping_reservations",
175 expressions=[
176 ("timespan", RangeOperators.OVERLAPS),
177 ("room", RangeOperators.EQUAL),
178 ],
179 condition=Q(cancelled=False),
180 ),
181 ]
182
183 In case your model defines a range using two fields, instead of the native
184 PostgreSQL range types, you should write an expression that uses the equivalent
185 function (e.g. ``TsTzRange()``), and use the delimiters for the field. Most
186 often, the delimiters will be ``'[)'``, meaning that the lower bound is
187 inclusive and the upper bound is exclusive. You may use the
188 :class:`~django.contrib.postgres.fields.RangeBoundary` that provides an
189 expression mapping for the `range boundaries <https://www.postgresql.org/docs/
190 current/rangetypes.html#RANGETYPES-INCLUSIVITY>`_. For example::
191
192 from django.contrib.postgres.constraints import ExclusionConstraint
193 from django.contrib.postgres.fields import (
194 DateTimeRangeField,
195 RangeBoundary,
196 RangeOperators,
197 )
198 from django.db import models
199 from django.db.models import Func, Q
200
201
202 class TsTzRange(Func):
203 function = "TSTZRANGE"
204 output_field = DateTimeRangeField()
205
206
207 class Reservation(models.Model):
208 room = models.ForeignKey("Room", on_delete=models.CASCADE)
209 start = models.DateTimeField()
210 end = models.DateTimeField()
211 cancelled = models.BooleanField(default=False)
212
213 class Meta:
214 constraints = [
215 ExclusionConstraint(
216 name="exclude_overlapping_reservations",
217 expressions=[
218 (
219 TsTzRange("start", "end", RangeBoundary()),
220 RangeOperators.OVERLAPS,
221 ),
222 ("room", RangeOperators.EQUAL),
223 ],
224 condition=Q(cancelled=False),
225 ),
226 ]