diff options
author | Mike Bayer <mike_mp@zzzcomputing.com> | 2021-01-29 13:43:38 -0500 |
---|---|---|
committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2021-01-29 13:46:12 -0500 |
commit | ec9df97277ca4dfe7a7e7b29def0742ccd0235f8 (patch) | |
tree | 5c4470f46e5f3201c4bf7cd475380c78c97ee731 | |
parent | 893c4e7f89ae4018f286216e1cfc1b1b9341c939 (diff) | |
download | sqlalchemy-ec9df97277ca4dfe7a7e7b29def0742ccd0235f8.tar.gz |
Clarify Column.index / Column.unique parameters
These parameters need to be more clear that they cause a
constraint / index object to be generated. Clarify the rules
by which this occurs and include contextual information about
naming conventions as well.
Change-Id: I8dc96ead4457215abed391fd1b9f732a1eef6e09
References: #5887
(cherry picked from commit aff54c0bd8f75d324f1a4a8601a3d6f28739439e)
-rw-r--r-- | doc/build/core/constraints.rst | 32 | ||||
-rw-r--r-- | lib/sqlalchemy/sql/schema.py | 125 |
2 files changed, 137 insertions, 20 deletions
diff --git a/doc/build/core/constraints.rst b/doc/build/core/constraints.rst index 9e6ba0b73..365c94ad1 100644 --- a/doc/build/core/constraints.rst +++ b/doc/build/core/constraints.rst @@ -312,6 +312,8 @@ They may also not be supported on other databases. :ref:`passive_deletes_many_to_many` +.. _schema_unique_constraint: + UNIQUE Constraint ----------------- @@ -450,6 +452,9 @@ and :paramref:`_schema.Column.index` parameters. As of SQLAlchemy 0.9.2 this event-based approach is included, and can be configured using the argument :paramref:`_schema.MetaData.naming_convention`. +Configuring a Naming Convention for a MetaData Collection +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + :paramref:`_schema.MetaData.naming_convention` refers to a dictionary which accepts the :class:`.Index` class or individual :class:`.Constraint` classes as keys, and Python string templates as values. It also accepts a series of @@ -506,14 +511,6 @@ will be explicit when a new migration script is generated:: The above ``"uq_user_name"`` string was copied from the :class:`.UniqueConstraint` object that ``--autogenerate`` located in our metadata. -The default value for :paramref:`_schema.MetaData.naming_convention` handles -the long-standing SQLAlchemy behavior of assigning a name to a :class:`.Index` -object that is created using the :paramref:`_schema.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 as @@ -523,6 +520,22 @@ column names separated with or without an underscore. The documentation for :paramref:`_schema.MetaData.naming_convention` has further detail on each of these conventions. +.. _constraint_default_naming_convention: + +The Default Naming Convention +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +The default value for :paramref:`_schema.MetaData.naming_convention` handles +the long-standing SQLAlchemy behavior of assigning a name to a :class:`.Index` +object that is created using the :paramref:`_schema.Column.index` parameter:: + + >>> from sqlalchemy.sql.schema import DEFAULT_NAMING_CONVENTION + >>> DEFAULT_NAMING_CONVENTION + immutabledict({'ix': 'ix_%(column_0_label)s'}) + +Truncation of Long Names +~~~~~~~~~~~~~~~~~~~~~~~~~ + 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 be @@ -557,6 +570,9 @@ The above suffix ``a79e`` is based on the md5 hash of the long name and will generate the same value every time to produce consistent names for a given schema. +Creating Custom Tokens for Naming Conventions +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + 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 diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py index f41059d23..ee5a1aa21 100644 --- a/lib/sqlalchemy/sql/schema.py +++ b/lib/sqlalchemy/sql/schema.py @@ -1252,11 +1252,57 @@ class Column(DialectKWArgs, SchemaItem, ColumnClause): application, including ORM attribute mapping; the ``name`` field is used only when rendering SQL. - :param index: When ``True``, indicates that the column is indexed. - This is a shortcut for using a :class:`.Index` construct on the - table. To specify indexes with explicit names or indexes that - contain multiple columns, use the :class:`.Index` construct - instead. + :param index: When ``True``, indicates that a :class:`_schema.Index` + construct will be automatically generated for this + :class:`_schema.Column`, which will result in a "CREATE INDEX" + statement being emitted for the :class:`_schema.Table` when the DDL + create operation is invoked. + + Using this flag is equivalent to making use of the + :class:`_schema.Index` construct explicitly at the level of the + :class:`_schema.Table` construct itself:: + + Table( + "some_table", + metadata, + Column("x", Integer), + Index("ix_some_table_x", "x") + ) + + To add the :paramref:`_schema.Index.unique` flag to the + :class:`_schema.Index`, set both the + :paramref:`_schema.Column.unique` and + :paramref:`_schema.Column.index` flags to True simultaneously, + which will have the effect of rendering the "CREATE UNIQUE INDEX" + DDL instruction instead of "CREATE INDEX". + + The name of the index is generated using the + :ref:`default naming convention <constraint_default_naming_convention>` + which for the :class:`_schema.Index` construct is of the form + ``ix_<tablename>_<columnname>``. + + As this flag is intended only as a convenience for the common case + of adding a single-column, default configured index to a table + definition, explicit use of the :class:`_schema.Index` construct + should be preferred for most use cases, including composite indexes + that encompass more than one column, indexes with SQL expressions + or ordering, backend-specific index configuration options, and + indexes that use a specific name. + + .. note:: the :attr:`_schema.Column.index` attribute on + :class:`_schema.Column` + **does not indicate** if this column is indexed or not, only + if this flag was explicitly set here. To view indexes on + a column, view the :attr:`_schema.Table.indexes` collection + or use :meth:`_reflection.Inspector.get_indexes`. + + .. seealso:: + + :ref:`schema_indexes` + + :ref:`constraint_naming_conventions` + + :paramref:`_schema.Column.unique` :param info: Optional data dictionary which will be populated into the :attr:`.SchemaItem.info` attribute of this object. @@ -1346,12 +1392,67 @@ class Column(DialectKWArgs, SchemaItem, ColumnClause): reserved word. This flag is only needed to force quoting of a reserved word which is not known by the SQLAlchemy dialect. - :param unique: When ``True``, indicates that this column contains a - unique constraint, or if ``index`` is ``True`` as well, indicates - that the :class:`.Index` should be created with the unique flag. - To specify multiple columns in the constraint/index or to specify - an explicit name, use the :class:`.UniqueConstraint` or - :class:`.Index` constructs explicitly. + :param unique: When ``True``, and the :paramref:`_schema.Column.index` + parameter is left at its default value of ``False``, + indicates that a :class:`_schema.UniqueConstraint` + construct will be automatically generated for this + :class:`_schema.Column`, + which will result in a "UNIQUE CONSTRAINT" clause referring + to this column being included + in the ``CREATE TABLE`` statement emitted, when the DDL create + operation for the :class:`_schema.Table` object is invoked. + + When this flag is ``True`` while the + :paramref:`_schema.Column.index` parameter is simultaneously + set to ``True``, the effect instead is that a + :class:`_schema.Index` construct which includes the + :paramref:`_schema.Index.unique` parameter set to ``True`` + is generated. See the documentation for + :paramref:`_schema.Column.index` for additional detail. + + Using this flag is equivalent to making use of the + :class:`_schema.UniqueConstraint` construct explicitly at the + level of the :class:`_schema.Table` construct itself:: + + Table( + "some_table", + metadata, + Column("x", Integer), + UniqueConstraint("x") + ) + + The :paramref:`_schema.UniqueConstraint.name` parameter + of the unique constraint object is left at its default value + of ``None``; in the absence of a :ref:`naming convention <constraint_naming_conventions>` + for the enclosing :class:`_schema.MetaData`, the UNIQUE CONSTRAINT + construct will be emitted as unnamed, which typically invokes + a database-specific naming convention to take place. + + As this flag is intended only as a convenience for the common case + of adding a single-column, default configured unique constraint to a table + definition, explicit use of the :class:`_schema.UniqueConstraint` construct + should be preferred for most use cases, including composite constraints + that encompass more than one column, backend-specific index configuration options, and + constraints that use a specific name. + + .. note:: the :attr:`_schema.Column.unique` attribute on + :class:`_schema.Column` + **does not indicate** if this column has a unique constraint or + not, only if this flag was explicitly set here. To view + indexes and unique constraints that may involve this column, + view the + :attr:`_schema.Table.indexes` and/or + :attr:`_schema.Table.constraints` collections or use + :meth:`_reflection.Inspector.get_indexes` and/or + :meth:`_reflection.Inspector.get_unique_constraints` + + .. seealso:: + + :ref:`schema_unique_constraint` + + :ref:`constraint_naming_conventions` + + :paramref:`_schema.Column.index` :param system: When ``True``, indicates this is a "system" column, that is a column which is automatically made available by the @@ -1370,7 +1471,7 @@ class Column(DialectKWArgs, SchemaItem, ColumnClause): parameter to :class:`_schema.Column`. - """ + """ # noqa E501 name = kwargs.pop("name", None) type_ = kwargs.pop("type_", None) |