SqlAlchemy 2.0 中文文档（四十）（2）

SqlAlchemy 2.0 中文文档（四十）（1）https://developer.aliyun.com/article/1563000

将序列关联到 SERIAL 列

PostgreSQL 的 SERIAL 数据类型是一种自增类型，它意味着在发出 CREATE TABLE 时会隐式创建一个 PostgreSQL 序列。当为 Column 指定 Sequence 构造时，可以通过为 Sequence.optional 参数指定 True 值来表明在这种特定情况下不应使用它。这允许给定的 Sequence 用于没有其他替代主键生成系统的后端，但对于诸如 PostgreSQL 之类的后端，它会自动生成一个特定列的序列：

table = Table(
    "cartitems",
    metadata_obj,
    Column(
        "cart_id",
        Integer,
        # use an explicit Sequence where available, but not on
        # PostgreSQL where SERIAL will be used
        Sequence("cart_id_seq", start=1, optional=True),
        primary_key=True,
    ),
    Column("description", String(40)),
    Column("createdate", DateTime()),
)

在上面的例子中，对于 PostgreSQL 的 CREATE TABLE 将使用 SERIAL 数据类型来创建 cart_id 列，而 cart_id_seq 序列将被忽略。然而，在 Oracle 中，cart_id_seq 序列将被显式创建。

提示

SERIAL 和 SEQUENCE 的这种特定交互相当古老，与其他情况一样，改用 Identity 将简化操作，只需在所有支持的后端上使用 IDENTITY 即可。

单独执行序列

SEQUENCE 是 SQL 中的一级模式对象，并且可以在数据库中独立生成值。如果你有一个 Sequence 对象，可以直接将其传递给 SQL 执行方法，通过其“next value”指令来调用它：

with my_engine.connect() as conn:
    seq = Sequence("some_sequence", start=1)
    nextid = conn.execute(seq)

为了将 Sequence 的“next value”函数嵌入到类似 SELECT 或 INSERT 的 SQL 语句中，可以使用 Sequence.next_value() 方法，该方法将在语句编译时生成适合目标后端的 SQL 函数：

>>> my_seq = Sequence("some_sequence", start=1)
>>> stmt = select(my_seq.next_value())
>>> print(stmt.compile(dialect=postgresql.dialect()))
SELECT  nextval('some_sequence')  AS  next_value_1 

将 Sequence 与 MetaData 关联起来

对于要与任意 Table 对象关联的 Sequence，可以使用 Sequence.metadata 参数将 Sequence 关联到特定的 MetaData：

seq = Sequence("my_general_seq", metadata=metadata_obj, start=1)

这样的序列可以按照通常的方式与列关联起来：

table = Table(
    "cartitems",
    metadata_obj,
    seq,
    Column("description", String(40)),
    Column("createdate", DateTime()),
)

在上面的例子中，Sequence 对象被视为一个独立的模式构造，可以独立存在或在表之间共享。

显式地将 Sequence 与 MetaData 关联起来，可以实现以下行为：

• Sequence 会继承指定给目标 MetaData 的 MetaData.schema 参数，这会影响 CREATE / DROP DDL 的生成，以及 Sequence.next_value() 函数在 SQL 语句中的呈现方式。
  
• MetaData.create_all() 和 MetaData.drop_all() 方法将为此 Sequence 发出 CREATE / DROP，即使该 Sequence 未与此 MetaData 的任何成员 Table / Column 关联。
  

将 Sequence 关联为服务器端默认

注意

以下技术仅在 PostgreSQL 数据库中可用。它在 Oracle 中不起作用。

前面的部分演示了如何将 Sequence 关联到 Column 作为Python 端默认生成器：

Column(
    "cart_id",
    Integer,
    Sequence("cart_id_seq", metadata=metadata_obj, start=1),
    primary_key=True,
)

在上述情况下，当相关的 Table 要被创建 / 删除时，Sequence 将自动受到 CREATE SEQUENCE / DROP SEQUENCE DDL 的影响。但是，在发出 CREATE TABLE 时，该序列不会出现为该列的服务器端默认。

如果我们希望序列被用作服务器端默认，即使我们从 SQL 命令行向表中发出 INSERT 命令，我们可以使用 Column.server_default 参数，与序列的值生成函数一起使用，该函数可以从 Sequence.next_value() 方法获得。下面我们演示了相同的 Sequence 同时关联到 Column，既作为 Python 端的默认生成器，又作为服务器端的默认生成器：

cart_id_seq = Sequence("cart_id_seq", metadata=metadata_obj, start=1)
table = Table(
    "cartitems",
    metadata_obj,
    Column(
        "cart_id",
        Integer,
        cart_id_seq,
        server_default=cart_id_seq.next_value(),
        primary_key=True,
    ),
    Column("description", String(40)),
    Column("createdate", DateTime()),
)

或使用 ORM：

class CartItem(Base):
    __tablename__ = "cartitems"
    cart_id_seq = Sequence("cart_id_seq", metadata=Base.metadata, start=1)
    cart_id = Column(
        Integer, cart_id_seq, server_default=cart_id_seq.next_value(), primary_key=True
    )
    description = Column(String(40))
    createdate = Column(DateTime)

在发出“CREATE TABLE”语句时，在 PostgreSQL 上会发出：

CREATE  TABLE  cartitems  (
  cart_id  INTEGER  DEFAULT  nextval('cart_id_seq')  NOT  NULL,
  description  VARCHAR(40),
  createdate  TIMESTAMP  WITHOUT  TIME  ZONE,
  PRIMARY  KEY  (cart_id)
)

在 Python 端和服务器端默认生成上下文中放置Sequence可以确保“主键提取”逻辑在所有情况下都有效。  通常，启用序列的数据库也支持对 INSERT 语句使用 RETURNING，当发出此语句时，SQLAlchemy 会自动使用它。  但是，如果对特定插入未使用 RETURNING，则 SQLAlchemy 更愿意在 INSERT 语句本身之外“预执行”序列，这仅在序列作为  Python 端默认生成器函数时有效。

该示例还将Sequence直接与封闭的MetaData关联起来，这再次确保了Sequence与MetaData集合的参数完全关联，包括默认模式（如果有）。

另请参阅

序列/SERIAL/IDENTITY - 在 PostgreSQL 方言文档中

RETURNING 支持 - 在 Oracle 方言文档中

计算列（GENERATED ALWAYS AS）

1.3.11 版中新增。

Computed 构造允许将 Column 声明为“GENERATED ALWAYS AS”列，在 DDL 中，即由数据库服务器计算值的列。 该构造接受一个 SQL 表达式，通常使用字符串或 text() 构造进行文本声明，类似于 CheckConstraint 的方式。 然后，数据库服务器会解释该 SQL 表达式，以确定行内列的值。

示例:

from sqlalchemy import Table, Column, MetaData, Integer, Computed
metadata_obj = MetaData()
square = Table(
    "square",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("side", Integer),
    Column("area", Integer, Computed("side * side")),
    Column("perimeter", Integer, Computed("4 * side")),
)

在 PostgreSQL 12 后端上运行 square 表的 DDL 如下所示：

CREATE  TABLE  square  (
  id  SERIAL  NOT  NULL,
  side  INTEGER,
  area  INTEGER  GENERATED  ALWAYS  AS  (side  *  side)  STORED,
  perimeter  INTEGER  GENERATED  ALWAYS  AS  (4  *  side)  STORED,
  PRIMARY  KEY  (id)
)

值是在 INSERT 和 UPDATE 时持久化，还是在获取时计算，是数据库的实现细节；前者称为“存储”，后者称为“虚拟”。 一些数据库实现支持两者，但有些只支持其中一个。 可以指定可选的 Computed.persisted 标志为 True 或 False，以指示是否在 DDL 中渲染“STORED”或“VIRTUAL”关键字，但是如果目标后端不支持该关键字，则会引发错误； 如果将其设置为未设置，则将使用目标后端的有效默认值。

计算列 构造是 FetchedValue 对象的子类，并且会自行设置为目标 列 的“服务器默认值”和“服务器更新时生成器”，这意味着当生成 INSERT 和 UPDATE 语句时，它将被视为默认生成的列，以及当使用 ORM  时，它将被视为生成的列被获取。这包括它将作为数据库的 RETURNING 子句的一部分，对于支持 RETURNING  并且生成的值需要被急切地获取的数据库。

注

使用 计算列 构造定义的 列 可能不会存储除服务器应用之外的任何值；当尝试写入 INSERT 或 UPDATE 时，SQLAlchemy 目前的行为是将忽略该值。

“GENERATED ALWAYS AS” 目前已知受支持的数据库有：

• MySQL 版本 5.7 及以上
  
• MariaDB 10.x 系列及以上
  
• PostgreSQL 版本 12 及以上
  
• Oracle - 注意 RETURNING 在 UPDATE 中无法正常工作（在包含计算列的 UPDATE…RETURNING 被呈现时会发出警告）
  
• Microsoft SQL Server
  
• SQLite 版本 3.31 及以上
  

当 计算列 与不受支持的后端一起使用时，如果目标方言不支持它，则在尝试呈现构造时会引发 CompileError。否则，如果方言支持它但使用的特定数据库服务器版本不支持它，则在将 DDL 发送到数据库时会引发 DBAPIError 的子类，通常是 OperationalError。

请参见

计算列

自增列（GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY）

1.4 版本中新增。

Identity 构造允许将 列 声明为自增列，并在 DDL 中呈现为“GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY”。自增列的值由数据库服务器自动生成，使用增量（或减量）序列。该构造与 Sequence 共享大部分用于控制数据库行为的选项。

示例：

from sqlalchemy import Table, Column, MetaData, Integer, Identity, String
metadata_obj = MetaData()
data = Table(
    "data",
    metadata_obj,
    Column("id", Integer, Identity(start=42, cycle=True), primary_key=True),
    Column("data", String),
)

在 PostgreSQL 12 后端上运行时，data 表的 DDL 如下所示：

CREATE  TABLE  data  (
  id  INTEGER  GENERATED  BY  DEFAULT  AS  IDENTITY  (START  WITH  42  CYCLE)  NOT  NULL,
  data  VARCHAR,
  PRIMARY  KEY  (id)
)

数据库将在插入时为 id 列生成一个值，从 42 开始，如果语句尚未包含 id 列的值。身份列也可以要求数据库生成列的值，忽略语句中传递的值或者根据后端引发错误。要激活此模式，请在 Identity 构造函数中将参数 Identity.always 设置为 True。将上一个示例更新以包含此参数将生成以下 DDL：

CREATE  TABLE  data  (
  id  INTEGER  GENERATED  ALWAYS  AS  IDENTITY  (START  WITH  42  CYCLE)  NOT  NULL,
  data  VARCHAR,
  PRIMARY  KEY  (id)
)

Identity 构造函数是 FetchedValue 对象的子类，并将自己设置为目标 Column 的“服务器默认”生成器，这意味着当生成 INSERT 语句时，它将被视为默认生成列，以及在使用 ORM  时，它将被视为生成列。这包括它将作为数据库的 RETURNING 子句的一部分，对于支持 RETURNING  并且要急切获取生成的值的数据库来说，它将被提前获取。

当前已知支持 Identity 构造函数的包括：

• PostgreSQL 版本为 10。
  
• Oracle 版本为 12. 还支持传递 always=None 以启用默认生成模式，以及传递参数 on_null=True 以指定“ON NULL”与“BY DEFAULT”身份列一起使用。
  
• Microsoft SQL Server. MSSQL 使用一种自定义语法，仅支持 start 和 increment 参数，而忽略所有其他参数。
  

当 Identity 与不受支持的后端一起使用时，它会被忽略，并且会使用默认的 SQLAlchemy 自增列逻辑。

当 Column 同时指定 Identity 并将 Column.autoincrement 设置为 False 时，将引发错误。

另请参阅

Identity

默认对象 API

class sqlalchemy.schema.Computed

定义一个生成列，即“GENERATED ALWAYS AS”语法。

Computed构造是添加到Column对象的参数列表中的内联构造：

from sqlalchemy import Computed
Table('square', metadata_obj,
    Column('side', Float, nullable=False),
    Column('area', Float, Computed('side * side'))
)

请参阅下面链接的文档以获取完整详细信息。

版本 1.3.11 中的新功能。

另请参阅

计算列（GENERATED ALWAYS AS）

成员

init(), copy()

类签名

类sqlalchemy.schema.Computed（sqlalchemy.schema.FetchedValue，sqlalchemy.schema.SchemaItem)

method __init__(sqltext: _DDLColumnArgument, persisted: bool | None = None) → None

构造一个生成的 DDL 构造，以配合Column。

参数：

• sqltext –
  包含列生成表达式的字符串，该表达式将逐字使用，或者 SQL 表达式构造，例如text()对象。 如果以字符串形式给出，则将对象转换为text()对象。
  警告
  Computed.sqltext参数可以作为 Python 字符串参数传递给Computed，它将被视为受信任的 SQL 文本并按照给定的方式呈现。 不要将不受信任的输入传递给此参数。
  
• persisted –可选，控制数据库如何持久化此列。 可能的值为：

• None，默认值，将使用数据库定义的默认持久性。
  
• True，将呈现GENERATED ALWAYS AS ... STORED，或者如果支持的话，将呈现目标数据库的等效值。
  
• False，将呈现GENERATED ALWAYS AS ... VIRTUAL，或者如果支持的话，将呈现目标数据库的等效值。
  

• 当 DDL 发出到目标数据库时，如果数据库不支持持久性选项，则指定True或False可能会引发错误。 将此参数保留在其默认值None上可确保对所有支持GENERATED ALWAYS AS的数据库都能成功。
  

method copy(*, target_table: Table | None = None, **kw: Any) → Computed

自版本 1.4 起已弃用：Computed.copy()方法已弃用，并将在将来的版本中删除。

class sqlalchemy.schema.ColumnDefaul

列上的普通默认值。

这可能对应于一个常量，一个可调用函数，或者一个 SQL 子句。

每当使用 Column 的 default、onupdate 参数时，都会自动生成 ColumnDefault。ColumnDefault 也可以按位置传递。

例如，以下内容：

Column('foo', Integer, default=50)

等同于：

Column('foo', Integer, ColumnDefault(50))

类签名

class sqlalchemy.schema.ColumnDefault (sqlalchemy.schema.DefaultGenerator, abc.ABC)

class sqlalchemy.schema.DefaultClause

由 DDL 指定的 DEFAULT 列值。

DefaultClause 是一个 FetchedValue，在发出“CREATE TABLE”时也会生成一个“DEFAULT”子句。

每当使用 Column 的 server_default、server_onupdate 参数时，都会自动生成 DefaultClause。DefaultClause 也可以按位置传递。

例如，以下内容：

Column('foo', Integer, server_default="50")

等同于：

Column('foo', Integer, DefaultClause("50"))

类签名

class sqlalchemy.schema.DefaultClause (sqlalchemy.schema.FetchedValue)

class sqlalchemy.schema.DefaultGenerator

列默认值的基类。

此对象仅存在于 column.default 或 column.onupdate。它不作为服务器默认值有效。

类签名

class sqlalchemy.schema.DefaultGenerator (sqlalchemy.sql.expression.Executable, sqlalchemy.schema.SchemaItem)

class sqlalchemy.schema.FetchedValue

一个用于透明数据库端默认值的标记。

当数据库配置为为列提供一些自动默认值时，请使用 FetchedValue。

例如：

Column('foo', Integer, FetchedValue())

将指示某个触发器或默认生成器在插入期间为 foo 列创建一个新值。

另请参阅

标记隐式生成的值、时间戳和触发列

类签名

class sqlalchemy.schema.FetchedValue (sqlalchemy.sql.expression.SchemaEventTarget)

class sqlalchemy.schema.Sequence

表示一个命名的数据库序列。

Sequence 对象表示数据库序列的名称和配置参数。它还表示可以由 SQLAlchemy Engine 或 Connection “执行”的构造，为目标数据库渲染适当的 “下一个值” 函数并返回结果。

Sequence 通常与主键列相关联：

some_table = Table(
    'some_table', metadata,
    Column('id', Integer, Sequence('some_table_seq', start=1),
    primary_key=True)
)

当为上述 Table 发出 CREATE TABLE 时，如果目标平台支持序列，则还将发出 CREATE SEQUENCE 语句。对于不支持序列的平台，将忽略 Sequence 构造。

另请参阅

定义序列

CreateSequence

DropSequence

成员

init(), create(), drop(), next_value()

类签名

类 sqlalchemy.schema.Sequence (sqlalchemy.schema.HasSchemaAttr, sqlalchemy.schema.IdentityOptions, sqlalchemy.schema.DefaultGenerator)

method __init__(name: str, start: int | None = None, increment: int | None = None, minvalue: int | None = None, maxvalue: int | None = None, nominvalue: bool | None = None, nomaxvalue: bool | None = None, cycle: bool | None = None, schema: str | Literal[SchemaConst.BLANK_SCHEMA] | None = None, cache: int | None = None, order: bool | None = None, data_type: _TypeEngineArgument[int] | None = None, optional: bool = False, quote: bool | None = None, metadata: MetaData | None = None, quote_schema: bool | None = None, for_update: bool = False) → None

构造一个 Sequence 对象。

参数：

• name – 序列的名称。
  
• start –
  序列的起始索引。当向数据库发出 CREATE SEQUENCE 命令时，此值用作 “START WITH” 子句的值。如果为 None，则省略子句，大多数平台上表示起始值为 1。
  从版本 2.0 开始：要使 DDL 发出 “START WITH” 命令，Sequence.start 参数是必需的。这是对版本 1.4 中所做更改的逆转，该更改如果未包括 Sequence.start 将隐式地渲染 “START WITH 1”。有关更多详细信息，请参见序列构造将恢复为没有任何显式默认的“start”值；影响 MS SQL Server。
  
• increment – 序列的增量值。当向数据库发出 CREATE SEQUENCE 命令时，此值用作 “INCREMENT BY” 子句的值。如果为 None，则省略子句，大多数平台上表示增量为 1。
  
• minvalue – 序列的最小值。当将 CREATE SEQUENCE 命令发送到数据库时，此值用作“MINVALUE”子句的值。如果为None，则省略该子句，在大多数平台上表示升序和降序序列的最小值分别为 1 和-2⁶³-1。
  
• maxvalue – 序列的最大值。当将 CREATE SEQUENCE 命令发送到数据库时，此值用作“MAXVALUE”子句的值。如果为None，则省略该子句，在大多数平台上表示升序和降序序列的最大值分别为 2⁶³-1 和-1。
  
• nominvalue – 序列的无最小值。当将 CREATE SEQUENCE 命令发送到数据库时，此值用作“NO MINVALUE”子句的值。如果为None，则省略该子句，在大多数平台上表示升序和降序序列的最小值分别为 1 和-2⁶³-1。
  
• nomaxvalue – 序列的无最大值。当将 CREATE SEQUENCE 命令发送到数据库时，此值用作“NO MAXVALUE”子句的值。如果为None，则省略该子句，在大多数平台上表示升序和降序序列的最大值分别为 2⁶³-1 和-1。
  
• cycle – 允许序列在达到最大值或最小值时循环。当升序或降序序列分别达到最大值或最小值时，此值用于在将 CREATE SEQUENCE  命令发送到数据库时作为“CYCLE”子句。如果达到限制，则生成的下一个数字将分别是最小值或最大值。如果  cycle=False（默认值），则在序列达到其最大值后调用 nextval 将返回错误。
  
• schema – 序列的可选模式名称，如果位于除默认模式之外的模式中。当MetaData也存在时，选择模式名称的规则与Table.schema的规则相同。
  
• cache – 可选的整数值；提前计算序列中未来值的数量。渲染 Oracle 和 PostgreSQL 理解的 CACHE 关键字。
  
• order – 可选的布尔值；如果为True，则渲染 ORDER 关键字，Oracle 可理解此关键字，表示序列是有确定顺序的。可能需要使用 Oracle RAC 提供确定性排序。
  
• data_type –
  序列要返回的类型，对于允许我们在 INTEGER、BIGINT 等之间选择的方言（例如 mssql）。
  自 1.4.0 版本新增。
  
• optional – 布尔值，当为True时，表示这个Sequence对象只需在不提供其他方法生成主键标识符的后端上显式生成。目前，它基本上意味着“在 PostgreSQL 后端上不要创建这个序列，在那里，SERIAL 关键字会自动为我们创建一个序列”。
  
• quote – 布尔值，当为True或False时，显式地强制对 Sequence.name 进行引用或取消引用。当保持其默认值None时，将根据大小写和保留字规则进行正常的引用。
  
• quote_schema – 设置对schema名称的引用偏好。
  
• metadata –可选的 MetaData 对象，这个Sequence将与之关联。与 MetaData 关联的 Sequence 将获得以下功能：

• Sequence将继承指定给目标MetaData的MetaData.schema参数，这会影响创建/删除 DDL 的生成，如果有的话。
  
• Sequence.create() 和 Sequence.drop() 方法会自动使用绑定到 MetaData 对象的引擎（如果有的话）。
  
• MetaData.create_all() 和 MetaData.drop_all() 方法将为这个Sequence发出 CREATE / DROP，即使这个Sequence没有与任何属于这个MetaData的Table / Column相关联也是如此。
  

• 上述行为只有在通过此参数将 Sequence 显式关联到 MetaData 时才会发生。
  另请参见
  将 Sequence 与 MetaData 关联 - 对Sequence.metadata参数的完整讨论。
  
• for_update – 当与Column相关联时，表示应该在该列的表上对 UPDATE 语句调用此Sequence，而不是在 INSERT 语句中，当该列在语句中没有其他值时。
  

method create(bind: _CreateDropBind, checkfirst: bool = True) → None

在数据库中创建此序列。

method drop(bind: _CreateDropBind, checkfirst: bool = True) → None

从数据库中删除此序列。

method next_value() → Function[int]

返回一个next_value函数元素，该函数将在任何 SQL 表达式中为此Sequence呈现适当的增量函数。

class sqlalchemy.schema.Identity

定义一个 identity 列，即“GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY”语法。

Identity 构造是一个内联构造，添加到Column对象的参数列表中：

from sqlalchemy import Identity
Table('foo', metadata_obj,
    Column('id', Integer, Identity())
    Column('description', Text),
)

详细信息请参见下面的链接文档。

版本 1.4 中新增。

另请参阅

Identity 列（GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY）

成员

init(), copy()

类签名

class sqlalchemy.schema.Identity (sqlalchemy.schema.IdentityOptions, sqlalchemy.schema.FetchedValue, sqlalchemy.schema.SchemaItem)

method __init__(always: bool = False, on_null: bool | None = None, start: int | None = None, increment: int | None = None, minvalue: int | None = None, maxvalue: int | None = None, nominvalue: bool | None = None, nomaxvalue: bool | None = None, cycle: bool | None = None, cache: int | None = None, order: bool | None = None) → None

构造一个 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY 的 DDL 构造，以配合一个Column。

有关大多数参数的完整描述，请参阅Sequence文档。

注意

MSSQL 支持此构造作为在列上生成 IDENTITY 的首选替代方法，但它使用的非标准语法仅支持Identity.start和Identity.increment。所有其他参数都将被忽略。

参数：

• always – 一个布尔值，表示身份列的类型。如果指定为False，则用户指定的值优先。如果指定为True，则不接受用户指定的值（在某些后端，如 PostgreSQL，可以在 INSERT 中指定 OVERRIDING SYSTEM VALUE 或类似的内容来覆盖序列值）。一些后端也对此参数有一个默认值，None 可以用来省略在 DDL 中呈现这部分。如果后端没有默认值，则将其视为False。
  
• on_null – 设置为True 以指定在always=False身份列中与ON NULL一起使用。此选项仅在某些后端（如 Oracle）上受支持。
  
• start – 序列的起始索引。
  
• increment – 序列的增量值。
  
• minvalue – 序列的最小值。
  
• maxvalue – 序列的最大值。
  
• nominvalue – 序列没有最小值。
  
• nomaxvalue – 序列没有最大值。
  
• cycle – 允许序列在达到maxvalue或minvalue时循环。
  
• cache – 可选整数值；提前计算的序列中未来值的数量。
  
• order – 可选布尔值；如果为真，则呈现 ORDER 关键字。
  

method copy(**kw: Any) → Identity

自版本 1.4 弃用：Identity.copy() 方法已弃用，并将在将来的版本中移除。

定义约束和索引

原文：docs.sqlalchemy.org/en/20/core/constraints.html

这一部分将讨论 SQL 的约束和索引。在 SQLAlchemy 中，关键类包括ForeignKeyConstraint和Index。

定义外键

SQL 中的外键是一个表级构造，它将该表中的一个或多个列约束为仅允许存在于另一组列中的值，通常但不总是位于不同的表上。我们称被约束的列为外键列，它们被约束到的列为引用列。引用列几乎总是定义其拥有表的主键，尽管也有例外情况。外键是连接具有关系的行对的“关节”，SQLAlchemy 在其几乎每个操作的每个区域都赋予了这个概念非常深的重要性。

在 SQLAlchemy 中以及在 DDL 中，外键约束可以被定义为表子句中的附加属性，或者对于单列外键，它们可以选择地在单列的定义中指定。单列外键更常见，在列级别上是通过将ForeignKey对象构造为Column对象的参数来指定的：

user_preference = Table(
    "user_preference",
    metadata_obj,
    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)),
)

上面，我们定义了一个新表user_preference，其中每一行必须包含一个存在于user表的user_id列中的值。

参数ForeignKey最常见的形式是一个字符串，格式为*.，或者对于远程模式或者“owner”的表格是..*。它也可以是一个实际的Column对象，稍后我们将看到它是通过其c集合从现有的Table对象中访问的：

ForeignKey(user.c.user_id)

使用字符串的优点是，在首次需要时才解析user和user_preference之间的 Python 链接，因此表对象可以轻松地分布在多个模块中并按任何顺序定义。

外键也可以在表级别使用 ForeignKeyConstraint 对象定义。此对象可以描述单列或多列外键。多列外键称为复合外键，几乎总是引用具有复合主键的表。下面我们定义一个具有复合主键的表 invoice：

invoice = Table(
    "invoice",
    metadata_obj,
    Column("invoice_id", Integer, primary_key=True),
    Column("ref_num", Integer, primary_key=True),
    Column("description", String(60), nullable=False),
)

然后是一个引用 invoice 的复合外键的表 invoice_item：

invoice_item = Table(
    "invoice_item",
    metadata_obj,
    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"]
    ),
)

注意，ForeignKeyConstraint 是定义复合外键的唯一方式。虽然我们也可以将单独的 ForeignKey 对象放置在 invoice_item.invoice_id 和 invoice_item.ref_num 列上，但 SQLAlchemy 不会意识到这两个值应该配对在一起 - 它将是两个单独的外键约束，而不是一个引用两个列的单个复合外键。

通过 ALTER 创建/删除外键约束

我们在教程和其他地方看到的涉及 DDL 的外键行为表明，约束通常以“内联”的方式在 CREATE TABLE 语句中呈现，例如：

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)
)

CONSTRAINT .. FOREIGN KEY 指令用于在 CREATE TABLE 定义中以“内联”的方式创建约束。 MetaData.create_all() 和 MetaData.drop_all() 方法默认使用所有涉及的 Table 对象的拓扑排序，以便按照它们的外键依赖顺序创建和删除表（此排序也可通过 MetaData.sorted_tables 访问器获取）。

当涉及两个或更多外键约束参与“依赖循环”时，这种方法无法工作，其中一组表彼此相互依赖，假设后端执行外键（除了  SQLite、MySQL/MyISAM 之外总是是这样的情况）。因此，这些方法将在除 SQLite 之外的所有后端上将循环中的约束拆分为单独的  ALTER 语句，SQLite 不支持大多数形式的 ALTER。给定这样的模式：

node = Table(
    "node",
    metadata_obj,
    Column("node_id", Integer, primary_key=True),
    Column("primary_element", Integer, ForeignKey("element.element_id")),
)
element = Table(
    "element",
    metadata_obj,
    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"
    ),
)

当我们在像 PostgreSQL 这样的后端上调用 MetaData.create_all() 时，这两个表之间的循环被解析，并且约束被分别创建：

>>> with engine.connect() as conn:
...     metadata_obj.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) 

为了发出这些表的 DROP，相同的逻辑也适用，但是请注意，在 SQL 中，发出 DROP CONSTRAINT 需要约束具有名称。在上面的 'node' 表的情况下，我们还没有为此约束命名；因此系统将仅尝试发出对具有名称的约束的 DROP：

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

在无法解决循环的情况下，例如如果我们在这里没有对约束应用名称，则将收到以下错误：

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.

此错误仅适用于 DROP 情况，因为我们可以在 CREATE 情况下发出 “ADD CONSTRAINT” 而无需名称；数据库通常会自动分配一个名称。

ForeignKeyConstraint.use_alter 和 ForeignKey.use_alter 关键字参数可用于手动解决依赖循环。我们可以仅将此标志添加到 'element' 表中，如下所示：

element = Table(
    "element",
    metadata_obj,
    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",
    ),
)

在我们的 CREATE DDL 中，我们将只看到此约束的 ALTER 语句，而不是另一个约束的 ALTER 语句：

>>> with engine.connect() as conn:
...     metadata_obj.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 和 ForeignKey.use_alter 将要求约束具有名称，否则将生成类似以下的错误：

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

另请参阅

配置约束命名约定

sort_tables_and_constraints() ### ON UPDATE 和 ON DELETE

大多数数据库都支持外键值的级联，即当父行更新时，新值会放在子行中，或者当父行删除时，所有相应的子行都会被设置为 null  或删除。在数据定义语言中，这些是使用诸如 “ON UPDATE CASCADE”，“ON DELETE CASCADE” 和 “ON  DELETE SET NULL” 之类的短语来指定的，对应于外键约束。在 “ON UPDATE” 或 “ON DELETE”  之后的短语可能还允许使用特定于正在使用的数据库的其他短语。ForeignKey 和 ForeignKeyConstraint 对象通过 onupdate 和 ondelete 关键字参数支持生成此子句。该值是任何字符串，该字符串将在适当的 “ON UPDATE” 或 “ON DELETE” 短语之后输出：

child = Table(
    "child",
    metadata_obj,
    Column(
        "id",
        Integer,
        ForeignKey("parent.id", onupdate="CASCADE", ondelete="CASCADE"),
        primary_key=True,
    ),
)
composite = Table(
    "composite",
    metadata_obj,
    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",
    ),
)

请注意，这些子句在与 MySQL 配合使用时需要 InnoDB 表。它们在其他数据库上也可能不受支持。

另请参阅

有关将 ON DELETE CASCADE 与 ORM relationship() 结构集成的背景，请参阅以下部分：

使用外键 ON DELETE cascade 处理 ORM 关系

使用外键 ON DELETE 处理多对多关系 ## 唯一约束

可以使用 Column 上的 unique 关键字在单个列上匿名创建唯一约束。显式命名的唯一约束和/或具有多个列的约束通过 UniqueConstraint 表级构造创建。

from sqlalchemy import UniqueConstraint
metadata_obj = MetaData()
mytable = Table(
    "mytable",
    metadata_obj,
    # 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"),
)

检查约束

检查约束可以具有命名或未命名，可以在列或表级别使用 CheckConstraint 构造。检查约束的文本直接传递到数据库，因此具有有限的“数据库独立”行为。列级检查约束通常只应引用它们放置的列，而表级约束可以引用表中的任何列。

请注意，一些数据库不支持主动支持检查约束，例如旧版本的 MySQL（在 8.0.16 之前）。

from sqlalchemy import CheckConstraint
metadata_obj = MetaData()
mytable = Table(
    "mytable",
    metadata_obj,
    # 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"),
)
mytable.create(engine)
CREATE  TABLE  mytable  (
  col1  INTEGER  CHECK  (col1>5),
  col2  INTEGER,
  col3  INTEGER,
  CONSTRAINT  check1  CHECK  (col2  >  col3  +  5)
) 

主键约束

任何 Table 对象的主键约束在本质上是隐式存在的，基于标记有 Column.primary_key 标志的 Column 对象。PrimaryKeyConstraint 对象提供了对此约束的显式访问，其中包括直接配置的选项：

from sqlalchemy import PrimaryKeyConstraint
my_table = Table(
    "mytable",
    metadata_obj,
    Column("id", Integer),
    Column("version_id", Integer),
    Column("data", String(50)),
    PrimaryKeyConstraint("id", "version_id", name="mytable_pk"),
)

另请参阅

PrimaryKeyConstraint - 详细的 API 文档。

使用 Declarative ORM 扩展设置约束

Table 是 SQLAlchemy 核心构造，允许定义表元数据，其中可以用于 SQLAlchemy ORM 作为映射类的目标。Declarative 扩展允许自动创建 Table 对象，主要根据表的内容作为 Column 对象的映射。

要将表级约束对象（例如 ForeignKeyConstraint）应用于使用 Declarative 定义的表，请使用 __table_args__ 属性，详见 表配置。

配置约束命名约定

关系数据库通常会为所有约束和索引分配明确的名称。在常见情况下，使用CREATE TABLE创建表时，约束（如 CHECK、UNIQUE 和 PRIMARY KEY 约束）会与表定义一起内联生成，如果未另行指定名称，则数据库通常会自动为这些约束分配名称。当在使用诸如ALTER TABLE之类的命令更改数据库中的现有数据库表时，此命令通常需要为新约束指定显式名称，以及能够指定要删除或修改的现有约束的名称。

使用Constraint.name参数可以明确命名约束，对于索引，可以使用Index.name参数。然而，在约束的情况下，此参数是可选的。还有使用Column.unique和Column.index参数的用例，这些参数创建UniqueConstraint和Index对象时未明确指定名称。

对现有表和约束进行更改的用例可以由模式迁移工具（如Alembic）处理。然而，Alembic 和 SQLAlchemy 目前都不会为未指定名称的约束对象创建名称，导致可以更改现有约束的情况下，必须反向工程关系数据库用于自动分配名称的命名系统，或者必须注意确保所有约束都已命名。

与必须为所有Constraint和Index对象分配显式名称相比，可以使用事件构建自动命名方案。这种方法的优势在于，约束将获得一致的命名方案，无需在整个代码中使用显式名称参数，并且约定也适用于由Column.unique和Column.index参数生成的那些约束和索引。从 SQLAlchemy 0.9.2 开始，包含了这种基于事件的方法，并且可以使用参数MetaData.naming_convention进行配置。

配置元数据集合的命名约定

MetaData.naming_convention指的是一个接受Index类或单独的Constraint类作为键，Python 字符串模板作为值的字典。它还接受一系列字符串代码作为备用键，分别为外键、主键、索引、检查和唯一约束的"fk"、"pk"、"ix"、"ck"、"uq"。在这个字典中的字符串模板在与这个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_obj = MetaData(naming_convention=convention)

上述约定将为目标MetaData集合中的所有约束建立名称。例如，当我们创建一个未命名的UniqueConstraint时，我们可以观察到生成的名称：

>>> user_table = Table(
...     "user",
...     metadata_obj,
...     Column("id", Integer, primary_key=True),
...     Column("name", String(30), nullable=False),
...     UniqueConstraint("name"),
... )
>>> list(user_table.constraints)[1].name
'uq_user_name'

即使我们只使用Column.unique标志，这个相同的特性也会生效：

>>> user_table = Table(
...     "user",
...     metadata_obj,
...     Column("id", Integer, primary_key=True),
...     Column("name", String(30), nullable=False, unique=True),
... )
>>> list(user_table.constraints)[1].name
'uq_user_name'

命名约定方法的一个关键优势是，名称是在 Python 构建时建立的，而不是在 DDL 发出时建立的。当使用 Alembic 的--autogenerate功能时，这个特性的效果是，当生成新的迁移脚本时，命名约定将是明确的：

def upgrade():
    op.create_unique_constraint("uq_user_name", "user", ["name"])

上述的"uq_user_name"字符串是从--autogenerate在我们的元数据中定位的UniqueConstraint对象复制而来。

可用的令牌包括 %(table_name)s、%(referred_table_name)s、%(column_0_name)s、%(column_0_label)s、%(column_0_key)s、%(referred_column_0_name)s，以及每个令牌的多列版本，包括 %(column_0N_name)s、%(column_0_N_name)s、%(referred_column_0_N_name)s，它们以带有或不带有下划线的形式呈现所有列名称。关于 MetaData.naming_convention 的文档进一步详细说明了这些约定。

默认命名约定

MetaData.naming_convention 的默认值处理了长期以来 SQLAlchemy 的行为，即使用 Column.index 参数创建 Index 对象时分配名称的情况：

>>> from sqlalchemy.sql.schema import DEFAULT_NAMING_CONVENTION
>>> DEFAULT_NAMING_CONVENTION
immutabledict({'ix': 'ix_%(column_0_label)s'})

长名称的截断

当生成的名称，特别是使用多列令牌的名称，超出目标数据库的标识符长度限制（例如，PostgreSQL 具有 63 个字符的限制）时，名称将使用基于长名称的 md5 哈希的 4 个字符后缀进行确定性截断。例如，以下命名约定将根据使用的列名生成非常长的名称：

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

在 PostgreSQL 方言中，长度超过 63 个字符的名称将被截断，如以下示例所示：

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)
)

上述后缀 a79e 基于长名称的 md5 哈希，并且每次生成相同的值，以便为给定模式生成一致的名称。

创建用于命名约定的自定义令牌

还可以通过在 naming_convention 字典中指定额外的令牌和可调用对象来添加新的令牌。例如，如果我们想要使用 GUID 方案对外键约束进行命名，我们可以这样做：

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

在创建新的 ForeignKeyConstraint 时，我们将获得以下名称：

>>> metadata_obj = MetaData(naming_convention=convention)
>>> user_table = Table(
...     "user",
...     metadata_obj,
...     Column("id", Integer, primary_key=True),
...     Column("version", Integer, primary_key=True),
...     Column("data", String(30)),
... )
>>> address_table = Table(
...     "address",
...     metadata_obj,
...     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

另请参阅

MetaData.naming_convention - 附加使用详细信息以及所有可用命名组件的列表。

命名约束的重要性 - 在 Alembic 文档中。

版本 1.3.0 中的新功能：添加了诸如 %(column_0_N_name)s 等多列命名令牌。超出目标数据库字符限制的生成名称将被确定性地截断。

命名 CHECK 约束

CheckConstraint 对象配置针对任意 SQL 表达式，该表达式可以有任意数量的列，并且通常使用原始 SQL 字符串进行配置。因此，通常与 CheckConstraint 一起使用的约定是，我们希望对象已经有一个名称，然后我们使用其他约定元素增强它。一个典型的约定是 "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"),
)

上述表格将产生名称 ck_foo_value_gt_5：

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

CheckConstraint 还支持 %(columns_0_name)s token；我们可以通过确保在约束表达式中使用 Column 或 column() 元素来利用它，无论是通过单独声明约束表达式还是内联到表中：

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

或通过内联使用 column()：

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

两者都将产生名称 ck_foo_value：

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

“column zero”的名称确定是通过扫描给定表达式中的列对象来执行的。如果表达式中有多个列，扫描将使用确定性搜索，但是表达式的结构将决定哪个列被标记为“column zero”。### 针对布尔、枚举和其他模式类型进行命名配置

SchemaType 类指的是诸如 Boolean 和 Enum 等类型对象，它们生成与类型相伴随的 CHECK 约束。这里约束的名称最直接是通过发送“name”参数来设置的，例如 Boolean.name：

Table("foo", metadata_obj, Column("flag", Boolean(name="ck_foo_flag")))

命名约定功能也可以与这些类型结合使用，通常是通过使用包含 %(constraint_name)s 的约定，然后将名称应用于类型：

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

上述表格将产生约束名称 ck_foo_flag_bool：

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

SchemaType类使用特殊的内部符号，以便命名约定仅在 DDL 编译时确定。在 PostgreSQL 上，有一个原生的 BOOLEAN 类型，因此不需要Boolean的 CHECK 约束；即使为检查约束设置了命名约定，我们也可以安全地设置不带名称的Boolean类型。如果我们针对没有原生 BOOLEAN 类型的数据库运行，如 SQLite 或 MySQL，那么仅当我们运行时才会参考此约定 CHECK 约束。

CHECK 约束也可以使用column_0_name标记，与SchemaType很好地配合使用，因为这些约束只有一个列：

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

以上模式将产生：

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

使用 ORM 声明式混合与命名约定

使用命名约定功能与 ORM 声明性混合一起使用时，必须为每个实际表映射的子类存在单独的约束对象。有关背景和示例，请参见使用命名约定在混合上创建索引和约束部分。

SqlAlchemy 2.0 中文文档（四十）（3）https://developer.aliyun.com/article/1563002