定义外键
A 外键 在SQL中是一个表级构造,它约束该表中的一个或多个列,使其只允许存在于不同的列集中的值,通常但不总是位于不同的表中。我们称之为约束 外键 列和它们被约束到的列 引用的 柱。被引用的列几乎总是为其所属表定义主键,尽管有例外。外键是连接成对行的“关节”,这些行彼此之间具有关系,而sqlAlchemy在其操作的几乎每个领域都非常重视这个概念。
在SQLAlchemy和DDL中,可以将外键约束定义为table子句中的附加属性,或者对于单列外键,可以在单列定义中选择指定它们。单列外键更常见,在列级别通过构造 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_id
列中也存在 user
表的 user_id
列。
论点 ForeignKey
通常是窗体的字符串 <tablename>.<columnname> 或远程架构中的表或表单的“所有者” <schemaname>.<tablename>.<columnname> . 它也可能是一个实际的 Column
对象,稍后我们将看到,它是从现有的 Table
对象通过其 c
收藏:
ForeignKey(user.c.user_id)
使用字符串的优势在于 user
和 user_preference
只有在第一次需要时才解析,这样表对象就可以轻松地分布在多个模块中并以任意顺序定义。
也可以在表级别定义外键,使用 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_item
具有复合外键引用 invoice
::
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语句中呈现为“inline”,例如:
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
指令用于在创建表定义中以“内联”方式创建约束。这个 MetaData.create_all()
和 MetaData.drop_all()
默认情况下,方法会使用拓扑类型 Table
所涉及的对象,以便按照表的外键依赖性的顺序创建和删除表(也可以通过 MetaData.sorted_tables
访问器)。
如果一个“依赖循环”中包含两个或多个外键约束,并且一组表相互依赖,则这种方法无法工作,假定后端强制使用外键(除了sqlite、mysql/myisam之外,始终如此)。因此,这些方法将在这样一个循环中将约束分解为单独的alter语句,位于除不支持大多数形式alter的sqlite之外的所有后端。给出如下模式:
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'
)
)
当我们召唤 MetaData.create_all()
在PostgreSQL后端等后端,解决这两个表之间的循环,并分别创建约束:
>>> 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约束,需要该约束有一个名称。在这种情况下 'node'
上表中,我们没有命名这个约束;因此,系统将尝试只为那些命名为:
>>> 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.
此错误仅适用于放置案例,因为我们可以在不带名称的创建案例中发出“添加约束”;数据库通常会自动分配一个。
这个 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语句,而不会看到另一个约束:
>>> 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
当与DROP操作一起使用时,将要求命名约束,否则将生成如下错误:
sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint
ForeignKeyConstraint(...); it has no name
在 1.0.0 版更改: - The ForeignKeyConstraint.use_alter
flag can be used with an un-named constraint; only the DROP operation
will emit a specific error when actually called upon.
更新和删除时
大多数数据库支持 级联 在外键值中,即当更新父行时,新值将放置在子行中,或者当删除父行时,所有相应的子行都将设置为空或已删除。在数据定义语言中,使用与外键约束相对应的短语(如“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"
)
)
请注意,这些条款要求 InnoDB
与MySQL一起使用时的表。其他数据库也可能不支持它们。
配置约束命名约定
关系数据库通常为所有约束和索引指定显式名称。在创建表的常见情况下,使用 CREATE TABLE
如果在表定义中生成诸如check、unique和primary key约束之类的约束,则数据库通常有一个系统,在该系统中,如果没有另外指定名称,则会自动将名称分配给这些约束。当使用诸如 ALTER TABLE
,此命令通常需要为新约束指定显式名称,并且能够指定要删除或修改的现有约束的名称。
可以使用 Constraint.name
参数,对于索引, Index.name
参数。但是,在约束的情况下,此参数是可选的。还有一些使用 Column.unique
和 Column.index
创建的参数 UniqueConstraint
和 Index
未指定显式名称的对象。
修改现有表和约束的用例可以由模式迁移工具处理,例如 Alembic . 但是,alembic和sqlacalchemy目前都没有为未指定名称的约束对象创建名称,这导致了能够更改现有约束意味着必须对关系数据库使用的命名系统进行反向工程以自动分配名称,或者必须注意确保约束已命名。
与必须给所有人指定明确的名字不同 Constraint
和 Index
对象,可以使用事件构造自动命名方案。这种方法的优点是,约束将获得一致的命名方案,而不需要在整个代码中使用显式的名称参数,而且对于由 Column.unique
和 Column.index
参数。从SQLAlchemy 0.9.2开始,此基于事件的方法包括在内,并且可以使用参数进行配置 MetaData.naming_convention
.
默认命名约定
的默认值 MetaData.naming_convention
处理将名称分配给 Index
使用创建的对象 Column.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哈希,每次都将生成相同的值,以为给定架构生成一致的名称。
为命名约定创建自定义令牌
也可以通过在命名约定字典中指定附加令牌和可调用的来添加新令牌。例如,如果我们想使用一个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
1.3.0 新版功能: 添加了多列命名标记,如 %(column_0_N_name)s
. 如果生成的名称超出了目标数据库的字符限制,则将被确定地截断。
命名检查约束
这个 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
令牌;我们可以通过确保使用 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)
)
通过扫描列对象的给定表达式来确定“列零”的名称。如果表达式有多个列存在,扫描将使用确定性搜索,但是表达式的结构将确定哪个列被标记为“列零”。
为布尔、枚举和其他架构类型配置命名
这个 SchemaType
类引用类型对象,例如 Boolean
和 Enum
它会生成一个伴随类型的检查约束。这里的约束名称最直接的设置方式是发送“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
不带名称的类型,即使检查约束有命名约定。只有当我们对一个没有本机布尔类型(如sqlite或mysql)的数据库运行时,才会对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))
)
在 1.0 版更改: 不包括的约束命名约定 %(constraint_name)s
再次工作 SchemaType
约束条件。
索引
可以匿名创建索引(使用自动生成的名称 ix_<column label>
)对于使用inline的单个列 index
关键字在 Column
,它还修改了 unique
将唯一性应用于索引本身,而不是添加单独的唯一约束。对于具有特定名称或包含多个列的索引,请使用 Index
构造,需要名称。
下面我们举例说明 Table
用几个 Index
关联的对象。“create index”的DDL在表的create语句之后立即发出:
metadata_obj = MetaData()
mytable = Table('mytable', metadata_obj,
# 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)
注意在上面的例子中, Index
构造是在它对应的表外部创建的,使用 Column
直接对象。 Index
还支持在 Table
,使用字符串名称标识列::
metadata_obj = MetaData()
mytable = Table('mytable', metadata_obj,
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)
)
这个 Index
对象还支持其自身 create()
方法:
i = Index('someindex', mytable.c.col5)
sqli.create(engine)
功能指标
Index
支持SQL和函数表达式,目标后端支持。要使用降序值针对列创建索引,请 ColumnElement.desc()
可使用修饰符:
from sqlalchemy import Index
Index('someindex', mytable.c.somecol.desc())
或者,如果后端支持PostgreSQL等功能索引,则可以使用 lower()
功能:
from sqlalchemy import func, Index
Index('someindex', func.lower(mytable.c.somecol))