1
.. _metadata_constraints_toplevel:
2
.. _metadata_constraints:
4
.. module:: sqlalchemy.schema
6
=================================
7
Defining Constraints and Indexes
8
=================================
10
.. _metadata_foreignkeys:
12
This section will discuss SQL :term:`constraints` and indexes. In SQLAlchemy
13
the key classes include :class:`.ForeignKeyConstraint` and :class:`.Index`.
18
A *foreign key* in SQL is a table-level construct that constrains one or more
19
columns in that table to only allow values that are present in a different set
20
of columns, typically but not always located on a different table. We call the
21
columns which are constrained the *foreign key* columns and the columns which
22
they are constrained towards the *referenced* columns. The referenced columns
23
almost always define the primary key for their owning table, though there are
24
exceptions to this. The foreign key is the "joint" that connects together
25
pairs of rows which have a relationship with each other, and SQLAlchemy
26
assigns very deep importance to this concept in virtually every area of its
29
In SQLAlchemy as well as in DDL, foreign key constraints can be defined as
30
additional attributes within the table clause, or for single-column foreign
31
keys they may optionally be specified within the definition of a single
32
column. The single column foreign key is more common, and at the column level
33
is specified by constructing a :class:`~sqlalchemy.schema.ForeignKey` object
34
as an argument to a :class:`~sqlalchemy.schema.Column` object::
36
user_preference = Table('user_preference', metadata,
37
Column('pref_id', Integer, primary_key=True),
38
Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False),
39
Column('pref_name', String(40), nullable=False),
40
Column('pref_value', String(100))
43
Above, we define a new table ``user_preference`` for which each row must
44
contain a value in the ``user_id`` column that also exists in the ``user``
45
table's ``user_id`` column.
47
The argument to :class:`~sqlalchemy.schema.ForeignKey` is most commonly a
48
string of the form *<tablename>.<columnname>*, or for a table in a remote
49
schema or "owner" of the form *<schemaname>.<tablename>.<columnname>*. It may
50
also be an actual :class:`~sqlalchemy.schema.Column` object, which as we'll
51
see later is accessed from an existing :class:`~sqlalchemy.schema.Table`
52
object via its ``c`` collection::
54
ForeignKey(user.c.user_id)
56
The advantage to using a string is that the in-python linkage between ``user``
57
and ``user_preference`` is resolved only when first needed, so that table
58
objects can be easily spread across multiple modules and defined in any order.
60
Foreign keys may also be defined at the table level, using the
61
:class:`~sqlalchemy.schema.ForeignKeyConstraint` object. This object can
62
describe a single- or multi-column foreign key. A multi-column foreign key is
63
known as a *composite* foreign key, and almost always references a table that
64
has a composite primary key. Below we define a table ``invoice`` which has a
65
composite primary key::
67
invoice = Table('invoice', metadata,
68
Column('invoice_id', Integer, primary_key=True),
69
Column('ref_num', Integer, primary_key=True),
70
Column('description', String(60), nullable=False)
73
And then a table ``invoice_item`` with a composite foreign key referencing
76
invoice_item = Table('invoice_item', metadata,
77
Column('item_id', Integer, primary_key=True),
78
Column('item_name', String(60), nullable=False),
79
Column('invoice_id', Integer, nullable=False),
80
Column('ref_num', Integer, nullable=False),
81
ForeignKeyConstraint(['invoice_id', 'ref_num'], ['invoice.invoice_id', 'invoice.ref_num'])
84
It's important to note that the
85
:class:`~sqlalchemy.schema.ForeignKeyConstraint` is the only way to define a
86
composite foreign key. While we could also have placed individual
87
:class:`~sqlalchemy.schema.ForeignKey` objects on both the
88
``invoice_item.invoice_id`` and ``invoice_item.ref_num`` columns, SQLAlchemy
89
would not be aware that these two values should be paired together - it would
90
be two individual foreign key constraints instead of a single composite
91
foreign key referencing two columns.
95
Creating/Dropping Foreign Key Constraints via ALTER
96
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
98
In all the above examples, the :class:`~sqlalchemy.schema.ForeignKey` object
99
causes the "REFERENCES" keyword to be added inline to a column definition
100
within a "CREATE TABLE" statement when
101
:func:`~sqlalchemy.schema.MetaData.create_all` is issued, and
102
:class:`~sqlalchemy.schema.ForeignKeyConstraint` invokes the "CONSTRAINT"
103
keyword inline with "CREATE TABLE". There are some cases where this is
104
undesireable, particularly when two tables reference each other mutually, each
105
with a foreign key referencing the other. In such a situation at least one of
106
the foreign key constraints must be generated after both tables have been
107
built. To support such a scheme, :class:`~sqlalchemy.schema.ForeignKey` and
108
:class:`~sqlalchemy.schema.ForeignKeyConstraint` offer the flag
109
``use_alter=True``. When using this flag, the constraint will be generated
110
using a definition similar to "ALTER TABLE <tablename> ADD CONSTRAINT <name>
111
...". Since a name is required, the ``name`` attribute must also be specified.
114
node = Table('node', meta,
115
Column('node_id', Integer, primary_key=True),
116
Column('primary_element', Integer,
117
ForeignKey('element.element_id', use_alter=True, name='fk_node_element_id')
121
element = Table('element', meta,
122
Column('element_id', Integer, primary_key=True),
123
Column('parent_node_id', Integer),
124
ForeignKeyConstraint(
128
name='fk_element_parent_node_id'
132
.. _on_update_on_delete:
134
ON UPDATE and ON DELETE
135
~~~~~~~~~~~~~~~~~~~~~~~
137
Most databases support *cascading* of foreign key values, that is the when a
138
parent row is updated the new value is placed in child rows, or when the
139
parent row is deleted all corresponding child rows are set to null or deleted.
140
In data definition language these are specified using phrases like "ON UPDATE
141
CASCADE", "ON DELETE CASCADE", and "ON DELETE SET NULL", corresponding to
142
foreign key constraints. The phrase after "ON UPDATE" or "ON DELETE" may also
143
other allow other phrases that are specific to the database in use. The
144
:class:`~sqlalchemy.schema.ForeignKey` and
145
:class:`~sqlalchemy.schema.ForeignKeyConstraint` objects support the
146
generation of this clause via the ``onupdate`` and ``ondelete`` keyword
147
arguments. The value is any string which will be output after the appropriate
148
"ON UPDATE" or "ON DELETE" phrase::
150
child = Table('child', meta,
151
Column('id', Integer,
152
ForeignKey('parent.id', onupdate="CASCADE", ondelete="CASCADE"),
157
composite = Table('composite', meta,
158
Column('id', Integer, primary_key=True),
159
Column('rev_id', Integer),
160
Column('note_id', Integer),
161
ForeignKeyConstraint(
162
['rev_id', 'note_id'],
163
['revisions.id', 'revisions.note_id'],
164
onupdate="CASCADE", ondelete="SET NULL"
168
Note that these clauses are not supported on SQLite, and require ``InnoDB``
169
tables when used with MySQL. They may also not be supported on other
176
Unique constraints can be created anonymously on a single column using the
177
``unique`` keyword on :class:`~sqlalchemy.schema.Column`. Explicitly named
178
unique constraints and/or those with multiple columns are created via the
179
:class:`~sqlalchemy.schema.UniqueConstraint` table-level construct.
181
.. sourcecode:: python+sql
183
from sqlalchemy import UniqueConstraint
186
mytable = Table('mytable', meta,
188
# per-column anonymous unique constraint
189
Column('col1', Integer, unique=True),
191
Column('col2', Integer),
192
Column('col3', Integer),
194
# explicit/composite unique constraint. 'name' is optional.
195
UniqueConstraint('col2', 'col3', name='uix_1')
201
Check constraints can be named or unnamed and can be created at the Column or
202
Table level, using the :class:`~sqlalchemy.schema.CheckConstraint` construct.
203
The text of the check constraint is passed directly through to the database,
204
so there is limited "database independent" behavior. Column level check
205
constraints generally should only refer to the column to which they are
206
placed, while table level constraints can refer to any columns in the table.
208
Note that some databases do not actively support check constraints such as
211
.. sourcecode:: python+sql
213
from sqlalchemy import CheckConstraint
216
mytable = Table('mytable', meta,
218
# per-column CHECK constraint
219
Column('col1', Integer, CheckConstraint('col1>5')),
221
Column('col2', Integer),
222
Column('col3', Integer),
224
# table level CHECK constraint. 'name' is optional.
225
CheckConstraint('col2 > col3 + 5', name='check1')
228
{sql}mytable.create(engine)
229
CREATE TABLE mytable (
230
col1 INTEGER CHECK (col1>5),
233
CONSTRAINT check1 CHECK (col2 > col3 + 5)
236
PRIMARY KEY Constraint
237
----------------------
239
The primary key constraint of any :class:`.Table` object is implicitly
240
present, based on the :class:`.Column` objects that are marked with the
241
:paramref:`.Column.primary_key` flag. The :class:`.PrimaryKeyConstraint`
242
object provides explicit access to this constraint, which includes the
243
option of being configured directly::
245
from sqlalchemy import PrimaryKeyConstraint
247
my_table = Table('mytable', metadata,
248
Column('id', Integer),
249
Column('version_id', Integer),
250
Column('data', String(50)),
251
PrimaryKeyConstraint('id', 'version_id', name='mytable_pk')
256
:class:`.PrimaryKeyConstraint` - detailed API documentation.
258
Setting up Constraints when using the Declarative ORM Extension
259
----------------------------------------------------------------
261
The :class:`.Table` is the SQLAlchemy Core construct that allows one to define
262
table metadata, which among other things can be used by the SQLAlchemy ORM
263
as a target to map a class. The :ref:`Declarative <declarative_toplevel>`
264
extension allows the :class:`.Table` object to be created automatically, given
265
the contents of the table primarily as a mapping of :class:`.Column` objects.
267
To apply table-level constraint objects such as :class:`.ForeignKeyConstraint`
268
to a table defined using Declarative, use the ``__table_args__`` attribute,
269
described at :ref:`declarative_table_args`.
271
.. _constraint_naming_conventions:
273
Configuring Constraint Naming Conventions
274
-----------------------------------------
276
Relational databases typically assign explicit names to all constraints and
277
indexes. In the common case that a table is created using ``CREATE TABLE``
278
where constraints such as CHECK, UNIQUE, and PRIMARY KEY constraints are
279
produced inline with the table definition, the database usually has a system
280
in place in which names are automatically assigned to these constraints, if
281
a name is not otherwise specified. When an existing database table is altered
282
in a database using a command such as ``ALTER TABLE``, this command typically
283
needs to specify expicit names for new constraints as well as be able to
284
specify the name of an existing constraint that is to be dropped or modified.
286
Constraints can be named explicitly using the :paramref:`.Constraint.name` parameter,
287
and for indexes the :paramref:`.Index.name` parameter. However, in the
288
case of constraints this parameter is optional. There are also the use
289
cases of using the :paramref:`.Column.unique` and :paramref:`.Column.index`
290
parameters which create :class:`.UniqueConstraint` and :class:`.Index` objects
291
without an explicit name being specified.
293
The use case of alteration of existing tables and constraints can be handled
294
by schema migration tools such as `Alembic <http://http://alembic.readthedocs.org/>`_.
295
However, neither Alembic nor SQLAlchemy currently create names for constraint
296
objects where the name is otherwise unspecified, leading to the case where
297
being able to alter existing constraints means that one must reverse-engineer
298
the naming system used by the relational database to auto-assign names,
299
or that care must be taken to ensure that all constraints are named.
301
In contrast to having to assign explicit names to all :class:`.Constraint`
302
and :class:`.Index` objects, automated naming schemes can be constructed
303
using events. This approach has the advantage that constraints will get
304
a consistent naming scheme without the need for explicit name parameters
305
throughout the code, and also that the convention takes place just as well
306
for those constraints and indexes produced by the :paramref:`.Column.unique`
307
and :paramref:`.Column.index` parameters. As of SQLAlchemy 0.9.2 this
308
event-based approach is included, and can be configured using the argument
309
:paramref:`.MetaData.naming_convention`.
311
:paramref:`.MetaData.naming_convention` refers to a dictionary which accepts
312
the :class:`.Index` class or individual :class:`.Constraint` classes as keys,
313
and Python string templates as values. It also accepts a series of
314
string-codes as alternative keys, ``"fk"``, ``"pk"``,
315
``"ix"``, ``"ck"``, ``"uq"`` for foreign key, primary key, index,
316
check, and unique constraint, respectively. The string templates in this
317
dictionary are used whenever a constraint or index is associated with this
318
:class:`.MetaData` object that does not have an existing name given (including
319
one exception case where an existing name can be further embellished).
321
An example naming convention that suits basic cases is as follows::
324
"ix": 'ix_%(column_0_label)s',
325
"uq": "uq_%(table_name)s_%(column_0_name)s",
326
"ck": "ck_%(table_name)s_%(constraint_name)s",
327
"fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
328
"pk": "pk_%(table_name)s"
331
metadata = MetaData(naming_convention=convention)
333
The above convention will establish names for all constraints within
334
the target :class:`.MetaData` collection.
335
For example, we can observe the name produced when we create an unnamed
336
:class:`.UniqueConstraint`::
338
>>> user_table = Table('user', metadata,
339
... Column('id', Integer, primary_key=True),
340
... Column('name', String(30), nullable=False),
341
... UniqueConstraint('name')
343
>>> list(user_table.constraints)[1].name
346
This same feature takes effect even if we just use the :paramref:`.Column.unique`
349
>>> user_table = Table('user', metadata,
350
... Column('id', Integer, primary_key=True),
351
... Column('name', String(30), nullable=False, unique=True)
353
>>> list(user_table.constraints)[1].name
356
A key advantage to the naming convention approach is that the names are established
357
at Python construction time, rather than at DDL emit time. The effect this has
358
when using Alembic's ``--autogenerate`` feature is that the naming convention
359
will be explicit when a new migration script is generated::
362
op.create_unique_constraint("uq_user_name", "user", ["name"])
364
The above ``"uq_user_name"`` string was copied from the :class:`.UniqueConstraint`
365
object that ``--autogenerate`` located in our metadata.
367
The default value for :paramref:`.MetaData.naming_convention` handles
368
the long-standing SQLAlchemy behavior of assigning a name to a :class:`.Index`
369
object that is created using the :paramref:`.Column.index` parameter::
371
>>> from sqlalchemy.sql.schema import DEFAULT_NAMING_CONVENTION
372
>>> DEFAULT_NAMING_CONVENTION
373
immutabledict({'ix': 'ix_%(column_0_label)s'})
375
The tokens available include ``%(table_name)s``,
376
``%(referred_table_name)s``, ``%(column_0_name)s``, ``%(column_0_label)s``,
377
``%(column_0_key)s``, ``%(referred_column_0_name)s``, and ``%(constraint_name)s``;
378
the documentation for :paramref:`.MetaData.naming_convention` describes each
379
individually. New tokens can also be added, by specifying an additional
380
token and a callable within the naming_convention dictionary. For example,
381
if we wanted to name our foreign key constraints using a GUID scheme,
382
we could do that as follows::
386
def fk_guid(constraint, table):
390
element.parent.name for element in constraint.elements
392
element.target_fullname for element in constraint.elements
394
guid = uuid.uuid5(uuid.NAMESPACE_OID, "_".join(str_tokens).encode('ascii'))
399
"ix": 'ix_%(column_0_label)s',
400
"fk": "fk_%(fk_guid)s",
403
Above, when we create a new :class:`.ForeignKeyConstraint`, we will get a
406
>>> metadata = MetaData(naming_convention=convention)
408
>>> user_table = Table('user', metadata,
409
... Column('id', Integer, primary_key=True),
410
... Column('version', Integer, primary_key=True),
411
... Column('data', String(30))
413
>>> address_table = Table('address', metadata,
414
... Column('id', Integer, primary_key=True),
415
... Column('user_id', Integer),
416
... Column('user_version_id', Integer)
418
>>> fk = ForeignKeyConstraint(['user_id', 'user_version_id'],
419
... ['user.id', 'user.version'])
420
>>> address_table.append_constraint(fk)
422
fk_0cd51ab5-8d70-56e8-a83c-86661737766d
426
:paramref:`.MetaData.naming_convention` - for additional usage details
427
as well as a listing of all avaiable naming components.
429
:ref:`alembic:tutorial_constraint_names` - in the Alembic documentation.
431
.. versionadded:: 0.9.2 Added the :paramref:`.MetaData.naming_convention` argument.
435
.. autoclass:: Constraint
438
.. autoclass:: CheckConstraint
442
.. autoclass:: ColumnCollectionConstraint
445
.. autoclass:: ForeignKey
450
.. autoclass:: ForeignKeyConstraint
455
.. autoclass:: PrimaryKeyConstraint
460
.. autoclass:: UniqueConstraint
464
.. autofunction:: sqlalchemy.schema.conv
471
Indexes can be created anonymously (using an auto-generated name ``ix_<column
472
label>``) for a single column using the inline ``index`` keyword on
473
:class:`~sqlalchemy.schema.Column`, which also modifies the usage of
474
``unique`` to apply the uniqueness to the index itself, instead of adding a
475
separate UNIQUE constraint. For indexes with specific names or which encompass
476
more than one column, use the :class:`~sqlalchemy.schema.Index` construct,
477
which requires a name.
479
Below we illustrate a :class:`~sqlalchemy.schema.Table` with several
480
:class:`~sqlalchemy.schema.Index` objects associated. The DDL for "CREATE
481
INDEX" is issued right after the create statements for the table:
483
.. sourcecode:: python+sql
486
mytable = Table('mytable', meta,
487
# an indexed column, with index "ix_mytable_col1"
488
Column('col1', Integer, index=True),
490
# a uniquely indexed column with index "ix_mytable_col2"
491
Column('col2', Integer, index=True, unique=True),
493
Column('col3', Integer),
494
Column('col4', Integer),
496
Column('col5', Integer),
497
Column('col6', Integer),
500
# place an index on col3, col4
501
Index('idx_col34', mytable.c.col3, mytable.c.col4)
503
# place a unique index on col5, col6
504
Index('myindex', mytable.c.col5, mytable.c.col6, unique=True)
506
{sql}mytable.create(engine)
507
CREATE TABLE mytable (
515
CREATE INDEX ix_mytable_col1 ON mytable (col1)
516
CREATE UNIQUE INDEX ix_mytable_col2 ON mytable (col2)
517
CREATE UNIQUE INDEX myindex ON mytable (col5, col6)
518
CREATE INDEX idx_col34 ON mytable (col3, col4){stop}
520
Note in the example above, the :class:`.Index` construct is created
521
externally to the table which it corresponds, using :class:`.Column`
522
objects directly. :class:`.Index` also supports
523
"inline" definition inside the :class:`.Table`, using string names to
527
mytable = Table('mytable', meta,
528
Column('col1', Integer),
530
Column('col2', Integer),
532
Column('col3', Integer),
533
Column('col4', Integer),
535
# place an index on col1, col2
536
Index('idx_col12', 'col1', 'col2'),
538
# place a unique index on col3, col4
539
Index('idx_col34', 'col3', 'col4', unique=True)
542
.. versionadded:: 0.7
543
Support of "inline" definition inside the :class:`.Table`
544
for :class:`.Index`\ .
546
The :class:`~sqlalchemy.schema.Index` object also supports its own ``create()`` method:
548
.. sourcecode:: python+sql
550
i = Index('someindex', mytable.c.col5)
551
{sql}i.create(engine)
552
CREATE INDEX someindex ON mytable (col5){stop}
554
.. _schema_indexes_functional:
559
:class:`.Index` supports SQL and function expressions, as supported by the
560
target backend. To create an index against a column using a descending
561
value, the :meth:`.ColumnElement.desc` modifier may be used::
563
from sqlalchemy import Index
565
Index('someindex', mytable.c.somecol.desc())
567
Or with a backend that supports functional indexes such as Postgresql,
568
a "case insensitive" index can be created using the ``lower()`` function::
570
from sqlalchemy import func, Index
572
Index('someindex', func.lower(mytable.c.somecol))
574
.. versionadded:: 0.8 :class:`.Index` supports SQL expressions and functions
575
as well as plain columns.
added
removed
doc/_sources/core/constraints.txt
