Convention Configuration¶

Overview¶

But there is also a better way to go, which is to make use of a feature new in SQLAlchemy 0.9.2 which makes use of the events behind the scenes known as naming_convention. Here, we can create a new MetaData object while passing a dictionary referring to a naming scheme:

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)

If we define our models using a MetaData as above, the given naming convention dictionary will be used to provide names for all constraints and indexes.

References¶

See also Configuring Constraint Naming Conventions - SQLAlchemy overview of naming convention support https://alembic.sqlalchemy.org/en/latest/naming.html

ORM Config Metadata Convention¶

Overview¶

How to implement in ORM engine¶

Based on the design, the ORM class has various components thoughts

It also accepts a series of string-codes as alternative keys, "fk" for foreign key "pk" for primary key "ix" for index "ck" for check "uq" for unique constraint

Configuring a Naming Convention for a MetaData Collection 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_obj = MetaData(naming_convention=convention)

and we can config the element based on the scripts, they call custom-token

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",
}

Naming CHECK Constraints The CheckConstraint object is configured against an arbitrary SQL expression, which can have any number of columns present, and additionally is often configured using a raw SQL string. Therefore a common convention to use with CheckConstraint is one where we expect the object to 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*obj = MetaData( naming_convention={"ck": "ck*%(table*name)s*%(constraint_name)s"} )

Table( "foo", metadata_obj, Column("value", Integer), CheckConstraint("value > 5", name="value_gt_5"), )

Very well document at https://docs.sqlalchemy.org/en/20/core/constraints.html

For the list of conventions

https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.MetaData.params.naming_convention

The built-in names are as follows, some of which may only be available for certain types of constraint:

%(table_name)s - the name of the Table object associated with the constraint.

%(referred_table_name)s - the name of the Table object associated with the referencing target of a ForeignKeyConstraint.

%(column_0_name)s - the name of the Column at index position "0" within the constraint.

%(column_0N_name)s - the name of all Column objects in order within the constraint, joined without a separator.

%(column_0_N_name)s - the name of all Column objects in order within the constraint, joined with an underscore as a separator.

%(column_0_label)s, %(column_0N_label)s, %(column_0_N_label)s - the label of either the zeroth Column or all Columns, separated with or without an underscore

%(column_0_key)s, %(column_0N_key)s, %(column_0_N_key)s - the key of either the zeroth Column or all Columns, separated with or without an underscore

%(referred_column_0_name)s, %(referred_column_0N_name)s %(referred_column_0_N_name)s, %(referred_column_0_key)s, %(referred_column_0N_key)s, … column tokens which render the names/keys/labels of columns that are referenced by a ForeignKeyConstraint.

%(constraint_name)s - a special key that refers to the existing name given to the constraint. When this key is present, the Constraint object’s existing name will be replaced with one that is composed from template string that uses this token. When this token is present, it is required that the Constraint is given an explicit name ahead of time.

user-defined: any additional token may be implemented by passing it along with a fn(constraint, table) callable to the naming_convention dictionary.

Reference¶

Defining Constraints and Indexes ↩