Defining Constraints and Indexes
This section will discuss SQL constraints and indexes. In SQLAlchemy
the key classes include ForeignKeyConstraint and Index.
Defining Foreign Keys
A foreign key in SQL is a table-level construct that constrains one or more
columns in that table to only allow values that are present in a different set
of columns, typically but not always located on a different table. We call the
columns which are constrained the foreign key columns and the columns which
they are constrained towards the referenced columns. The referenced columns
almost always define the primary key for their owning table, though there are
exceptions to this. The foreign key is the “joint” that connects together
pairs of rows which have a relationship with each other, and SQLAlchemy
assigns very deep importance to this concept in virtually every area of its
operation.
In SQLAlchemy as well as in DDL, foreign key constraints can be defined as
additional attributes within the table clause, or for single-column foreign
keys they may optionally be specified within the definition of a single
column. The single column foreign key is more common, and at the column level
is specified by constructing a ForeignKey object
as an argument to a Column object:
user_preference = Table('user_preference', metadata,
Column('pref_id', Integer, primary_key=True),
Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False),
Column('pref_name', String(40), nullable=False),
Column('pref_value', String(100))
)
Above, we define a new table user_preference for which each row must
contain a value in the user_id column that also exists in the user
table’s user_id column.
The argument to ForeignKey is most commonly a
string of the form <tablename>.<columnname>, or for a table in a remote
schema or “owner” of the form <schemaname>.<tablename>.<columnname>. It may
also be an actual Column object, which as we’ll
see later is accessed from an existing Table
object via its c collection:
ForeignKey(user.c.user_id)
The advantage to using a string is that the in-python linkage between user
and user_preference is resolved only when first needed, so that table
objects can be easily spread across multiple modules and defined in any order.
Foreign keys may also be defined at the table level, using the
ForeignKeyConstraint object. This object can
describe a single- or multi-column foreign key. A multi-column foreign key is
known as a composite foreign key, and almost always references a table that
has a composite primary key. Below we define a table invoice which has a
composite primary key:
invoice = Table('invoice', metadata,
Column('invoice_id', Integer, primary_key=True),
Column('ref_num', Integer, primary_key=True),
Column('description', String(60), nullable=False)
)
And then a table invoice_item with a composite foreign key referencing
invoice:
invoice_item = Table('invoice_item', metadata,
Column('item_id', Integer, primary_key=True),
Column('item_name', String(60), nullable=False),
Column('invoice_id', Integer, nullable=False),
Column('ref_num', Integer, nullable=False),
ForeignKeyConstraint(['invoice_id', 'ref_num'], ['invoice.invoice_id', 'invoice.ref_num'])
)
It’s important to note that the
ForeignKeyConstraint is the only way to define a
composite foreign key. While we could also have placed individual
ForeignKey objects on both the
invoice_item.invoice_id and invoice_item.ref_num columns, SQLAlchemy
would not be aware that these two values should be paired together - it would
be two individual foreign key constraints instead of a single composite
foreign key referencing two columns.
Creating/Dropping Foreign Key Constraints via ALTER
In all the above examples, the ForeignKey object
causes the “REFERENCES” keyword to be added inline to a column definition
within a “CREATE TABLE” statement when
create_all() is issued, and
ForeignKeyConstraint invokes the “CONSTRAINT”
keyword inline with “CREATE TABLE”. There are some cases where this is
undesirable, particularly when two tables reference each other mutually, each
with a foreign key referencing the other. In such a situation at least one of
the foreign key constraints must be generated after both tables have been
built. To support such a scheme, ForeignKey and
ForeignKeyConstraint offer the flag
use_alter=True. When using this flag, the constraint will be generated
using a definition similar to “ALTER TABLE <tablename> ADD CONSTRAINT <name>
...”. Since a name is required, the name attribute must also be specified.
For example:
node = Table('node', meta,
Column('node_id', Integer, primary_key=True),
Column('primary_element', Integer,
ForeignKey('element.element_id', use_alter=True, name='fk_node_element_id')
)
)
element = Table('element', meta,
Column('element_id', Integer, primary_key=True),
Column('parent_node_id', Integer),
ForeignKeyConstraint(
['parent_node_id'],
['node.node_id'],
use_alter=True,
name='fk_element_parent_node_id'
)
)
ON UPDATE and ON DELETE
Most databases support cascading of foreign key values, that is the when a
parent row is updated the new value is placed in child rows, or when the
parent row is deleted all corresponding child rows are set to null or deleted.
In data definition language these are specified using phrases like “ON UPDATE
CASCADE”, “ON DELETE CASCADE”, and “ON DELETE SET NULL”, corresponding to
foreign key constraints. The phrase after “ON UPDATE” or “ON DELETE” may also
other allow other phrases that are specific to the database in use. The
ForeignKey and
ForeignKeyConstraint objects support the
generation of this clause via the onupdate and ondelete keyword
arguments. The value is any string which will be output after the appropriate
“ON UPDATE” or “ON DELETE” phrase:
child = Table('child', meta,
Column('id', Integer,
ForeignKey('parent.id', onupdate="CASCADE", ondelete="CASCADE"),
primary_key=True
)
)
composite = Table('composite', meta,
Column('id', Integer, primary_key=True),
Column('rev_id', Integer),
Column('note_id', Integer),
ForeignKeyConstraint(
['rev_id', 'note_id'],
['revisions.id', 'revisions.note_id'],
onupdate="CASCADE", ondelete="SET NULL"
)
)
Note that these clauses are not supported on SQLite, and require InnoDB
tables when used with MySQL. They may also not be supported on other
databases.
UNIQUE Constraint
Unique constraints can be created anonymously on a single column using the
unique keyword on Column. Explicitly named
unique constraints and/or those with multiple columns are created via the
UniqueConstraint table-level construct.
from sqlalchemy import UniqueConstraint
meta = MetaData()
mytable = Table('mytable', meta,
# per-column anonymous unique constraint
Column('col1', Integer, unique=True),
Column('col2', Integer),
Column('col3', Integer),
# explicit/composite unique constraint. 'name' is optional.
UniqueConstraint('col2', 'col3', name='uix_1')
)
CHECK Constraint
Check constraints can be named or unnamed and can be created at the Column or
Table level, using the CheckConstraint construct.
The text of the check constraint is passed directly through to the database,
so there is limited “database independent” behavior. Column level check
constraints generally should only refer to the column to which they are
placed, while table level constraints can refer to any columns in the table.
Note that some databases do not actively support check constraints such as
MySQL.
from sqlalchemy import CheckConstraint
meta = MetaData()
mytable = Table('mytable', meta,
# per-column CHECK constraint
Column('col1', Integer, CheckConstraint('col1>5')),
Column('col2', Integer),
Column('col3', Integer),
# table level CHECK constraint. 'name' is optional.
CheckConstraint('col2 > col3 + 5', name='check1')
)
sqlmytable.create(engine)
PRIMARY KEY Constraint
The primary key constraint of any Table object is implicitly
present, based on the Column objects that are marked with the
Column.primary_key flag. The PrimaryKeyConstraint
object provides explicit access to this constraint, which includes the
option of being configured directly:
from sqlalchemy import PrimaryKeyConstraint
my_table = Table('mytable', metadata,
Column('id', Integer),
Column('version_id', Integer),
Column('data', String(50)),
PrimaryKeyConstraint('id', 'version_id', name='mytable_pk')
)
Setting up Constraints when using the Declarative ORM Extension
The Table is the SQLAlchemy Core construct that allows one to define
table metadata, which among other things can be used by the SQLAlchemy ORM
as a target to map a class. The Declarative
extension allows the Table object to be created automatically, given
the contents of the table primarily as a mapping of Column objects.
To apply table-level constraint objects such as ForeignKeyConstraint
to a table defined using Declarative, use the __table_args__ attribute,
described at Table Configuration.
Configuring Constraint Naming Conventions
Relational databases typically assign explicit names to all constraints and
indexes. In the common case that a table is created using CREATE TABLE
where constraints such as CHECK, UNIQUE, and PRIMARY KEY constraints are
produced inline with the table definition, the database usually has a system
in place in which names are automatically assigned to these constraints, if
a name is not otherwise specified. When an existing database table is altered
in a database using a command such as ALTER TABLE, this command typically
needs to specify expicit names for new constraints as well as be able to
specify the name of an existing constraint that is to be dropped or modified.
Constraints can be named explicitly using the Constraint.name parameter,
and for indexes the Index.name parameter. However, in the
case of constraints this parameter is optional. There are also the use
cases of using the Column.unique and Column.index
parameters which create UniqueConstraint and Index objects
without an explicit name being specified.
The use case of alteration of existing tables and constraints can be handled
by schema migration tools such as Alembic.
However, neither Alembic nor SQLAlchemy currently create names for constraint
objects where the name is otherwise unspecified, leading to the case where
being able to alter existing constraints means that one must reverse-engineer
the naming system used by the relational database to auto-assign names,
or that care must be taken to ensure that all constraints are named.
In contrast to having to assign explicit names to all Constraint
and Index objects, automated naming schemes can be constructed
using events. This approach has the advantage that constraints will get
a consistent naming scheme without the need for explicit name parameters
throughout the code, and also that the convention takes place just as well
for those constraints and indexes produced by the Column.unique
and Column.index parameters. As of SQLAlchemy 0.9.2 this
event-based approach is included, and can be configured using the argument
MetaData.naming_convention.
MetaData.naming_convention refers to a dictionary which accepts
the Index class or individual Constraint classes as keys,
and Python string templates as values. It also accepts a series of
string-codes as alternative keys, "fk", "pk",
"ix", "ck", "uq" for foreign key, primary key, index,
check, and unique constraint, respectively. The string templates in this
dictionary are used whenever a constraint or index is associated with this
MetaData object that does not have an existing name given (including
one exception case where an existing name can be further embellished).
An example naming convention that suits basic cases is as follows:
convention = {
"ix": 'ix_%(column_0_label)s',
"uq": "uq_%(table_name)s_%(column_0_name)s",
"ck": "ck_%(table_name)s_%(constraint_name)s",
"fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
"pk": "pk_%(table_name)s"
}
metadata = MetaData(naming_convention=convention)
The above convention will establish names for all constraints within
the target MetaData collection.
For example, we can observe the name produced when we create an unnamed
UniqueConstraint:
>>> user_table = Table('user', metadata,
... Column('id', Integer, primary_key=True),
... Column('name', String(30), nullable=False),
... UniqueConstraint('name')
... )
>>> list(user_table.constraints)[1].name
'uq_user_name'
This same feature takes effect even if we just use the Column.unique
flag:
>>> user_table = Table('user', metadata,
... Column('id', Integer, primary_key=True),
... Column('name', String(30), nullable=False, unique=True)
... )
>>> list(user_table.constraints)[1].name
'uq_user_name'
A key advantage to the naming convention approach is that the names are established
at Python construction time, rather than at DDL emit time. The effect this has
when using Alembic’s --autogenerate feature is that the naming convention
will be explicit when a new migration script is generated:
def upgrade():
op.create_unique_constraint("uq_user_name", "user", ["name"])
The above "uq_user_name" string was copied from the UniqueConstraint
object that --autogenerate located in our metadata.
The default value for MetaData.naming_convention handles
the long-standing SQLAlchemy behavior of assigning a name to a Index
object that is created using the Column.index parameter:
>>> from sqlalchemy.sql.schema import DEFAULT_NAMING_CONVENTION
>>> DEFAULT_NAMING_CONVENTION
immutabledict({'ix': 'ix_%(column_0_label)s'})
The tokens available include %(table_name)s,
%(referred_table_name)s, %(column_0_name)s, %(column_0_label)s,
%(column_0_key)s, %(referred_column_0_name)s, and %(constraint_name)s;
the documentation for MetaData.naming_convention describes each
individually. New tokens can also be added, by specifying an additional
token and a callable within the naming_convention dictionary. For example,
if we wanted to name our foreign key constraints using a GUID scheme,
we could do that as follows:
import uuid
def fk_guid(constraint, table):
str_tokens = [
table.name,
] + [
element.parent.name for element in constraint.elements
] + [
element.target_fullname for element in constraint.elements
]
guid = uuid.uuid5(uuid.NAMESPACE_OID, "_".join(str_tokens).encode('ascii'))
return str(guid)
convention = {
"fk_guid": fk_guid,
"ix": 'ix_%(column_0_label)s',
"fk": "fk_%(fk_guid)s",
}
Above, when we create a new ForeignKeyConstraint, we will get a
name as follows:
>>> metadata = MetaData(naming_convention=convention)
>>> user_table = Table('user', metadata,
... Column('id', Integer, primary_key=True),
... Column('version', Integer, primary_key=True),
... Column('data', String(30))
... )
>>> address_table = Table('address', metadata,
... Column('id', Integer, primary_key=True),
... Column('user_id', Integer),
... Column('user_version_id', Integer)
... )
>>> fk = ForeignKeyConstraint(['user_id', 'user_version_id'],
... ['user.id', 'user.version'])
>>> address_table.append_constraint(fk)
>>> fk.name
fk_0cd51ab5-8d70-56e8-a83c-86661737766d
Constraints API
-
class sqlalchemy.schema.Constraint(name=None, deferrable=None, initially=None, _create_rule=None, **dialect_kw)
Bases: sqlalchemy.sql.base.DialectKWArgs, sqlalchemy.schema.SchemaItem
A table-level SQL constraint.
-
__init__(name=None, deferrable=None, initially=None, _create_rule=None, **dialect_kw)
Create a SQL constraint.
Parameters: |
- name – Optional, the in-database name of this Constraint.
- deferrable – Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
issuing DDL for this constraint.
- initially – Optional string. If set, emit INITIALLY <value> when issuing DDL
for this constraint.
- _create_rule –
a callable which is passed the DDLCompiler object during
compilation. Returns True or False to signal inline generation of
this Constraint.
The AddConstraint and DropConstraint DDL constructs provide
DDLElement’s more comprehensive “conditional DDL” approach that is
passed a database connection when DDL is being issued. _create_rule
is instead called during any CREATE TABLE compilation, where there
may not be any transaction/connection in progress. However, it
allows conditional compilation of the constraint even for backends
which do not support addition of constraints through ALTER TABLE,
which currently includes SQLite.
_create_rule is used by some types to create constraints.
Currently, its call signature is subject to change at any time.
- **dialect_kw – Additional keyword arguments are dialect
specific, and passed in the form <dialectname>_<argname>. See
the documentation regarding an individual dialect at
Dialects for detail on documented arguments.
|
-
class sqlalchemy.schema.CheckConstraint(sqltext, name=None, deferrable=None, initially=None, table=None, _create_rule=None, _autoattach=True)
Bases: sqlalchemy.schema.Constraint
A table- or column-level CHECK constraint.
Can be included in the definition of a Table or Column.
-
__init__(sqltext, name=None, deferrable=None, initially=None, table=None, _create_rule=None, _autoattach=True)
Construct a CHECK constraint.
Parameters: |
- sqltext –
A string containing the constraint definition, which will be used
verbatim, or a SQL expression construct. If given as a string,
the object is converted to a Text object. If the textual
string includes a colon character, escape this using a backslash:
CheckConstraint(r"foo ~ E'a(?\:b|c)d")
- name – Optional, the in-database name of the constraint.
- deferrable – Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
issuing DDL for this constraint.
- initially – Optional string. If set, emit INITIALLY <value> when issuing DDL
for this constraint.
|
-
classmethod argument_for(dialect_name, argument_name, default)
-
Add a new kind of dialect-specific keyword argument for this class.
E.g.:
Index.argument_for("mydialect", "length", None)
some_index = Index('a', 'b', mydialect_length=5)
The DialectKWArgs.argument_for() method is a per-argument
way adding extra arguments to the
DefaultDialect.construct_arguments dictionary. This
dictionary provides a list of argument names accepted by various
schema-level constructs on behalf of a dialect.
New dialects should typically specify this dictionary all at once as a
data member of the dialect class. The use case for ad-hoc addition of
argument names is typically for end-user code that is also using
a custom compilation scheme which consumes the additional arguments.
Parameters: |
- dialect_name – name of a dialect. The dialect must be
locatable, else a NoSuchModuleError is raised. The
dialect must also include an existing
DefaultDialect.construct_arguments collection, indicating
that it participates in the keyword-argument validation and default
system, else ArgumentError is raised. If the dialect does
not include this collection, then any keyword argument can be
specified on behalf of this dialect already. All dialects packaged
within SQLAlchemy include this collection, however for third party
dialects, support may vary.
- argument_name – name of the parameter.
- default – default value of the parameter.
|
-
dialect_kwargs
-
A collection of keyword arguments specified as dialect-specific
options to this construct.
The arguments are present here in their original <dialect>_<kwarg>
format. Only arguments that were actually passed are included;
unlike the DialectKWArgs.dialect_options collection, which
contains all options known by this dialect including defaults.
The collection is also writable; keys are accepted of the
form <dialect>_<kwarg> where the value will be assembled
into the list of options.
-
dialect_options
-
A collection of keyword arguments specified as dialect-specific
options to this construct.
This is a two-level nested registry, keyed to <dialect_name>
and <argument_name>. For example, the postgresql_where
argument would be locatable as:
arg = my_object.dialect_options['postgresql']['where']
-
get_children(**kwargs)
-
used to allow SchemaVisitor access
-
info
-
Info dictionary associated with the object, allowing user-defined
data to be associated with this SchemaItem.
The dictionary is automatically generated when first accessed.
It can also be specified in the constructor of some objects,
such as Table and Column.
-
kwargs
-
A synonym for DialectKWArgs.dialect_kwargs.
-
quote
-
Return the value of the quote flag passed
to this schema object, for those schema items which
have a name field.
Deprecated since version 0.9: Use <obj>.name.quote
-
class sqlalchemy.schema.ColumnCollectionConstraint(*columns, **kw)
Bases: sqlalchemy.schema.ColumnCollectionMixin, sqlalchemy.schema.Constraint
A constraint that proxies a ColumnCollection.
-
__init__(*columns, **kw)
Parameters: |
- *columns – A sequence of column names or Column objects.
- name – Optional, the in-database name of this constraint.
- deferrable – Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
issuing DDL for this constraint.
- initially – Optional string. If set, emit INITIALLY <value> when issuing DDL
for this constraint.
- **kw – other keyword arguments including dialect-specific
arguments are propagated to the Constraint superclass.
|
-
class sqlalchemy.schema.ForeignKey(column, _constraint=None, use_alter=False, name=None, onupdate=None, ondelete=None, deferrable=None, initially=None, link_to_name=False, match=None, **dialect_kw)
Bases: sqlalchemy.sql.base.DialectKWArgs, sqlalchemy.schema.SchemaItem
Defines a dependency between two columns.
ForeignKey is specified as an argument to a Column object,
e.g.:
t = Table("remote_table", metadata,
Column("remote_id", ForeignKey("main_table.id"))
)
Note that ForeignKey is only a marker object that defines
a dependency between two columns. The actual constraint
is in all cases represented by the ForeignKeyConstraint
object. This object will be generated automatically when
a ForeignKey is associated with a Column which
in turn is associated with a Table. Conversely,
when ForeignKeyConstraint is applied to a Table,
ForeignKey markers are automatically generated to be
present on each associated Column, which are also
associated with the constraint object.
Note that you cannot define a “composite” foreign key constraint,
that is a constraint between a grouping of multiple parent/child
columns, using ForeignKey objects. To define this grouping,
the ForeignKeyConstraint object must be used, and applied
to the Table. The associated ForeignKey objects
are created automatically.
The ForeignKey objects associated with an individual
Column object are available in the foreign_keys collection
of that column.
Further examples of foreign key configuration are in
metadata_foreignkeys.
-
__init__(column, _constraint=None, use_alter=False, name=None, onupdate=None, ondelete=None, deferrable=None, initially=None, link_to_name=False, match=None, **dialect_kw)
Construct a column-level FOREIGN KEY.
The ForeignKey object when constructed generates a
ForeignKeyConstraint which is associated with the parent
Table object’s collection of constraints.
Parameters: |
- column –
A single target column for the key relationship. A
Column object or a column name as a string:
tablename.columnkey or schema.tablename.columnkey.
columnkey is the key which has been assigned to the column
(defaults to the column name itself), unless link_to_name is
True in which case the rendered name of the column is used.
New in version 0.7.4: Note that if the schema name is not included, and the
underlying MetaData has a “schema”, that value will
be used.
- name – Optional string. An in-database name for the key if
constraint is not provided.
- onupdate – Optional string. If set, emit ON UPDATE <value> when
issuing DDL for this constraint. Typical values include CASCADE,
DELETE and RESTRICT.
- ondelete – Optional string. If set, emit ON DELETE <value> when
issuing DDL for this constraint. Typical values include CASCADE,
DELETE and RESTRICT.
- deferrable – Optional bool. If set, emit DEFERRABLE or NOT
DEFERRABLE when issuing DDL for this constraint.
- initially – Optional string. If set, emit INITIALLY <value> when
issuing DDL for this constraint.
- link_to_name – if True, the string name given in column is
the rendered name of the referenced column, not its locally
assigned key.
- use_alter – passed to the underlying
ForeignKeyConstraint to indicate the constraint should
be generated/dropped externally from the CREATE TABLE/ DROP TABLE
statement. See that classes’ constructor for details.
- match – Optional string. If set, emit MATCH <value> when issuing
DDL for this constraint. Typical values include SIMPLE, PARTIAL
and FULL.
- **dialect_kw –
Additional keyword arguments are dialect
specific, and passed in the form <dialectname>_<argname>. The
arguments are ultimately handled by a corresponding
ForeignKeyConstraint. See the documentation regarding
an individual dialect at Dialects for detail on
documented arguments.
|
-
classmethod argument_for(dialect_name, argument_name, default)
-
Add a new kind of dialect-specific keyword argument for this class.
E.g.:
Index.argument_for("mydialect", "length", None)
some_index = Index('a', 'b', mydialect_length=5)
The DialectKWArgs.argument_for() method is a per-argument
way adding extra arguments to the
DefaultDialect.construct_arguments dictionary. This
dictionary provides a list of argument names accepted by various
schema-level constructs on behalf of a dialect.
New dialects should typically specify this dictionary all at once as a
data member of the dialect class. The use case for ad-hoc addition of
argument names is typically for end-user code that is also using
a custom compilation scheme which consumes the additional arguments.
Parameters: |
- dialect_name – name of a dialect. The dialect must be
locatable, else a NoSuchModuleError is raised. The
dialect must also include an existing
DefaultDialect.construct_arguments collection, indicating
that it participates in the keyword-argument validation and default
system, else ArgumentError is raised. If the dialect does
not include this collection, then any keyword argument can be
specified on behalf of this dialect already. All dialects packaged
within SQLAlchemy include this collection, however for third party
dialects, support may vary.
- argument_name – name of the parameter.
- default – default value of the parameter.
|
-
column
Return the target Column referenced by this
ForeignKey.
If no target column has been established, an exception
is raised.
Changed in version 0.9.0: Foreign key target column resolution now occurs as soon as both
the ForeignKey object and the remote Column to which it refers
are both associated with the same MetaData object.
-
copy(schema=None)
Produce a copy of this ForeignKey object.
The new ForeignKey will not be bound
to any Column.
This method is usually used by the internal
copy procedures of Column, Table,
and MetaData.
Parameters: | schema – The returned ForeignKey will
reference the original table and column name, qualified
by the given string schema name. |
-
dialect_kwargs
-
A collection of keyword arguments specified as dialect-specific
options to this construct.
The arguments are present here in their original <dialect>_<kwarg>
format. Only arguments that were actually passed are included;
unlike the DialectKWArgs.dialect_options collection, which
contains all options known by this dialect including defaults.
The collection is also writable; keys are accepted of the
form <dialect>_<kwarg> where the value will be assembled
into the list of options.
-
dialect_options
-
A collection of keyword arguments specified as dialect-specific
options to this construct.
This is a two-level nested registry, keyed to <dialect_name>
and <argument_name>. For example, the postgresql_where
argument would be locatable as:
arg = my_object.dialect_options['postgresql']['where']
-
get_children(**kwargs)
-
used to allow SchemaVisitor access
-
get_referent(table)
Return the Column in the given Table
referenced by this ForeignKey.
Returns None if this ForeignKey does not reference the given
Table.
-
info
-
Info dictionary associated with the object, allowing user-defined
data to be associated with this SchemaItem.
The dictionary is automatically generated when first accessed.
It can also be specified in the constructor of some objects,
such as Table and Column.
-
kwargs
-
A synonym for DialectKWArgs.dialect_kwargs.
-
quote
-
Return the value of the quote flag passed
to this schema object, for those schema items which
have a name field.
Deprecated since version 0.9: Use <obj>.name.quote
-
references(table)
Return True if the given Table is referenced by this
ForeignKey.
-
target_fullname
Return a string based ‘column specification’ for this
ForeignKey.
This is usually the equivalent of the string-based “tablename.colname”
argument first passed to the object’s constructor.
-
class sqlalchemy.schema.ForeignKeyConstraint(columns, refcolumns, name=None, onupdate=None, ondelete=None, deferrable=None, initially=None, use_alter=False, link_to_name=False, match=None, table=None, **dialect_kw)
Bases: sqlalchemy.schema.Constraint
A table-level FOREIGN KEY constraint.
Defines a single column or composite FOREIGN KEY ... REFERENCES
constraint. For a no-frills, single column foreign key, adding a
ForeignKey to the definition of a Column is a
shorthand equivalent for an unnamed, single column
ForeignKeyConstraint.
Examples of foreign key configuration are in metadata_foreignkeys.
-
__init__(columns, refcolumns, name=None, onupdate=None, ondelete=None, deferrable=None, initially=None, use_alter=False, link_to_name=False, match=None, table=None, **dialect_kw)
Construct a composite-capable FOREIGN KEY.
-
classmethod argument_for(dialect_name, argument_name, default)
-
Add a new kind of dialect-specific keyword argument for this class.
E.g.:
Index.argument_for("mydialect", "length", None)
some_index = Index('a', 'b', mydialect_length=5)
The DialectKWArgs.argument_for() method is a per-argument
way adding extra arguments to the
DefaultDialect.construct_arguments dictionary. This
dictionary provides a list of argument names accepted by various
schema-level constructs on behalf of a dialect.
New dialects should typically specify this dictionary all at once as a
data member of the dialect class. The use case for ad-hoc addition of
argument names is typically for end-user code that is also using
a custom compilation scheme which consumes the additional arguments.
Parameters: |
- dialect_name – name of a dialect. The dialect must be
locatable, else a NoSuchModuleError is raised. The
dialect must also include an existing
DefaultDialect.construct_arguments collection, indicating
that it participates in the keyword-argument validation and default
system, else ArgumentError is raised. If the dialect does
not include this collection, then any keyword argument can be
specified on behalf of this dialect already. All dialects packaged
within SQLAlchemy include this collection, however for third party
dialects, support may vary.
- argument_name – name of the parameter.
- default – default value of the parameter.
|
-
dialect_kwargs
-
A collection of keyword arguments specified as dialect-specific
options to this construct.
The arguments are present here in their original <dialect>_<kwarg>
format. Only arguments that were actually passed are included;
unlike the DialectKWArgs.dialect_options collection, which
contains all options known by this dialect including defaults.
The collection is also writable; keys are accepted of the
form <dialect>_<kwarg> where the value will be assembled
into the list of options.
-
dialect_options
-
A collection of keyword arguments specified as dialect-specific
options to this construct.
This is a two-level nested registry, keyed to <dialect_name>
and <argument_name>. For example, the postgresql_where
argument would be locatable as:
arg = my_object.dialect_options['postgresql']['where']
-
get_children(**kwargs)
-
used to allow SchemaVisitor access
-
info
-
Info dictionary associated with the object, allowing user-defined
data to be associated with this SchemaItem.
The dictionary is automatically generated when first accessed.
It can also be specified in the constructor of some objects,
such as Table and Column.
-
kwargs
-
A synonym for DialectKWArgs.dialect_kwargs.
-
quote
-
Return the value of the quote flag passed
to this schema object, for those schema items which
have a name field.
Deprecated since version 0.9: Use <obj>.name.quote
-
class sqlalchemy.schema.PrimaryKeyConstraint(*columns, **kw)
Bases: sqlalchemy.schema.ColumnCollectionConstraint
A table-level PRIMARY KEY constraint.
The PrimaryKeyConstraint object is present automatically
on any Table object; it is assigned a set of
Column objects corresponding to those marked with
the Column.primary_key flag:
>>> my_table = Table('mytable', metadata,
... Column('id', Integer, primary_key=True),
... Column('version_id', Integer, primary_key=True),
... Column('data', String(50))
... )
>>> my_table.primary_key
PrimaryKeyConstraint(
Column('id', Integer(), table=<mytable>,
primary_key=True, nullable=False),
Column('version_id', Integer(), table=<mytable>,
primary_key=True, nullable=False)
)
The primary key of a Table can also be specified by using
a PrimaryKeyConstraint object explicitly; in this mode of usage,
the “name” of the constraint can also be specified, as well as other
options which may be recognized by dialects:
my_table = Table('mytable', metadata,
Column('id', Integer),
Column('version_id', Integer),
Column('data', String(50)),
PrimaryKeyConstraint('id', 'version_id',
name='mytable_pk')
)
The two styles of column-specification should generally not be mixed.
An warning is emitted if the columns present in the
PrimaryKeyConstraint
don’t match the columns that were marked as primary_key=True, if both
are present; in this case, the columns are taken strictly from the
PrimaryKeyConstraint declaration, and those columns otherwise
marked as primary_key=True are ignored. This behavior is intended to
be backwards compatible with previous behavior.
Changed in version 0.9.2: Using a mixture of columns within a
PrimaryKeyConstraint in addition to columns marked as
primary_key=True now emits a warning if the lists don’t match.
The ultimate behavior of ignoring those columns marked with the flag
only is currently maintained for backwards compatibility; this warning
may raise an exception in a future release.
For the use case where specific options are to be specified on the
PrimaryKeyConstraint, but the usual style of using
primary_key=True flags is still desirable, an empty
PrimaryKeyConstraint may be specified, which will take on the
primary key column collection from the Table based on the
flags:
my_table = Table('mytable', metadata,
Column('id', Integer, primary_key=True),
Column('version_id', Integer, primary_key=True),
Column('data', String(50)),
PrimaryKeyConstraint(name='mytable_pk',
mssql_clustered=True)
)
New in version 0.9.2: an empty PrimaryKeyConstraint may now
be specified for the purposes of establishing keyword arguments with
the constraint, independently of the specification of “primary key”
columns within the Table itself; columns marked as
primary_key=True will be gathered into the empty constraint’s
column collection.
-
__init__(*columns, **kw)
-
Parameters: |
- *columns – A sequence of column names or Column objects.
- name – Optional, the in-database name of this constraint.
- deferrable – Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
issuing DDL for this constraint.
- initially – Optional string. If set, emit INITIALLY <value> when issuing DDL
for this constraint.
- **kw – other keyword arguments including dialect-specific
arguments are propagated to the Constraint superclass.
|
-
classmethod argument_for(dialect_name, argument_name, default)
-
Add a new kind of dialect-specific keyword argument for this class.
E.g.:
Index.argument_for("mydialect", "length", None)
some_index = Index('a', 'b', mydialect_length=5)
The DialectKWArgs.argument_for() method is a per-argument
way adding extra arguments to the
DefaultDialect.construct_arguments dictionary. This
dictionary provides a list of argument names accepted by various
schema-level constructs on behalf of a dialect.
New dialects should typically specify this dictionary all at once as a
data member of the dialect class. The use case for ad-hoc addition of
argument names is typically for end-user code that is also using
a custom compilation scheme which consumes the additional arguments.
Parameters: |
- dialect_name – name of a dialect. The dialect must be
locatable, else a NoSuchModuleError is raised. The
dialect must also include an existing
DefaultDialect.construct_arguments collection, indicating
that it participates in the keyword-argument validation and default
system, else ArgumentError is raised. If the dialect does
not include this collection, then any keyword argument can be
specified on behalf of this dialect already. All dialects packaged
within SQLAlchemy include this collection, however for third party
dialects, support may vary.
- argument_name – name of the parameter.
- default – default value of the parameter.
|
-
dialect_kwargs
-
A collection of keyword arguments specified as dialect-specific
options to this construct.
The arguments are present here in their original <dialect>_<kwarg>
format. Only arguments that were actually passed are included;
unlike the DialectKWArgs.dialect_options collection, which
contains all options known by this dialect including defaults.
The collection is also writable; keys are accepted of the
form <dialect>_<kwarg> where the value will be assembled
into the list of options.
-
dialect_options
-
A collection of keyword arguments specified as dialect-specific
options to this construct.
This is a two-level nested registry, keyed to <dialect_name>
and <argument_name>. For example, the postgresql_where
argument would be locatable as:
arg = my_object.dialect_options['postgresql']['where']
-
get_children(**kwargs)
-
used to allow SchemaVisitor access
-
info
-
Info dictionary associated with the object, allowing user-defined
data to be associated with this SchemaItem.
The dictionary is automatically generated when first accessed.
It can also be specified in the constructor of some objects,
such as Table and Column.
-
kwargs
-
A synonym for DialectKWArgs.dialect_kwargs.
-
quote
-
Return the value of the quote flag passed
to this schema object, for those schema items which
have a name field.
Deprecated since version 0.9: Use <obj>.name.quote
-
class sqlalchemy.schema.UniqueConstraint(*columns, **kw)
Bases: sqlalchemy.schema.ColumnCollectionConstraint
A table-level UNIQUE constraint.
Defines a single column or composite UNIQUE constraint. For a no-frills,
single column constraint, adding unique=True to the Column
definition is a shorthand equivalent for an unnamed, single column
UniqueConstraint.
-
__init__(*columns, **kw)
-
Parameters: |
- *columns – A sequence of column names or Column objects.
- name – Optional, the in-database name of this constraint.
- deferrable – Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
issuing DDL for this constraint.
- initially – Optional string. If set, emit INITIALLY <value> when issuing DDL
for this constraint.
- **kw – other keyword arguments including dialect-specific
arguments are propagated to the Constraint superclass.
|
-
classmethod argument_for(dialect_name, argument_name, default)
-
Add a new kind of dialect-specific keyword argument for this class.
E.g.:
Index.argument_for("mydialect", "length", None)
some_index = Index('a', 'b', mydialect_length=5)
The DialectKWArgs.argument_for() method is a per-argument
way adding extra arguments to the
DefaultDialect.construct_arguments dictionary. This
dictionary provides a list of argument names accepted by various
schema-level constructs on behalf of a dialect.
New dialects should typically specify this dictionary all at once as a
data member of the dialect class. The use case for ad-hoc addition of
argument names is typically for end-user code that is also using
a custom compilation scheme which consumes the additional arguments.
Parameters: |
- dialect_name – name of a dialect. The dialect must be
locatable, else a NoSuchModuleError is raised. The
dialect must also include an existing
DefaultDialect.construct_arguments collection, indicating
that it participates in the keyword-argument validation and default
system, else ArgumentError is raised. If the dialect does
not include this collection, then any keyword argument can be
specified on behalf of this dialect already. All dialects packaged
within SQLAlchemy include this collection, however for third party
dialects, support may vary.
- argument_name – name of the parameter.
- default – default value of the parameter.
|
-
dialect_kwargs
-
A collection of keyword arguments specified as dialect-specific
options to this construct.
The arguments are present here in their original <dialect>_<kwarg>
format. Only arguments that were actually passed are included;
unlike the DialectKWArgs.dialect_options collection, which
contains all options known by this dialect including defaults.
The collection is also writable; keys are accepted of the
form <dialect>_<kwarg> where the value will be assembled
into the list of options.
-
dialect_options
-
A collection of keyword arguments specified as dialect-specific
options to this construct.
This is a two-level nested registry, keyed to <dialect_name>
and <argument_name>. For example, the postgresql_where
argument would be locatable as:
arg = my_object.dialect_options['postgresql']['where']
-
get_children(**kwargs)
-
used to allow SchemaVisitor access
-
info
-
Info dictionary associated with the object, allowing user-defined
data to be associated with this SchemaItem.
The dictionary is automatically generated when first accessed.
It can also be specified in the constructor of some objects,
such as Table and Column.
-
kwargs
-
A synonym for DialectKWArgs.dialect_kwargs.
-
quote
-
Return the value of the quote flag passed
to this schema object, for those schema items which
have a name field.
Deprecated since version 0.9: Use <obj>.name.quote
-
sqlalchemy.schema.conv(cls, value, quote=None)
Mark a string indicating that a name has already been converted
by a naming convention.
This is a string subclass that indicates a name that should not be
subject to any further naming conventions.
E.g. when we create a Constraint using a naming convention
as follows:
m = MetaData(naming_convention={
"ck": "ck_%(table_name)s_%(constraint_name)s"
})
t = Table('t', m, Column('x', Integer),
CheckConstraint('x > 5', name='x5'))
The name of the above constraint will be rendered as "ck_t_x5".
That is, the existing name x5 is used in the naming convention as the
constraint_name token.
In some situations, such as in migration scripts, we may be rendering
the above CheckConstraint with a name that’s already been
converted. In order to make sure the name isn’t double-modified, the
new name is applied using the schema.conv() marker. We can
use this explicitly as follows:
m = MetaData(naming_convention={
"ck": "ck_%(table_name)s_%(constraint_name)s"
})
t = Table('t', m, Column('x', Integer),
CheckConstraint('x > 5', name=conv('ck_t_x5')))
Where above, the schema.conv() marker indicates that the constraint
name here is final, and the name will render as "ck_t_x5" and not
"ck_t_ck_t_x5"
Indexes
Indexes can be created anonymously (using an auto-generated name ix_<column
label>) for a single column using the inline index keyword on
Column, which also modifies the usage of
unique to apply the uniqueness to the index itself, instead of adding a
separate UNIQUE constraint. For indexes with specific names or which encompass
more than one column, use the Index construct,
which requires a name.
Below we illustrate a Table with several
Index objects associated. The DDL for “CREATE
INDEX” is issued right after the create statements for the table:
meta = MetaData()
mytable = Table('mytable', meta,
# an indexed column, with index "ix_mytable_col1"
Column('col1', Integer, index=True),
# a uniquely indexed column with index "ix_mytable_col2"
Column('col2', Integer, index=True, unique=True),
Column('col3', Integer),
Column('col4', Integer),
Column('col5', Integer),
Column('col6', Integer),
)
# place an index on col3, col4
Index('idx_col34', mytable.c.col3, mytable.c.col4)
# place a unique index on col5, col6
Index('myindex', mytable.c.col5, mytable.c.col6, unique=True)
sqlmytable.create(engine)
Note in the example above, the Index construct is created
externally to the table which it corresponds, using Column
objects directly. Index also supports
“inline” definition inside the Table, using string names to
identify columns:
meta = MetaData()
mytable = Table('mytable', meta,
Column('col1', Integer),
Column('col2', Integer),
Column('col3', Integer),
Column('col4', Integer),
# place an index on col1, col2
Index('idx_col12', 'col1', 'col2'),
# place a unique index on col3, col4
Index('idx_col34', 'col3', 'col4', unique=True)
)
New in version 0.7: Support of “inline” definition inside the Table
for Index.
The Index object also supports its own create() method:
i = Index('someindex', mytable.c.col5)
sqli.create(engine)
Functional Indexes
Index supports SQL and function expressions, as supported by the
target backend. To create an index against a column using a descending
value, the ColumnElement.desc() modifier may be used:
from sqlalchemy import Index
Index('someindex', mytable.c.somecol.desc())
Or with a backend that supports functional indexes such as Postgresql,
a “case insensitive” index can be created using the lower() function:
from sqlalchemy import func, Index
Index('someindex', func.lower(mytable.c.somecol))
New in version 0.8: Index supports SQL expressions and functions
as well as plain columns.
Index API
-
class sqlalchemy.schema.Index(name, *expressions, **kw)
Bases: sqlalchemy.sql.base.DialectKWArgs, sqlalchemy.schema.ColumnCollectionMixin, sqlalchemy.schema.SchemaItem
A table-level INDEX.
Defines a composite (one or more column) INDEX.
E.g.:
sometable = Table("sometable", metadata,
Column("name", String(50)),
Column("address", String(100))
)
Index("some_index", sometable.c.name)
For a no-frills, single column index, adding
Column also supports index=True:
sometable = Table("sometable", metadata,
Column("name", String(50), index=True)
)
For a composite index, multiple columns can be specified:
Index("some_index", sometable.c.name, sometable.c.address)
Functional indexes are supported as well, typically by using the
func construct in conjunction with table-bound
Column objects:
Index("some_index", func.lower(sometable.c.name))
New in version 0.8: support for functional and expression-based indexes.
An Index can also be manually associated with a Table,
either through inline declaration or using
Table.append_constraint(). When this approach is used, the names
of the indexed columns can be specified as strings:
Table("sometable", metadata,
Column("name", String(50)),
Column("address", String(100)),
Index("some_index", "name", "address")
)
To support functional or expression-based indexes in this form, the
text() construct may be used:
from sqlalchemy import text
Table("sometable", metadata,
Column("name", String(50)),
Column("address", String(100)),
Index("some_index", text("lower(name)"))
)
New in version 0.9.5: the text() construct may be used to
specify Index expressions, provided the Index
is explicitly associated with the Table.
-
__init__(name, *expressions, **kw)
Construct an index object.
Parameters: |
- name – The name of the index
- *expressions – Column expressions to include in the index. The expressions
are normally instances of Column, but may also
be arbitrary SQL expressions which ultimately refer to a
Column.
- unique=False – Keyword only argument; if True, create a unique index.
- quote=None – Keyword only argument; whether to apply quoting to the name of
the index. Works in the same manner as that of
Column.quote.
- **kw – Additional keyword arguments not mentioned above are
dialect specific, and passed in the form
<dialectname>_<argname>. See the documentation regarding an
individual dialect at Dialects for detail on
documented arguments.
|
-
classmethod argument_for(dialect_name, argument_name, default)
-
Add a new kind of dialect-specific keyword argument for this class.
E.g.:
Index.argument_for("mydialect", "length", None)
some_index = Index('a', 'b', mydialect_length=5)
The DialectKWArgs.argument_for() method is a per-argument
way adding extra arguments to the
DefaultDialect.construct_arguments dictionary. This
dictionary provides a list of argument names accepted by various
schema-level constructs on behalf of a dialect.
New dialects should typically specify this dictionary all at once as a
data member of the dialect class. The use case for ad-hoc addition of
argument names is typically for end-user code that is also using
a custom compilation scheme which consumes the additional arguments.
Parameters: |
- dialect_name – name of a dialect. The dialect must be
locatable, else a NoSuchModuleError is raised. The
dialect must also include an existing
DefaultDialect.construct_arguments collection, indicating
that it participates in the keyword-argument validation and default
system, else ArgumentError is raised. If the dialect does
not include this collection, then any keyword argument can be
specified on behalf of this dialect already. All dialects packaged
within SQLAlchemy include this collection, however for third party
dialects, support may vary.
- argument_name – name of the parameter.
- default – default value of the parameter.
|
-
bind
Return the connectable associated with this Index.
-
create(bind=None)
Issue a CREATE statement for this
Index, using the given Connectable
for connectivity.
-
dialect_kwargs
-
A collection of keyword arguments specified as dialect-specific
options to this construct.
The arguments are present here in their original <dialect>_<kwarg>
format. Only arguments that were actually passed are included;
unlike the DialectKWArgs.dialect_options collection, which
contains all options known by this dialect including defaults.
The collection is also writable; keys are accepted of the
form <dialect>_<kwarg> where the value will be assembled
into the list of options.
-
dialect_options
-
A collection of keyword arguments specified as dialect-specific
options to this construct.
This is a two-level nested registry, keyed to <dialect_name>
and <argument_name>. For example, the postgresql_where
argument would be locatable as:
arg = my_object.dialect_options['postgresql']['where']
-
drop(bind=None)
Issue a DROP statement for this
Index, using the given Connectable
for connectivity.
-
get_children(**kwargs)
-
used to allow SchemaVisitor access
-
info
-
Info dictionary associated with the object, allowing user-defined
data to be associated with this SchemaItem.
The dictionary is automatically generated when first accessed.
It can also be specified in the constructor of some objects,
such as Table and Column.
-
kwargs
-
A synonym for DialectKWArgs.dialect_kwargs.
-
quote
-
Return the value of the quote flag passed
to this schema object, for those schema items which
have a name field.
Deprecated since version 0.9: Use <obj>.name.quote