The Importance of Naming Constraints

命名约束的重要性

An important topic worth mentioning is that of constraint naming conventions. As we’ve proceeded here, we’ve talked about adding tables and columns, and we’ve also hinted at lots of other operations listed in Operation Reference such as those which support adding or dropping constraints like foreign keys and unique constraints. The way these constraints are referred to in migration scripts is by name, however these names by default are in most cases generated by the relational database in use, when the constraint is created. For example, if you emitted two CREATE TABLE statements like this on Postgresql:

值得一提的一个重要主题是约束命名约定。正如我们在这里进行的那样，我们已经讨论了添加表和列，并且我们还暗示了操作参考中列出的许多其他操作，例如支持添加或删除外键和唯一约束等约束的操作。在迁移脚本中引用这些约束的方式是按名称，但是在大多数情况下，这些名称默认情况下是由使用的关系数据库在创建约束时生成的。例如，如果您在 Postgresql 上发出两个这样的 CREATE TABLE 语句：

test=> CREATE TABLE user_account (id INTEGER PRIMARY KEY);
CREATE TABLE
test=> CREATE TABLE user_order (
test(>   id INTEGER PRIMARY KEY,
test(>   user_account_id INTEGER REFERENCES user_account(id));
CREATE TABLE

Suppose we wanted to DROP the REFERENCES that we just applied to the user_order.user_account_id column, how do we do that? At the prompt, we’d use ALTER TABLE <tablename> DROP CONSTRAINT <constraint_name>, or if using Alembic we’d be using Operations.drop_constraint(). But both of those functions need a name - what’s the name of this constraint?

假设我们想要删除刚刚应用于 user_order.user_account_id 列的 REFERENCES ，我们该怎么做？在提示符下，我们将使用 ALTER TABLE <tablename> DROP CONSTRAINT <constraint_name>，或者如果使用 Alembic，我们将使用 Operations.drop_constraint()。但是这两个函数都需要一个名字——这个约束的名字是什么？

It does have a name, which in this case we can figure out by looking at the Postgresql catalog tables:

它确实有一个名称，在这种情况下，我们可以通过查看 Postgresql 目录表来确定：

test=> SELECT r.conname FROM
test->  pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
test->  JOIN pg_catalog.pg_constraint r  ON c.oid = r.conrelid
test->  WHERE c.relname='user_order' AND r.contype = 'f'
test-> ;
             conname
---------------------------------
 user_order_user_account_id_fkey
(1 row)

The name above is not something that Alembic or SQLAlchemy created; user_order_user_account_id_fkey is a naming scheme used internally by Postgresql to name constraints that are otherwise not named.

上面的名字不是 Alembic 或 SQLAlchemy 创建的； user_order_user_account_id_fkey 是 Postgresql 内部使用的命名方案，用于命名未命名的约束。

This scheme doesn’t seem so complicated, and we might want to just use our knowledge of it so that we know what name to use for our Operations.drop_constraint() call. But is that a good idea? What if for example we needed our code to run on Oracle as well. OK, certainly Oracle uses this same scheme, right? Or if not, something similar. Let’s check:

这个方案似乎并不复杂，我们可能只想使用我们对它的了解，以便我们知道为 Operations.drop_constraint() 调用使用什么名称。但这是个好主意吗？例如，如果我们也需要我们的代码在 Oracle 上运行怎么办。好的，Oracle 肯定使用相同的方案，对吧？或者如果没有，类似的东西。让我们检查：

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> CREATE TABLE user_account (id INTEGER PRIMARY KEY);

Table created.

SQL> CREATE TABLE user_order (
  2     id INTEGER PRIMARY KEY,
  3     user_account_id INTEGER REFERENCES user_account(id));

Table created.

SQL> SELECT constraint_name FROM all_constraints WHERE
  2     table_name='USER_ORDER' AND constraint_type in ('R');

CONSTRAINT_NAME
-----------------------------------------------------
SYS_C0029334

Oh, we can see that is…..much worse. Oracle’s names are entirely unpredictable alphanumeric codes, and this will make being able to write migrations quite tedious, as we’d need to look up all these names.

哦，我们可以看到那是……更糟。 Oracle 的名称是完全不可预测的字母数字代码，这将使编写迁移变得非常乏味，因为我们需要查找所有这些名称。

The solution to having to look up names is to make your own names. This is an easy, though tedious thing to do manually. For example, to create our model in SQLAlchemy ensuring we use names for foreign key constraints would look like:

必须查找名称的解决方案是自己命名。手动完成这是一件容易但乏味的事情。例如，要在 SQLAlchemy 中创建我们的模型，确保我们使用名称作为外键约束，如下所示：

from sqlalchemy import MetaData, Table, Column, Integer, ForeignKey

meta = MetaData()

user_account = Table('user_account', meta,
                  Column('id', Integer, primary_key=True)
              )

user_order = Table('user_order', meta,
                  Column('id', Integer, primary_key=True),
                  Column('user_order_id', Integer,
                    ForeignKey('user_account.id', name='fk_user_order_id'))
              )

Simple enough, though this has some disadvantages. The first is that it’s tedious; we need to remember to use a name for every ForeignKey object, not to mention every UniqueConstraint, CheckConstraint, Index, and maybe even PrimaryKeyConstraint as well if we wish to be able to alter those too, and beyond all that, all the names have to be globally unique. Even with all that effort, if we have a naming scheme in mind, it’s easy to get it wrong when doing it manually each time.

很简单，尽管这有一些缺点。首先是乏味；我们需要记住为每个 ForeignKey 对象使用一个名称，更不用说每个 UniqueConstraint, CheckConstraint, Index, 甚至 PrimaryKeyConstraint，如果我们也希望能够更改它们，除此之外，所有名称都必须是全局唯一的。即使付出了所有努力，如果我们有一个命名方案，每次手动执行时很容易出错。

What’s worse is that manually naming constraints (and indexes) gets even more tedious in that we can no longer use convenience features such as the .unique=True or .index=True flag on Column:

更糟糕的是，手动命名约束（和索引）变得更加乏味，因为我们不能再在列上使用 .unique=True 或 .index=True 标志等便利功能：

user_account = Table('user_account', meta,
                  Column('id', Integer, primary_key=True),
                  Column('name', String(50), unique=True)
              )

Above, the unique=True flag creates a UniqueConstraint, but again, it’s not named. If we want to name it, manually we have to forego the usage of unique=True and type out the whole constraint:

上面，unique=True 标志创建了一个 UniqueConstraint ，但同样，它没有命名。如果我们想命名它，我们必须手动放弃使用 unique=True 并输入整个约束：

user_account = Table('user_account', meta,
                  Column('id', Integer, primary_key=True),
                  Column('name', String(50)),
                  UniqueConstraint('name', name='uq_user_account_name')
              )

There’s a solution to all this naming work, which is to use an automated naming convention. For some years, SQLAlchemy has encourgaged the use of DDL Events in order to create naming schemes. The after_parent_attach() event in particular is the best place to intercept when Constraint and Index objects are being associated with a parent Table object, and to assign a .name to the constraint while making use of the name of the table and associated columns.

所有这些命名工作都有一个解决方案，那就是使用 自动命名约定。多年来，SQLAlchemy 一直鼓励使用 DDL 事件来创建命名方案。当约束和索引对象与父表对象关联时，after_parent_attach() 事件是拦截的最佳位置，并在使用表名和关联列的名称时为约束分配 .name。

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:

但是还有一个更好的方法，那就是利用 SQLAlchemy 0.9.2 版本中的一个新特性，它利用称为 naming_convention 的幕后事件。在这里，我们可以创建一个新的 MetaData 对象，同时传递一个引用命名方案的字典：

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.

如果我们使用上面的 MetaData 定义我们的模型，那么给定的命名约定字典将用于为所有约束和索引提供名称。

alembic 中文文档

The Importance of Naming Constraints