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 and ON DELETE
133
~~~~~~~~~~~~~~~~~~~~~~~
135
Most databases support *cascading* of foreign key values, that is the when a
136
parent row is updated the new value is placed in child rows, or when the
137
parent row is deleted all corresponding child rows are set to null or deleted.
138
In data definition language these are specified using phrases like "ON UPDATE
139
CASCADE", "ON DELETE CASCADE", and "ON DELETE SET NULL", corresponding to
140
foreign key constraints. The phrase after "ON UPDATE" or "ON DELETE" may also
141
other allow other phrases that are specific to the database in use. The
142
:class:`~sqlalchemy.schema.ForeignKey` and
143
:class:`~sqlalchemy.schema.ForeignKeyConstraint` objects support the
144
generation of this clause via the ``onupdate`` and ``ondelete`` keyword
145
arguments. The value is any string which will be output after the appropriate
146
"ON UPDATE" or "ON DELETE" phrase::
148
child = Table('child', meta,
149
Column('id', Integer,
150
ForeignKey('parent.id', onupdate="CASCADE", ondelete="CASCADE"),
155
composite = Table('composite', meta,
156
Column('id', Integer, primary_key=True),
157
Column('rev_id', Integer),
158
Column('note_id', Integer),
159
ForeignKeyConstraint(
160
['rev_id', 'note_id'],
161
['revisions.id', 'revisions.note_id'],
162
onupdate="CASCADE", ondelete="SET NULL"
166
Note that these clauses are not supported on SQLite, and require ``InnoDB``
167
tables when used with MySQL. They may also not be supported on other
174
Unique constraints can be created anonymously on a single column using the
175
``unique`` keyword on :class:`~sqlalchemy.schema.Column`. Explicitly named
176
unique constraints and/or those with multiple columns are created via the
177
:class:`~sqlalchemy.schema.UniqueConstraint` table-level construct.
179
.. sourcecode:: python+sql
182
mytable = Table('mytable', meta,
184
# per-column anonymous unique constraint
185
Column('col1', Integer, unique=True),
187
Column('col2', Integer),
188
Column('col3', Integer),
190
# explicit/composite unique constraint. 'name' is optional.
191
UniqueConstraint('col2', 'col3', name='uix_1')
197
Check constraints can be named or unnamed and can be created at the Column or
198
Table level, using the :class:`~sqlalchemy.schema.CheckConstraint` construct.
199
The text of the check constraint is passed directly through to the database,
200
so there is limited "database independent" behavior. Column level check
201
constraints generally should only refer to the column to which they are
202
placed, while table level constraints can refer to any columns in the table.
204
Note that some databases do not actively support check constraints such as
207
.. sourcecode:: python+sql
210
mytable = Table('mytable', meta,
212
# per-column CHECK constraint
213
Column('col1', Integer, CheckConstraint('col1>5')),
215
Column('col2', Integer),
216
Column('col3', Integer),
218
# table level CHECK constraint. 'name' is optional.
219
CheckConstraint('col2 > col3 + 5', name='check1')
222
{sql}mytable.create(engine)
223
CREATE TABLE mytable (
224
col1 INTEGER CHECK (col1>5),
227
CONSTRAINT check1 CHECK (col2 > col3 + 5)
230
Setting up Constraints when using the Declarative ORM Extension
231
----------------------------------------------------------------
233
The :class:`.Table` is the SQLAlchemy Core construct that allows one to define
234
table metadata, which among other things can be used by the SQLAlchemy ORM
235
as a target to map a class. The :ref:`Declarative <declarative_toplevel>`
236
extension allows the :class:`.Table` object to be created automatically, given
237
the contents of the table primarily as a mapping of :class:`.Column` objects.
239
To apply table-level constraint objects such as :class:`.ForeignKeyConstraint`
240
to a table defined using Declarative, use the ``__table_args__`` attribute,
241
described at :ref:`declarative_table_args`.
245
.. autoclass:: Constraint
248
.. autoclass:: CheckConstraint
251
.. autoclass:: ColumnCollectionConstraint
254
.. autoclass:: ForeignKey
258
.. autoclass:: ForeignKeyConstraint
262
.. autoclass:: PrimaryKeyConstraint
265
.. autoclass:: UniqueConstraint
273
Indexes can be created anonymously (using an auto-generated name ``ix_<column
274
label>``) for a single column using the inline ``index`` keyword on
275
:class:`~sqlalchemy.schema.Column`, which also modifies the usage of
276
``unique`` to apply the uniqueness to the index itself, instead of adding a
277
separate UNIQUE constraint. For indexes with specific names or which encompass
278
more than one column, use the :class:`~sqlalchemy.schema.Index` construct,
279
which requires a name.
281
Below we illustrate a :class:`~sqlalchemy.schema.Table` with several
282
:class:`~sqlalchemy.schema.Index` objects associated. The DDL for "CREATE
283
INDEX" is issued right after the create statements for the table:
285
.. sourcecode:: python+sql
288
mytable = Table('mytable', meta,
289
# an indexed column, with index "ix_mytable_col1"
290
Column('col1', Integer, index=True),
292
# a uniquely indexed column with index "ix_mytable_col2"
293
Column('col2', Integer, index=True, unique=True),
295
Column('col3', Integer),
296
Column('col4', Integer),
298
Column('col5', Integer),
299
Column('col6', Integer),
302
# place an index on col3, col4
303
Index('idx_col34', mytable.c.col3, mytable.c.col4)
305
# place a unique index on col5, col6
306
Index('myindex', mytable.c.col5, mytable.c.col6, unique=True)
308
{sql}mytable.create(engine)
309
CREATE TABLE mytable (
317
CREATE INDEX ix_mytable_col1 ON mytable (col1)
318
CREATE UNIQUE INDEX ix_mytable_col2 ON mytable (col2)
319
CREATE UNIQUE INDEX myindex ON mytable (col5, col6)
320
CREATE INDEX idx_col34 ON mytable (col3, col4){stop}
322
Note in the example above, the :class:`.Index` construct is created
323
externally to the table which it corresponds, using :class:`.Column`
324
objects directly. :class:`.Index` also supports
325
"inline" definition inside the :class:`.Table`, using string names to
329
mytable = Table('mytable', meta,
330
Column('col1', Integer),
332
Column('col2', Integer),
334
Column('col3', Integer),
335
Column('col4', Integer),
337
# place an index on col1, col2
338
Index('idx_col12', 'col1', 'col2'),
340
# place a unique index on col3, col4
341
Index('idx_col34', 'col3', 'col4', unique=True)
344
.. versionadded:: 0.7
345
Support of "inline" definition inside the :class:`.Table`
346
for :class:`.Index`\ .
348
The :class:`~sqlalchemy.schema.Index` object also supports its own ``create()`` method:
350
.. sourcecode:: python+sql
352
i = Index('someindex', mytable.c.col5)
353
{sql}i.create(engine)
354
CREATE INDEX someindex ON mytable (col5){stop}
356
.. _schema_indexes_functional:
361
:class:`.Index` supports SQL and function expressions, as supported by the
362
target backend. To create an index against a column using a descending
363
value, the :meth:`.ColumnElement.desc` modifier may be used::
365
from sqlalchemy import Index
367
Index('someindex', mytable.c.somecol.desc())
369
Or with a backend that supports functional indexes such as Postgresql,
370
a "case insensitive" index can be created using the ``lower()`` function::
372
from sqlalchemy import func, Index
374
Index('someindex', func.lower(mytable.c.somecol))
376
.. versionadded:: 0.8 :class:`.Index` supports SQL expressions and functions
377
as well as plain columns.
added
removed
doc/_sources/core/constraints.txt
