Defining Foreign Keys
SQLの 外部キー はテーブル・レベルの構成体であり、そのテーブルの1つ以上の列を制約して、別の列セットに存在する値のみを許可します。通常は別のテーブルにあるとは限りません。制約される列を 外部キー 列と呼び、制約される列を 参照される 列と呼びます。参照される列は、ほとんどの場合、所有するテーブルの主キーを定義しますが、これには例外があります。外部キーは、互いに関係を持つ行のペアを接続する”ジョイント”であり、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 への引数は、最も一般的には <tablename>.<columnname> という形式の文字列か、リモートスキーマ内のテーブルか <schemaname>.<tablename>.<columnname> という形式の”owner”に対するものです。実際の Column オブジェクトでもかまいません。これは、後で説明するように、既存の Table オブジェクトの c コレクションからアクセスされます:
ForeignKey(user.c.user_id)
文字列を使用する利点は、Python内での user と user_preference の間のリンクが最初に必要になったときにのみ解決されることです。これにより、テーブルオブジェクトを複数のモジュールに簡単に展開し、任意の順序で定義することができます。
外部キーは、 ForeignKeyConstraint オブジェクトを使って、テーブルレベルで定義することもできます。このオブジェクトは、単一カラムまたは複数カラムの外部キーを記述することができます。複数カラムの外部キーは composite 外部キーと呼ばれ、ほとんどの場合、複合主キーを持つテーブルを参照します。以下では、複合主キーを持つテーブル 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"]
),
)Creating/Dropping Foreign Key Constraints via 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を除くすべての場合)、テーブルのセットが相互に依存する”依存サイクル”に2つ以上の外部キー制約が含まれている場合には機能しません。したがって、これらのメソッドは、ほとんどの形式の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() を呼び出すと、これら2つのテーブル間のサイクルが解決され、制約が別々に作成されます。
>>> 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文が表示され、他の制約に対しては表示されません。
>>> 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
ON UPDATE and ON DELETE
ほとんどのデータベースは外部キー値の*カスケード*をサポートしています。つまり、親の行が更新されると新しい値が子の行に置かれ、親の行が削除されると、対応するすべての子の行がnullに設定されるか削除されます。データ定義言語では、これらは外部キー制約に対応する”ON UPDATECASCADE”、”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 テーブルが必要であることに注意してください。また、他のデータベースでもサポートされていない可能性があります。
Configuring Constraint Naming Conventions
リレーショナル・データベースは通常、すべての制約とインデックスに明示的な名前を割り当てます。CHECK、UNIQUE、PRIMARY KEY制約などの制約がテーブル定義とインラインで生成される CREATE TABLE を使用してテーブルが作成される一般的なケースでは、データベースは通常、名前が他に指定されていない場合に、これらの制約に自動的に名前が割り当てられるシステムを備えています。既存のデータベース・テーブルが ALTER TABLE などのコマンドを使用してデータベース内で変更される場合、このコマンドは通常、新しい制約に明示的な名前を指定するとともに、削除または変更される既存の制約の名前を指定できる必要があります。
制約には Constraint.name パラメータを使って明示的に名前を付けることができ、インデックスには Index.name パラメータを使うことができます。ただし、制約の場合、このパラメータはオプションです。また、 Column.unique および Column.index パラメータを使って、明示的な名前を指定せずに UniqueConstraint および Index オブジェクトを作成するユースケースもあります。
既存のテーブルや制約を変更するユースケースは、 Alembic などのスキーマ移行ツールで処理できます。しかし、AlembicもSQLAlchemyも、名前が指定されていない制約オブジェクトに対して名前を作成することはできません。そのため、既存の制約を変更できるということは、名前を自動的に割り当てるためにリレーショナルデータベースで使用される命名システムをリバースエンジニアリングする必要があることや、すべての制約に名前が付けられるように注意する必要があることを意味します。
すべての Constraint および Index オブジェクトに明示的な名前を割り当てる必要があるのとは対照的に、自動化された命名スキームはイベントを使用して構築することができます。このアプローチには、コード全体で明示的な名前パラメータを必要とせずに、制約が一貫した命名スキームを取得するという利点があります。また、 Column.index パラメータによって生成された制約とインデックスに対しても同様に規則が行われるという利点もあります。SQLAlchemy 0.9.2では、このイベントベースのアプローチが含まれており、引数 MetaData.naming_convention を使用して設定できます。
The Default Naming Convention
MetaData.naming_convention のデフォルト値は、 Column.index パラメータを使用して作成された Index オブジェクトに名前を割り当てるという長年のSQLAlchemyの動作を処理します:
>>> from sqlalchemy.sql.schema import DEFAULT_NAMING_CONVENTION
>>> DEFAULT_NAMING_CONVENTION
immutabledict({'ix': 'ix_%(column_0_label)s'})Truncation of Long Names
生成された名前、特に複数列トークンを使用する名前が、対象データベースの識別子の長さ制限に対して長すぎる場合(例えば、PostgreSQLでは63文字に制限されています)、名前は次のようになります。
長い名前のmd 5ハッシュに基づく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ハッシュに基づいており、与えられたスキーマに対して一貫した名前を生成するために毎回同じ値を生成します。
Creating Custom Tokens for Naming Conventions
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
New in version 1.3.0では、: %(column_0_N_name)s のような複数列の名前付けトークンが追加されました。ターゲットデータベースの文字数制限を超えて生成された名前は、確定的に切り捨てられます。
Naming CHECK Constraints
CheckConstraint オブジェクトは、任意のSQL式に対して設定されます。任意の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)
)“column zero”の名前の決定は、列オブジェクトに対して指定された式をスキャンすることによって実行されます。式に複数の列が存在する場合、スキャンでは決定論的検索が使用されますが、式の構造によってどの列が”column zero”として示されるかが決定されます。
Configuring Naming for Boolean, Enum, and other schema types
SchemaType クラスは、型に付随するCHECK制約を生成する 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 のCHECK制約は必要ありません。名前なしで Boolean 型を設定しても安全です。たとえ名前付け規則がCHECK制約用に用意されていたとしてもです。この規則は、SQLiteやMySQLのようなネイティブブーリアン型を持たないデータベースに対して実行した場合にのみ、CHECK制約に対して参照されます。
CHECK制約はまた、 column_0_name トークンを利用することもできます。これは SchemaType とうまく動作します。なぜなら、これらの制約は1つの列しか持たないからです:
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 Declarative Mixins で命名規則機能を使用する場合、個々の制約オブジェクトはテーブルにマップされた実際のサブクラスごとに存在しなければなりません。背景と例については Creating Indexes and Constraints with Naming Conventions on Mixins を参照してください。
Indexes
インデックスは、 Column のインラインの index キーワードを使って、1つの列に対して匿名で(自動生成された名前 ix_<columnlabel> )作成することができます。これはまた、別のUNIQUE制約を追加する代わりに、インデックス自体に一意性を適用するために unique の使用法を変更します。特定の名前を持つインデックスや複数の列を含むインデックスには、名前を必要とする Index 構文を使用します。
以下にいくつかの Index オブジェクトが関連付けられた Table を示します。”CREATEINDEX”の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)
mytable.create(engine)
CREATE TABLE mytable (
col1 INTEGER,
col2 INTEGER,
col3 INTEGER,
col4 INTEGER,
col5 INTEGER,
col6 INTEGER
)
CREATE INDEX ix_mytable_col1 ON mytable (col1)
CREATE UNIQUE INDEX ix_mytable_col2 ON mytable (col2)
CREATE UNIQUE INDEX myindex ON mytable (col5, col6)
CREATE INDEX idx_col34 ON mytable (col3, col4)
上記の例では、 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),
)i = Index("someindex", mytable.c.col5)
i.create(engine)
CREATE INDEX someindex ON mytable (col5)
Functional Indexes
Index は、ターゲットバックエンドでサポートされているように、SQLと関数式をサポートしています。降順の値を使用して列に対するインデックスを作成するには、 ColumnElement.desc() 修飾子を使用できます:
from sqlalchemy import Index
Index("someindex", mytable.c.somecol.desc())あるいは、PostgreSQLのような関数型インデックスをサポートするバックエンドでは、”case insensitive”インデックスを lower() 関数を使って作成することができます:
from sqlalchemy import func, Index
Index("someindex", func.lower(mytable.c.somecol))