Defining Constraints and Indexes

This section will discuss SQL constraints and indexes. In SQLAlchemythe key classes include ForeignKeyConstraint and Index.

Defining Foreign Keys

A foreign key in SQL is a table-level construct that constrains one or morecolumns in that table to only allow values that are present in a different setof columns, typically but not always located on a different table. We call thecolumns which are constrained the foreign key columns and the columns whichthey are constrained towards the referenced columns. The referenced columnsalmost always define the primary key for their owning table, though there areexceptions to this. The foreign key is the “joint” that connects togetherpairs of rows which have a relationship with each other, and SQLAlchemyassigns very deep importance to this concept in virtually every area of itsoperation.

In SQLAlchemy as well as in DDL, foreign key constraints can be defined asadditional attributes within the table clause, or for single-column foreignkeys they may optionally be specified within the definition of a singlecolumn. The single column foreign key is more common, and at the column levelis specified by constructing a ForeignKey objectas 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 mustcontain a value in the user_id column that also exists in the usertable’s user_id column.

The argument to ForeignKey is most commonly astring of the form <tablename>.<columnname>, or for a table in a remoteschema or “owner” of the form <schemaname>.<tablename>.<columnname>. It mayalso be an actual Column object, which as we’llsee later is accessed from an existing Tableobject via its c collection:

ForeignKey(user.c.user_id)

The advantage to using a string is that the in-python linkage between userand user_preference is resolved only when first needed, so that tableobjects can be easily spread across multiple modules and defined in any order.

Foreign keys may also be defined at the table level, using theForeignKeyConstraint object. This object candescribe a single- or multi-column foreign key. A multi-column foreign key isknown as a composite foreign key, and almost always references a table thathas a composite primary key. Below we define a table invoice which has acomposite 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 referencinginvoice:

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 theForeignKeyConstraint is the only way to define acomposite foreign key. While we could also have placed individualForeignKey objects on both theinvoice_item.invoice_id and invoice_item.ref_num columns, SQLAlchemywould not be aware that these two values should be paired together - it wouldbe two individual foreign key constraints instead of a single compositeforeign key referencing two columns.

Creating/Dropping Foreign Key Constraints via ALTER

The behavior we’ve seen in tutorials and elsewhere involvingforeign keys with DDL illustrates that the constraints are typicallyrendered “inline” within the CREATE TABLE statement, such as:

CREATE TABLE addresses (
    id INTEGER NOT NULL,
    user_id INTEGER,
    email_address VARCHAR NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT user_id_fk FOREIGN KEY(user_id) REFERENCES users (id)
)

The CONSTRAINT .. FOREIGN KEY directive is used to create the constraintin an “inline” fashion within the CREATE TABLE definition. TheMetaData.create_all() and MetaData.drop_all() methods dothis by default, using a topological sort of all the Table objectsinvolved such that tables are created and dropped in order of their foreignkey dependency (this sort is also available via theMetaData.sorted_tables accessor).

This approach can’t work when two or more foreign key constraints areinvolved in a “dependency cycle”, where a set of tablesare mutually dependent on each other, assuming the backend enforces foreignkeys (always the case except on SQLite, MySQL/MyISAM). The methods willtherefore break out constraints in such a cycle into separate ALTERstatements, on all backends other than SQLite which does not supportmost forms of ALTER. Given a schema like:

node = Table(
    'node', metadata,
    Column('node_id', Integer, primary_key=True),
    Column(
        'primary_element', Integer,
        ForeignKey('element.element_id')
    )
)
 
element = Table(
    'element', metadata,
    Column('element_id', Integer, primary_key=True),
    Column('parent_node_id', Integer),
    ForeignKeyConstraint(
        ['parent_node_id'], ['node.node_id'],
        name='fk_element_parent_node_id'
    )
)

When we call upon MetaData.create_all() on a backend such as thePostgreSQL backend, the cycle between these two tables is resolved and theconstraints are created separately:

>>> with engine.connect() as conn:
...    metadata.create_all(conn, checkfirst=False)
CREATE TABLE element (
    element_id SERIAL NOT NULL,
    parent_node_id INTEGER,
    PRIMARY KEY (element_id)
)
CREATE TABLE node (
    node_id SERIAL NOT NULL,
    primary_element INTEGER,
    PRIMARY KEY (node_id)
)
ALTER TABLE element ADD CONSTRAINT fk_element_parent_node_id
    FOREIGN KEY(parent_node_id) REFERENCES node (node_id)
ALTER TABLE node ADD FOREIGN KEY(primary_element)
    REFERENCES element (element_id)

In order to emit DROP for these tables, the same logic applies, howevernote here that in SQL, to emit DROP CONSTRAINT requires that the constrainthas a name. In the case of the 'node' table above, we haven’t namedthis constraint; the system will therefore attempt to emit DROP for onlythose constraints that are named:

>>> with engine.connect() as conn:
...    metadata.drop_all(conn, checkfirst=False)
ALTER TABLE element DROP CONSTRAINT fk_element_parent_node_id
DROP TABLE node
DROP TABLE element

In the case where the cycle cannot be resolved, such as if we hadn’t applieda name to either constraint here, we will receive the following error:

sqlalchemy.exc.CircularDependencyError: Can't sort tables for DROP;
an unresolvable foreign key dependency exists between tables:
element, node.  Please ensure that the ForeignKey and ForeignKeyConstraint
objects involved in the cycle have names so that they can be dropped
using DROP CONSTRAINT.

This error only applies to the DROP case as we can emit “ADD CONSTRAINT”in the CREATE case without a name; the database typically assigns oneautomatically.

The ForeignKeyConstraint.use_alter andForeignKey.use_alter keyword arguments can be usedto manually resolve dependency cycles. We can add this flag only tothe 'element' table as follows:

element = Table(
    'element', metadata,
    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'
    )
)

in our CREATE DDL we will see the ALTER statement only for this constraint,and not the other one:

>>> with engine.connect() as conn:
...    metadata.create_all(conn, checkfirst=False)
CREATE TABLE element (
    element_id SERIAL NOT NULL,
    parent_node_id INTEGER,
    PRIMARY KEY (element_id)
)
CREATE TABLE node (
    node_id SERIAL NOT NULL,
    primary_element INTEGER,
    PRIMARY KEY (node_id),
    FOREIGN KEY(primary_element) REFERENCES element (element_id)
)
ALTER TABLE element ADD CONSTRAINT fk_element_parent_node_id
FOREIGN KEY(parent_node_id) REFERENCES node (node_id)

ForeignKeyConstraint.use_alter andForeignKey.use_alter, when used in conjunction with a dropoperation, will require that the constraint is named, else an errorlike the following is generated:

sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint
ForeignKeyConstraint(...); it has no name

Changed in version 1.0.0: - The DDL system invoked byMetaData.create_all()and MetaData.drop_all() will now automatically resolve mutuallydepdendent foreign keys between tables declared byForeignKeyConstraint and ForeignKey objects, withoutthe need to explicitly set the ForeignKeyConstraint.use_alterflag.

Changed in version 1.0.0: - The ForeignKeyConstraint.use_alterflag can be used with an un-named constraint; only the DROP operationwill emit a specific error when actually called upon.

See also

Configuring Constraint Naming Conventions

sort_tables_and_constraints()

ON UPDATE and ON DELETE

Most databases support cascading of foreign key values, that is the when aparent row is updated the new value is placed in child rows, or when theparent row is deleted all corresponding child rows are set to null or deleted.In data definition language these are specified using phrases like “ON UPDATECASCADE”, “ON DELETE CASCADE”, and “ON DELETE SET NULL”, corresponding toforeign key constraints. The phrase after “ON UPDATE” or “ON DELETE” may alsoother allow other phrases that are specific to the database in use. TheForeignKey andForeignKeyConstraint objects support thegeneration of this clause via the onupdate and ondelete keywordarguments. 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 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 theunique keyword on Column. Explicitly namedunique constraints and/or those with multiple columns are created via theUniqueConstraint 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 orTable 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 checkconstraints generally should only refer to the column to which they areplaced, while table level constraints can refer to any columns in the table.

Note that some databases do not actively support check constraints such asMySQL.

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)
CREATE TABLE mytable (
    col1 INTEGER  CHECK (col1>5),
    col2 INTEGER,
    col3 INTEGER,
    CONSTRAINT check1  CHECK (col2 > col3 + 5)
)

PRIMARY KEY Constraint

The primary key constraint of any Table object is implicitlypresent, based on the Column objects that are marked with theColumn.primary_key flag. The PrimaryKeyConstraintobject provides explicit access to this constraint, which includes theoption 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')
        )

See also

PrimaryKeyConstraint - detailed API documentation.

Setting up Constraints when using the Declarative ORM Extension

The Table is the SQLAlchemy Core construct that allows one to definetable metadata, which among other things can be used by the SQLAlchemy ORMas a target to map a class. The Declarativeextension allows the Table object to be created automatically, giventhe contents of the table primarily as a mapping of Column objects.

To apply table-level constraint objects such as ForeignKeyConstraintto 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 andindexes. In the common case that a table is created using CREATE TABLEwhere constraints such as CHECK, UNIQUE, and PRIMARY KEY constraints areproduced inline with the table definition, the database usually has a systemin place in which names are automatically assigned to these constraints, ifa name is not otherwise specified. When an existing database table is alteredin a database using a command such as ALTER TABLE, this command typicallyneeds to specify explicit names for new constraints as well as be able tospecify 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 thecase of constraints this parameter is optional. There are also the usecases of using the Column.unique and Column.indexparameters which create UniqueConstraint and Index objectswithout an explicit name being specified.

The use case of alteration of existing tables and constraints can be handledby schema migration tools such as Alembic.However, neither Alembic nor SQLAlchemy currently create names for constraintobjects where the name is otherwise unspecified, leading to the case wherebeing able to alter existing constraints means that one must reverse-engineerthe 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 Constraintand Index objects, automated naming schemes can be constructedusing events. This approach has the advantage that constraints will geta consistent naming scheme without the need for explicit name parametersthroughout the code, and also that the convention takes place just as wellfor those constraints and indexes produced by the Column.uniqueand Column.index parameters. As of SQLAlchemy 0.9.2 thisevent-based approach is included, and can be configured using the argumentMetaData.naming_convention.

MetaData.naming_convention refers to a dictionary which acceptsthe Index class or individual Constraint classes as keys,and Python string templates as values. It also accepts a series ofstring-codes as alternative keys, "fk", "pk","ix", "ck", "uq" for foreign key, primary key, index,check, and unique constraint, respectively. The string templates in thisdictionary are used whenever a constraint or index is associated with thisMetaData object that does not have an existing name given (includingone 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 withinthe target MetaData collection.For example, we can observe the name produced when we create an unnamedUniqueConstraint:

>>> 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.uniqueflag:

>>> 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 establishedat Python construction time, rather than at DDL emit time. The effect this haswhen using Alembic’s —autogenerate feature is that the naming conventionwill 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 UniqueConstraintobject that —autogenerate located in our metadata.

The default value for MetaData.naming_convention handlesthe long-standing SQLAlchemy behavior of assigning a name to a Indexobject 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, as well asmultiple-column versions of each including %(column_0N_name)s,%(column_0_N_name)s, %(referred_column_0_N_name)s which render allcolumn names separated with or without an underscore. The documentation forMetaData.naming_convention has further detail on each of theseconventions.

When a generated name, particularly those that use the multiple-column tokens,is too long for the identifier length limit of the target database(for example, PostgreSQL has a limit of 63 characters), the name will bedeterministically truncated using a 4-character suffix based on the md5hash of the long name. For example, the naming convention below willgenerate very long names given the column names in use:

metadata = MetaData(naming_convention={
    "uq": "uq_%(table_name)s_%(column_0_N_name)s"
})
 
long_names = Table(
    'long_names', metadata,
    Column('information_channel_code', Integer, key='a'),
    Column('billing_convention_name', Integer, key='b'),
    Column('product_identifier', Integer, key='c'),
    UniqueConstraint('a', 'b', 'c')
)

On the PostgreSQL dialect, names longer than 63 characters will be truncatedas in the following example:

CREATE TABLE long_names (
    information_channel_code INTEGER,
    billing_convention_name INTEGER,
    product_identifier INTEGER,
    CONSTRAINT uq_long_names_information_channel_code_billing_conventi_a79e
    UNIQUE (information_channel_code, billing_convention_name, product_identifier)
)

The above suffix a79e is based on the md5 hash of the long name and willgenerate the same value every time to produce consistent names for a givenschema.

New tokens can also be added, by specifying an additional tokenand a callable within the naming_convention dictionary. For example, if wewanted to name our foreign key constraints using a GUID scheme, we could dothat 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 aname 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

See also

MetaData.naming_convention - for additional usage detailsas well as a listing of all available naming components.

The Importance of Naming Constraints - in the Alembic documentation.

New in version 1.3.0: added multi-column naming tokens such as %(column_0_N_name)s.Generated names that go beyond the character limit for the target database will bedeterministically truncated.

Naming CHECK Constraints

The CheckConstraint object is configured against an arbitrarySQL expression, which can have any number of columns present, and additionallyis often configured using a raw SQL string. Therefore a common conventionto use with CheckConstraint is one where we expect the objectto have a name already, and we then enhance it with other convention elements.A typical convention is "ck%(table_name)s%(constraint_name)s":

metadata = MetaData(
    naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
)
 
Table('foo', metadata,
    Column('value', Integer),
    CheckConstraint('value > 5', name='value_gt_5')
)

The above table will produce the name ck_foo_value_gt_5:

CREATE TABLE foo (
    value INTEGER,
    CONSTRAINT ck_foo_value_gt_5 CHECK (value > 5)
)

CheckConstraint also supports the %(columns_0_name)stoken; we can make use of this by ensuring we use a Column orsql.expression.column() element within the constraint’s expression,either by declaring the constraint separate from the table:

metadata = MetaData(
    naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"}
)
 
foo = Table('foo', metadata,
    Column('value', Integer)
)
 
CheckConstraint(foo.c.value > 5)

or by using a sql.expression.column() inline:

from sqlalchemy import column
 
metadata = MetaData(
    naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"}
)
 
foo = Table('foo', metadata,
    Column('value', Integer),
    CheckConstraint(column('value') > 5)
)

Both will produce the name ck_foo_value:

CREATE TABLE foo (
    value INTEGER,
    CONSTRAINT ck_foo_value CHECK (value > 5)
)

The determination of the name of “column zero” is performed by scanningthe given expression for column objects. If the expression has more thanone column present, the scan does use a deterministic search, however thestructure of the expression will determine which column is noted as“column zero”.

New in version 1.0.0: The CheckConstraint object now supportsthe column_0_name naming convention token.

Configuring Naming for Boolean, Enum, and other schema types

The SchemaType class refers to type objects such as Booleanand Enum which generate a CHECK constraint accompanying the type.The name for the constraint here is most directly set up by sendingthe “name” parameter, e.g. Boolean.name:

Table('foo', metadata,
    Column('flag', Boolean(name='ck_foo_flag'))
)

The naming convention feature may be combined with these types as well,normally by using a convention which includes %(constraint_name)sand then applying a name to the type:

metadata = MetaData(
    naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
)
 
Table('foo', metadata,
    Column('flag', Boolean(name='flag_bool'))
)

The above table will produce the constraint name ck_foo_flag_bool:

CREATE TABLE foo (
    flag BOOL,
    CONSTRAINT ck_foo_flag_bool CHECK (flag IN (0, 1))
)

The SchemaType classes use special internal symbols so thatthe naming convention is only determined at DDL compile time. On PostgreSQL,there’s a native BOOLEAN type, so the CHECK constraint of Booleanis not needed; we are safe to set up a Boolean type without aname, even though a naming convention is in place for check constraints.This convention will only be consulted for the CHECK constraint if werun against a database without a native BOOLEAN type like SQLite orMySQL.

The CHECK constraint may also make use of the column_0_name token,which works nicely with SchemaType since these constraints haveonly one column:

metadata = MetaData(
    naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"}
)
 
Table('foo', metadata,
    Column('flag', Boolean())
)

The above schema will produce:

CREATE TABLE foo (
    flag BOOL,
    CONSTRAINT ck_foo_flag CHECK (flag IN (0, 1))
)

Changed in version 1.0: Constraint naming conventions that don’t include%(constraint_name)s again work with SchemaType constraints.

Constraints API

• class sqlalchemy.schema.Constraint(name=None, deferrable=None, initially=None, createrule=None, info=None, typebound=False, **dialect_kw)
• Bases: sqlalchemy.sql.base.DialectKWArgs, sqlalchemy.schema.SchemaItem

A table-level SQL constraint.

• init(name=None, deferrable=None, initially=None, createrule=None, info=None, typebound=False, **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 whenissuing DDL for this constraint.

    • initially – Optional string. If set, emit INITIALLY when issuing DDLfor this constraint.

    • info –

Optional data dictionary which will be populated into theSchemaItem.info attribute of this object.

New in version 1.0.0.

  - 

_create_rule –

a callable which is passed the DDLCompiler object duringcompilation. Returns True or False to signal inline generation ofthis Constraint.

The AddConstraint and DropConstraint DDL constructs provideDDLElement’s more comprehensive “conditional DDL” approach that ispassed a database connection when DDL is being issued. _create_ruleis instead called during any CREATE TABLE compilation, where theremay not be any transaction/connection in progress. However, itallows conditional compilation of the constraint even for backendswhich 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 dialectspecific, and passed in the form <dialectname>_<argname>. Seethe documentation regarding an individual dialect atDialects for detail on documented arguments.

• class sqlalchemy.schema.ColumnCollectionMixin(*columns, **kw)
• • columns = None
  • A ColumnCollection of Column objects.

This collection represents the columns which are referred to bythis object.

• class sqlalchemy.schema.ColumnCollectionConstraint(*columns, **kw)
• Bases: sqlalchemy.schema.ColumnCollectionMixin, sqlalchemy.schema.Constraint

A constraint that proxies a ColumnCollection.

• eq()

inherited from the eq() method of object

Return self==value.

• 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 whenissuing DDL for this constraint.

    • initially – Optional string. If set, emit INITIALLY when issuing DDLfor this constraint.

    • **kw – other keyword arguments including dialect-specificarguments are propagated to the Constraint superclass.

• le()

inherited from the le() method of object

Return self<=value.

• lt()

inherited from the lt() method of object

Return self

• class sqlalchemy.schema.CheckConstraint(sqltext, name=None, deferrable=None, initially=None, table=None, info=None, createrule=None, autoattach=True, __type_bound=False, **kw)
• Bases: sqlalchemy.schema.ColumnCollectionConstraint

A table- or column-level CHECK constraint.

Can be included in the definition of a Table or Column.

• eq()

inherited from the eq() method of object

Return self==value.

• init(sqltext, name=None, deferrable=None, initially=None, table=None, info=None, createrule=None, autoattach=True, __type_bound=False, **kw)

• Construct a CHECK constraint.

  • Parameters
  • • sqltext –

A string containing the constraint definition, which will be usedverbatim, or a SQL expression construct. If given as a string,the object is converted to a text() object. If the textualstring includes a colon character, escape this using a backslash:

CheckConstraint(r"foo ~ E'a(?\:b|c)d")

Warning

The CheckConstraint.sqltext argument to CheckConstraint can be passed as a Python string argument, which will be treated as trusted SQL text and rendered as given. DO NOT PASS UNTRUSTED INPUT TO THIS PARAMETER.

  - 

name – Optional, the in-database name of the constraint.

  - 

deferrable – Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE whenissuing DDL for this constraint.

  - 

initially – Optional string. If set, emit INITIALLY when issuing DDLfor this constraint.

  - 

info –

Optional data dictionary which will be populated into theSchemaItem.info attribute of this object.

New in version 1.0.0.

• le()

inherited from the le() method of object

Return self<=value.

• lt()

inherited from the lt() method of object

Return self

• 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, info=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 definesa dependency between two columns. The actual constraintis in all cases represented by the ForeignKeyConstraintobject. This object will be generated automatically whena ForeignKey is associated with a Column whichin turn is associated with a Table. Conversely,when ForeignKeyConstraint is applied to a Table,ForeignKey markers are automatically generated to bepresent on each associated Column, which are alsoassociated with the constraint object.

Note that you cannot define a “composite” foreign key constraint,that is a constraint between a grouping of multiple parent/childcolumns, using ForeignKey objects. To define this grouping,the ForeignKeyConstraint object must be used, and appliedto the Table. The associated ForeignKey objectsare created automatically.

The ForeignKey objects associated with an individualColumn object are available in the foreign_keys collectionof that column.

Further examples of foreign key configuration are inDefining Foreign Keys.

• eq()

inherited from the eq() method of object

Return self==value.

• init(column, constraint=None, _use_alter=False, name=None, onupdate=None, ondelete=None, deferrable=None, initially=None, link_to_name=False, match=None, info=None, **dialect_kw)[](https://docs.sqlalchemy.org/en/13/core/#sqlalchemy.schema.ForeignKey.init__)
• Construct a column-level FOREIGN KEY.

The ForeignKey object when constructed generates aForeignKeyConstraint which is associated with the parentTable object’s collection of constraints.

- Parameters
- 
  - 

column – A single target column for the key relationship. AColumn 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 isTrue in which case the rendered name of the column is used.

  - 

name – Optional string. An in-database name for the key ifconstraint is not provided.

  - 

onupdate – Optional string. If set, emit ON UPDATE whenissuing DDL for this constraint. Typical values include CASCADE,DELETE and RESTRICT.

  - 

ondelete – Optional string. If set, emit ON DELETE whenissuing DDL for this constraint. Typical values include CASCADE,DELETE and RESTRICT.

  - 

deferrable – Optional bool. If set, emit DEFERRABLE or NOTDEFERRABLE when issuing DDL for this constraint.

  - 

initially – Optional string. If set, emit INITIALLY whenissuing DDL for this constraint.

  - 

link_to_name – if True, the string name given in column isthe rendered name of the referenced column, not its locallyassigned key.

  - 

use_alter –

passed to the underlyingForeignKeyConstraint to indicate the constraint shouldbe generated/dropped externally from the CREATE TABLE/ DROP TABLEstatement. See ForeignKeyConstraint.use_alterfor further description.

See also

ForeignKeyConstraint.use_alter

Creating/Dropping Foreign Key Constraints via ALTER

  - 

match – Optional string. If set, emit MATCH when issuingDDL for this constraint. Typical values include SIMPLE, PARTIALand FULL.

  - 

info –

Optional data dictionary which will be populated into theSchemaItem.info attribute of this object.

New in version 1.0.0.

  - 

**dialect_kw –

Additional keyword arguments are dialectspecific, and passed in the form <dialectname>_<argname>. Thearguments are ultimately handled by a correspondingForeignKeyConstraint. See the documentation regardingan individual dialect at Dialects for detail ondocumented arguments.

New in version 0.9.2.

• le()

inherited from the le() method of object

Return self<=value.

• lt()

inherited from the lt() method of object

Return self

• 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, info=None, **dialect_kw)
• Bases: sqlalchemy.schema.ColumnCollectionConstraint

A table-level FOREIGN KEY constraint.

Defines a single column or composite FOREIGN KEY … REFERENCESconstraint. For a no-frills, single column foreign key, adding aForeignKey to the definition of a Column is ashorthand equivalent for an unnamed, single columnForeignKeyConstraint.

Examples of foreign key configuration are in Defining Foreign Keys.

• eq()

inherited from the eq() method of object

Return self==value.

• init(columns, refcolumns, name=None, onupdate=None, ondelete=None, deferrable=None, initially=None, use_alter=False, link_to_name=False, match=None, table=None, info=None, **dialect_kw)

• Construct a composite-capable FOREIGN KEY.

  • Parameters

  • • columns – A sequence of local column names. The named columnsmust be defined and present in the parent Table. The names shouldmatch the key given to each column (defaults to the name) unlesslink_to_name is True.

    • refcolumns – A sequence of foreign column names or Columnobjects. The columns must all be located within the same Table.

    • name – Optional, the in-database name of the key.

    • onupdate – Optional string. If set, emit ON UPDATE whenissuing DDL for this constraint. Typical values include CASCADE,DELETE and RESTRICT.

    • ondelete – Optional string. If set, emit ON DELETE whenissuing DDL for this constraint. Typical values include CASCADE,DELETE and RESTRICT.

    • deferrable – Optional bool. If set, emit DEFERRABLE or NOTDEFERRABLE when issuing DDL for this constraint.

    • initially – Optional string. If set, emit INITIALLY whenissuing DDL for this constraint.

    • link_to_name – if True, the string name given in column isthe rendered name of the referenced column, not its locally assignedkey.

    • use_alter –

If True, do not emit the DDL for this constraint aspart of the CREATE TABLE definition. Instead, generate it via anALTER TABLE statement issued after the full collection of tableshave been created, and drop it via an ALTER TABLE statement beforethe full collection of tables are dropped.

The use of ForeignKeyConstraint.use_alter isparticularly geared towards the case where two or more tablesare established within a mutually-dependent foreign key constraintrelationship; however, the MetaData.create_all() andMetaData.drop_all() methods will perform this resolutionautomatically, so the flag is normally not needed.

Changed in version 1.0.0: Automatic resolution of foreign keycycles has been added, removing the need to use theForeignKeyConstraint.use_alter in typical usecases.

See also

Creating/Dropping Foreign Key Constraints via ALTER

  - 

match – Optional string. If set, emit MATCH when issuingDDL for this constraint. Typical values include SIMPLE, PARTIALand FULL.

  - 

info –

Optional data dictionary which will be populated into theSchemaItem.info attribute of this object.

New in version 1.0.0.

  - 

**dialect_kw –

Additional keyword arguments are dialectspecific, and passed in the form <dialectname>_<argname>. Seethe documentation regarding an individual dialect atDialects for detail on documented arguments.

New in version 0.9.2.

• le()

inherited from the le() method of object

Return self<=value.

• lt()

inherited from the lt() method of object

Return self

• class sqlalchemy.schema.PrimaryKeyConstraint(*columns, **kw)
• Bases: sqlalchemy.schema.ColumnCollectionConstraint

A table-level PRIMARY KEY constraint.

The PrimaryKeyConstraint object is present automaticallyon any Table object; it is assigned a set ofColumn objects corresponding to those marked withthe 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 usinga PrimaryKeyConstraint object explicitly; in this mode of usage,the “name” of the constraint can also be specified, as well as otheroptions 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 thePrimaryKeyConstraintdon’t match the columns that were marked as primary_key=True, if bothare present; in this case, the columns are taken strictly from thePrimaryKeyConstraint declaration, and those columns otherwisemarked as primary_key=True are ignored. This behavior is intended tobe backwards compatible with previous behavior.

Changed in version 0.9.2: Using a mixture of columns within aPrimaryKeyConstraint in addition to columns marked asprimary_key=True now emits a warning if the lists don’t match.The ultimate behavior of ignoring those columns marked with the flagonly is currently maintained for backwards compatibility; this warningmay raise an exception in a future release.

For the use case where specific options are to be specified on thePrimaryKeyConstraint, but the usual style of usingprimary_key=True flags is still desirable, an emptyPrimaryKeyConstraint may be specified, which will take on theprimary key column collection from the Table based on theflags:

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 nowbe specified for the purposes of establishing keyword arguments withthe constraint, independently of the specification of “primary key”columns within the Table itself; columns marked asprimary_key=True will be gathered into the empty constraint’scolumn collection.

• eq()

inherited from the eq() method of object

Return self==value.

• le()

inherited from the le() method of object

Return self<=value.

• lt()

inherited from the lt() method of object

Return self

• 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 Columndefinition is a shorthand equivalent for an unnamed, single columnUniqueConstraint.

• eq()

inherited from the eq() method of object

Return self==value.

• init(*columns, **kw)

inherited from theinit()method ofColumnCollectionConstraint

- 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 whenissuing DDL for this constraint.

  - 

initially – Optional string. If set, emit INITIALLY when issuing DDLfor this constraint.

  - 

**kw – other keyword arguments including dialect-specificarguments are propagated to the Constraint superclass.

• le()

inherited from the le() method of object

Return self<=value.

• lt()

inherited from the lt() method of object

Return self

• sqlalchemy.schema.conv(value, quote=None)
• Mark a string indicating that a name has already been convertedby a naming convention.

This is a string subclass that indicates a name that should not besubject to any further naming conventions.

E.g. when we create a Constraint using a naming conventionas 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 theconstraint_name token.

In some situations, such as in migration scripts, we may be renderingthe above CheckConstraint with a name that’s already beenconverted. In order to make sure the name isn’t double-modified, thenew name is applied using the schema.conv() marker. We canuse 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 constraintname here is final, and the name will render as "ck_t_x5" and not"ck_t_ck_t_x5"

New in version 0.9.4.

See also

Configuring Constraint Naming Conventions

Indexes

Indexes can be created anonymously (using an auto-generated name ix_<columnlabel>) for a single column using the inline index keyword onColumn, which also modifies the usage ofunique to apply the uniqueness to the index itself, instead of adding aseparate UNIQUE constraint. For indexes with specific names or which encompassmore than one column, use the Index construct,which requires a name.

Below we illustrate a Table with severalIndex objects associated. The DDL for “CREATEINDEX” 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)
CREATE TABLE mytable (
    col1 INTEGER,
    col2 INTEGER,
    col3 INTEGER,
    col4 INTEGER,
    col5 INTEGER,
    col6 INTEGER
)
CREATE INDEX ix_mytable_col1 ON mytable (col1)
CREATE UNIQUE INDEX ix_mytable_col2 ON mytable (col2)
CREATE UNIQUE INDEX myindex ON mytable (col5, col6)
CREATE INDEX idx_col34 ON mytable (col3, col4)

Note in the example above, the Index construct is createdexternally to the table which it corresponds, using Columnobjects directly. Index also supports“inline” definition inside the Table, using string names toidentify 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)
)

The Index object also supports its own create() method:

i = Index('someindex', mytable.c.col5)
sqli.create(engine)
CREATE INDEX someindex ON mytable (col5)

Functional Indexes

Index supports SQL and function expressions, as supported by thetarget backend. To create an index against a column using a descendingvalue, 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))

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, addingColumn 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 thefunc construct in conjunction with table-boundColumn objects:

Index("some_index", func.lower(sometable.c.name))

An Index can also be manually associated with a Table,either through inline declaration or usingTable.append_constraint(). When this approach is used, the namesof 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, thetext() 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 tospecify Index expressions, provided the Indexis explicitly associated with the Table.

See also

Indexes - General information on Index.

PostgreSQL-Specific Index Options - PostgreSQL-specific options available forthe Index construct.

MySQL Specific Index Options - MySQL-specific options available for theIndex construct.

Clustered Index Support - MSSQL-specific options available for theIndex construct.

• eq()

inherited from the eq() method of object

Return self==value.

• init(name, *expressions, **kw)

• Construct an index object.

  • Parameters

  • • name – The name of the index

    • *expressions – Column expressions to include in the index. The expressionsare normally instances of Column, but may alsobe arbitrary SQL expressions which ultimately refer to aColumn.

    • unique=False – Keyword only argument; if True, create a unique index.

    • quote=None – Keyword only argument; whether to apply quoting to the name ofthe index. Works in the same manner as that ofColumn.quote.

    • info=None –

Optional data dictionary which will be populatedinto the SchemaItem.info attribute of this object.

New in version 1.0.0.

  - 

**kw – Additional keyword arguments not mentioned above aredialect specific, and passed in the form<dialectname>_<argname>. See the documentation regarding anindividual dialect at Dialects for detail ondocumented arguments.

• le()

inherited from the le() method of object

Return self<=value.

• lt()

inherited from the lt() method of object

Return self