Definition von Fremdschlüsseln
Ein Fremdschlüssel in SQL ist ein Tabellen-Level-Konstrukt, das eine oder mehrere Spalten in dieser Tabelle einschränkt, um nur Werte zuzulassen, die in einer anderen Menge von Spalten vorhanden sind, typischerweise, aber nicht immer, auf einer anderen Tabelle. Wir nennen die eingeschränkten Spalten die Fremdschlüssel-Spalten und die Spalten, auf die sie eingeschränkt sind, die referenzierten Spalten. Die referenzierten Spalten definieren fast immer den Primärschlüssel für ihre besitzende Tabelle, obwohl es Ausnahmen gibt. Der Fremdschlüssel ist die „Verbindung“, die Paare von Zeilen verbindet, die eine Beziehung zueinander haben, und SQLAlchemy misst diesem Konzept in praktisch jedem Bereich seiner Operationen eine sehr tiefe Bedeutung bei.
In SQLAlchemy sowie in DDL können Fremdschlüssel-Constraints als zusätzliche Attribute innerhalb der Tabellenklausel definiert werden, oder für einspaltige Fremdschlüssel können sie optional innerhalb der Definition einer einzelnen Spalte angegeben werden. Der einspaltige Fremdschlüssel ist häufiger und wird auf Spaltenebene durch die Konstruktion eines ForeignKey-Objekts als Argument zu einem Column-Objekt spezifiziert.
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)),
)Oben definieren wir eine neue Tabelle user_preference, für die jede Zeile einen Wert in der Spalte user_id enthalten muss, der auch in der Spalte user_id der Tabelle user vorhanden ist.
Das Argument für ForeignKey ist am häufigsten ein String der Form <tablename>.<columnname>, oder für eine Tabelle in einem entfernten Schema oder „Owner“ die Form <schemaname>.<tablename>.<columnname>. Es kann auch ein tatsächliches Column-Objekt sein, das, wie wir später sehen werden, aus einem vorhandenen Table-Objekt über seine c-Sammlung abgerufen wird.
ForeignKey(user.c.user_id)
Der Vorteil der Verwendung eines Strings besteht darin, dass die Verknüpfung in Python zwischen user und user_preference erst bei Bedarf aufgelöst wird, sodass Tabellenobjekte leicht über mehrere Module verteilt und in beliebiger Reihenfolge definiert werden können.
Fremdschlüssel können auch auf Tabellenebene über das Objekt ForeignKeyConstraint definiert werden. Dieses Objekt kann einen ein- oder mehrspaltigen Fremdschlüssel beschreiben. Ein mehrspaltiger Fremdschlüssel wird als zusammengesetzter Fremdschlüssel bezeichnet und referenziert fast immer eine Tabelle, die einen zusammengesetzten Primärschlüssel hat. Unten definieren wir eine Tabelle invoice, die einen zusammengesetzten Primärschlüssel hat.
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),
)Und dann eine Tabelle invoice_item mit einem zusammengesetzten Fremdschlüssel, der auf invoice verweist.
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"]
),
)Es ist wichtig zu beachten, dass die ForeignKeyConstraint die einzige Möglichkeit ist, einen zusammengesetzten Fremdschlüssel zu definieren. Obwohl wir auch einzelne ForeignKey-Objekte auf beiden Spalten invoice_item.invoice_id und invoice_item.ref_num hätten platzieren können, wäre SQLAlchemy nicht bewusst, dass diese beiden Werte zusammengehören sollten – es wären zwei einzelne Fremdschlüssel-Constraints statt eines einzelnen zusammengesetzten Fremdschlüssels, der auf zwei Spalten verweist.
Erstellen/Löschen von Fremdschlüssel-Constraints per ALTER
Das Verhalten, das wir in Tutorials und anderswo bei Fremdschlüsseln mit DDL gesehen haben, zeigt, dass die Constraints typischerweise „inline“ innerhalb der CREATE TABLE-Anweisung gerendert werden, wie z. B.
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)
)Die Direktive CONSTRAINT .. FOREIGN KEY wird verwendet, um den Constraint „inline“ innerhalb der CREATE TABLE-Definition zu erstellen. Die Methoden MetaData.create_all() und MetaData.drop_all() tun dies standardmäßig, indem sie eine topologische Sortierung aller beteiligten Table-Objekte verwenden, sodass Tabellen in der Reihenfolge ihrer Fremdschlüsselabhängigkeit erstellt und gelöscht werden (diese Sortierung ist auch über den MetaData.sorted_tables-Accessor verfügbar).
Dieser Ansatz funktioniert nicht, wenn zwei oder mehr Fremdschlüssel-Constraints in einem „Abhängigkeitszyklus“ beteiligt sind, d. h. eine Menge von Tabellen gegenseitig voneinander abhängig ist, vorausgesetzt, das Backend erzwingt Fremdschlüssel (immer der Fall außer bei SQLite, MySQL/MyISAM). Die Methoden werden daher Constraints in einem solchen Zyklus in separate ALTER-Anweisungen aufbrechen, auf allen Backends außer SQLite, das die meisten Formen von ALTER nicht unterstützt. Gegeben ein Schema wie
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"
),
)Wenn wir MetaData.create_all() auf einem Backend wie dem PostgreSQL-Backend aufrufen, wird der Zyklus zwischen diesen beiden Tabellen aufgelöst und die Constraints werden separat erstellt.
>>> 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)
Um diese Tabellen zu löschen, gilt die gleiche Logik. Beachten Sie jedoch, dass in SQL das Löschen von Constraints erfordert, dass der Constraint einen Namen hat. Im Fall der 'node'-Tabelle oben haben wir diesen Constraint nicht benannt; das System wird daher versuchen, nur die benannten Constraints zu löschen.
>>> 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
Wenn der Zyklus nicht aufgelöst werden kann, z. B. wenn wir hier keinerlei Namen für einen der Constraints vergeben hätten, erhalten wir die folgende Fehlermeldung.
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.
Dieser Fehler gilt nur für den DROP-Fall, da wir „ADD CONSTRAINT“ im CREATE-Fall ohne Namen ausgeben können; die Datenbank weist normalerweise automatisch einen zu.
Die Schlüsselwörter ForeignKeyConstraint.use_alter und ForeignKey.use_alter können verwendet werden, um Abhängigkeitszyklen manuell aufzulösen. Wir können dieses Flag nur für die Tabelle 'element' wie folgt hinzufügen.
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",
),
)In unserem CREATE DDL sehen wir die ALTER-Anweisung nur für diesen Constraint und nicht für den anderen.
>>> 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 und ForeignKey.use_alter erfordern bei Verwendung in Verbindung mit einer Löschoperation, dass der Constraint benannt ist, andernfalls wird ein Fehler wie der folgende generiert.
sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint
ForeignKeyConstraint(...); it has no name
ON UPDATE und ON DELETE
Die meisten Datenbanken unterstützen das Kaskadieren von Fremdschlüsselwerten, d. h. wenn eine übergeordnete Zeile aktualisiert wird, wird der neue Wert in untergeordnete Zeilen geschrieben, oder wenn die übergeordnete Zeile gelöscht wird, werden alle entsprechenden untergeordneten Zeilen auf NULL gesetzt oder gelöscht. In der Data Definition Language werden diese mit Phrasen wie „ON UPDATE CASCADE“, „ON DELETE CASCADE“ und „ON DELETE SET NULL“ angegeben, die Fremdschlüssel-Constraints entsprechen. Die Phrase nach „ON UPDATE“ oder „ON DELETE“ kann auch andere Phrasen zulassen, die für die verwendete Datenbank spezifisch sind. Die Objekte ForeignKey und ForeignKeyConstraint unterstützen die Generierung dieser Klausel über die Schlüsselwortargumente onupdate und ondelete. Der Wert ist ein beliebiger String, der nach der entsprechenden Phrase „ON UPDATE“ oder „ON DELETE“ ausgegeben wird.
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",
),
)Beachten Sie, dass diese Klauseln InnoDB-Tabellen erfordern, wenn sie mit MySQL verwendet werden. Sie werden möglicherweise auch auf anderen Datenbanken nicht unterstützt.
Konfigurieren von Constraint-Namenskonventionen
Relationale Datenbanken weisen allen Constraints und Indizes typischerweise explizite Namen zu. In dem gängigen Fall, dass eine Tabelle mit CREATE TABLE erstellt wird, bei der Constraints wie CHECK, UNIQUE und PRIMARY KEY Constraints inline mit der Tabellendefinition erzeugt werden, verfügt die Datenbank normalerweise über ein System, bei dem Namen automatisch diesen Constraints zugewiesen werden, wenn kein Name anderweitig angegeben ist. Wenn eine vorhandene Datenbanktabelle in einer Datenbank mit einem Befehl wie ALTER TABLE geändert wird, muss dieser Befehl typischerweise explizite Namen für neue Constraints angeben und auch in der Lage sein, den Namen eines vorhandenen Constraints anzugeben, der gelöscht oder geändert werden soll.
Constraints können explizit über den Parameter Constraint.name und für Indizes über den Parameter Index.name benannt werden. Im Fall von Constraints ist dieser Parameter jedoch optional. Es gibt auch die Anwendungsfälle, die Parameter Column.unique und Column.index zu verwenden, die UniqueConstraint- und Index-Objekte ohne explizite Namensangabe erstellen.
Der Anwendungsfall der Änderung vorhandener Tabellen und Constraints kann durch Schema-Migrationswerkzeuge wie Alembic gehandhabt werden. Weder Alembic noch SQLAlchemy erstellen jedoch derzeit Namen für Constraint-Objekte, bei denen der Name anderweitig nicht angegeben ist, was dazu führt, dass die Änderung vorhandener Constraints erfordert, dass man das Benennungssystem der relationalen Datenbank zur automatischen Namensvergabe umkehren muss oder dass Sorgfalt darauf gelegt werden muss, dass alle Constraints benannt sind.
Im Gegensatz zur expliziten Benennung aller Constraint- und Index-Objekte können automatisierte Benennungsschemata mithilfe von Events konstruiert werden. Dieser Ansatz hat den Vorteil, dass Constraints ein konsistentes Benennungsschema erhalten, ohne dass explizite Namensparameter im gesamten Code erforderlich sind, und dass die Konvention genauso gut für die durch die Parameter Column.unique und Column.index erzeugten Constraints und Indizes gilt. Seit SQLAlchemy 0.9.2 ist dieser ereignisbasierte Ansatz enthalten und kann mit dem Argument MetaData.naming_convention konfiguriert werden.
Konfigurieren einer Namenskonvention für eine MetaData-Sammlung
MetaData.naming_convention bezieht sich auf ein Wörterbuch, das die Klasse Index oder einzelne Klassen Constraint als Schlüssel und Python-String-Vorlagen als Werte akzeptiert. Es akzeptiert auch eine Reihe von String-Codes als alternative Schlüssel: "fk", "pk", "ix", "ck" und "uq" für Fremdschlüssel, Primärschlüssel, Index, Check und Unique Constraints. Die String-Vorlagen in diesem Wörterbuch werden verwendet, wann immer ein Constraint oder Index mit diesem MetaData-Objekt verknüpft wird, das keinen bestehenden Namen hat (einschließlich eines Ausnahmefalls, in dem ein bestehender Name weiter verschönert werden kann).
Ein Beispiel für eine Namenskonvention, die grundlegende Fälle abdeckt, ist wie folgt:
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)Die obige Konvention legt Namen für alle Constraints innerhalb der Ziel- MetaData-Sammlung fest. Wir können beispielsweise den Namen beobachten, der erzeugt wird, wenn wir einen unbenannten UniqueConstraint erstellen.
>>> 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'Dieselbe Funktion greift, auch wenn wir nur das Flag Column.unique verwenden.
>>> 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'Ein wesentlicher Vorteil des Namenskonventionsansatzes ist, dass die Namen zur Python-Konstruktionszeit und nicht zur DDL-Emissionszeit festgelegt werden. Dies hat zur Folge, dass bei Verwendung der --autogenerate-Funktion von Alembic die Namenskonvention bei der Generierung eines neuen Migrationsskripts explizit wird.
def upgrade():
op.create_unique_constraint("uq_user_name", "user", ["name"])Der obige String "uq_user_name" wurde aus dem UniqueConstraint-Objekt kopiert, das --autogenerate in unseren Metadaten gefunden hat.
Die verfügbaren Tokens umfassen %(table_name)s, %(referred_table_name)s, %(column_0_name)s, %(column_0_label)s, %(column_0_key)s, %(referred_column_0_name)s und %(constraint_name)s, sowie Mehrfachspaltenversionen davon, einschließlich %(column_0N_name)s, %(column_0_N_name)s, %(referred_column_0_N_name)s, die alle Spaltennamen mit oder ohne Unterstrich getrennt rendern. Die Dokumentation für MetaData.naming_convention enthält weitere Details zu jedem dieser Konventionen.
Die Standard-Namenskonvention
Der Standardwert für MetaData.naming_convention behandelt das langjährige SQLAlchemy-Verhalten, einem Index-Objekt, das mit dem Parameter Column.index erstellt wird, einen Namen zuzuweisen.
>>> from sqlalchemy.sql.schema import DEFAULT_NAMING_CONVENTION
>>> DEFAULT_NAMING_CONVENTION
immutabledict({'ix': 'ix_%(column_0_label)s'})Abschneiden langer Namen
Wenn ein generierter Name, insbesondere jene, die Mehrfachspalten-Tokens verwenden, zu lang für die Kennungslängenbeschränkung der Ziel-Datenbank ist (z. B. hat PostgreSQL eine Beschränkung von 63 Zeichen), wird der Name deterministisch mit einem 4-Zeichen-Suffix, das auf einem MD5-Hash des langen Namens basiert, abgeschnitten. Zum Beispiel wird die folgende Namenskonvention bei den verwendeten Spaltennamen sehr lange Namen generieren.
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"),
)Auf dem PostgreSQL-Dialekt werden Namen, die länger als 63 Zeichen sind, wie im folgenden Beispiel abgeschnitten.
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)
)Das obige Suffix a79e basiert auf dem MD5-Hash des langen Namens und generiert jedes Mal denselben Wert, um konsistente Namen für ein gegebenes Schema zu erzeugen.
Erstellen benutzerdefinierter Tokens für Namenskonventionen
Neue Tokens können auch hinzugefügt werden, indem ein zusätzliches Token und eine aufrufbare Funktion im `naming_convention`-Wörterbuch angegeben werden. Wenn wir beispielsweise unsere Fremdschlüssel-Constraints mit einem GUID-Schema benennen möchten, könnten wir dies wie folgt tun.
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",
}Oben, wenn wir eine neue ForeignKeyConstraint erstellen, erhalten wir einen Namen wie folgt:
>>> 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
Neu in Version 1.3.0: Mehrspalten-Namens-Tokens wie %(column_0_N_name)s hinzugefügt. Generierte Namen, die das Zeichenlimit für die Ziel-Datenbank überschreiten, werden deterministisch abgeschnitten.
Benennung von CHECK Constraints
Das CheckConstraint-Objekt wird gegen einen beliebigen SQL-Ausdruck konfiguriert, der beliebig viele Spalten enthalten kann, und wird zusätzlich oft mit einer rohen SQL-Zeichenkette konfiguriert. Daher ist eine gängige Konvention für die Verwendung mit CheckConstraint eine, bei der wir erwarten, dass das Objekt bereits einen Namen hat, und wir erweitern es dann mit anderen Konventionselementen. Eine typische Konvention ist "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"),
)Die obige Tabelle erzeugt den Namen ck_foo_value_gt_5
CREATE TABLE foo (
value INTEGER,
CONSTRAINT ck_foo_value_gt_5 CHECK (value > 5)
)CheckConstraint unterstützt auch das Token %(columns_0_name)s; wir können dies nutzen, indem wir sicherstellen, dass wir ein Column- oder column()-Element innerhalb des Ausdrucks der Einschränkung verwenden, entweder indem wir die Einschränkung separat von der Tabelle deklarieren
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)oder indem wir ein column() inline verwenden
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)
)Beides erzeugt den Namen ck_foo_value
CREATE TABLE foo (
value INTEGER,
CONSTRAINT ck_foo_value CHECK (value > 5)
)Die Bestimmung des Namens der "Spalte Null" erfolgt durch Scannen des gegebenen Ausdrucks nach Spaltenobjekten. Wenn der Ausdruck mehr als eine Spalte enthält, verwendet der Scan eine deterministische Suche, jedoch bestimmt die Struktur des Ausdrucks, welche Spalte als "Spalte Null" vermerkt wird.
Konfiguration der Benennung für Boolesche, Enum- und andere Schematypen
Die Klasse SchemaType bezieht sich auf Typobjekte wie Boolean und Enum, die eine CHECK-Einschränkung generieren, die mit dem Typ einhergeht. Der Name für die Einschränkung wird hier am direktesten durch Senden des Parameters "name" gesetzt, z. B. Boolean.name
Table("foo", metadata_obj, Column("flag", Boolean(name="ck_foo_flag")))Das Feature für die Benennungskonvention kann auch mit diesen Typen kombiniert werden, normalerweise durch die Verwendung einer Konvention, die %(constraint_name)s enthält, und dann durch die Zuweisung eines Namens zum Typ
metadata_obj = MetaData(
naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
)
Table("foo", metadata_obj, Column("flag", Boolean(name="flag_bool")))Die obige Tabelle erzeugt den Einschränkungsnamen ck_foo_flag_bool
CREATE TABLE foo (
flag BOOL,
CONSTRAINT ck_foo_flag_bool CHECK (flag IN (0, 1))
)Die SchemaType-Klassen verwenden spezielle interne Symbole, sodass die Benennungskonvention erst zur DDL-Kompilierungszeit bestimmt wird. Unter PostgreSQL gibt es einen nativen BOOLEAN-Typ, daher ist die CHECK-Einschränkung von Boolean nicht erforderlich; wir können einen Boolean-Typ ohne Namen einrichten, auch wenn eine Benennungskonvention für Check-Constraints vorhanden ist. Diese Konvention wird nur für die CHECK-Einschränkung konsultiert, wenn wir gegen eine Datenbank ohne nativen BOOLEAN-Typ wie SQLite oder MySQL laufen.
Die CHECK-Einschränkung kann auch das Token column_0_name nutzen, was gut mit SchemaType funktioniert, da diese Einschränkungen nur eine Spalte haben
metadata_obj = MetaData(naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"})
Table("foo", metadata_obj, Column("flag", Boolean()))Das obige Schema erzeugt
CREATE TABLE foo (
flag BOOL,
CONSTRAINT ck_foo_flag CHECK (flag IN (0, 1))
)Indizes
Indizes können anonym erstellt werden (mit einem automatisch generierten Namen ix_<column label>) für eine einzelne Spalte unter Verwendung des Inline-Schlüsselworts index auf Column, was auch die Verwendung von unique modifiziert, um die Einzigartigkeit auf den Index selbst anzuwenden, anstatt eine separate UNIQUE-Einschränkung hinzuzufügen. Für Indizes mit spezifischen Namen oder die mehr als eine Spalte umfassen, verwenden Sie das Konstrukt Index, das einen Namen erfordert.
Unten zeigen wir eine Table mit mehreren zugehörigen Index-Objekten. Die DDL für "CREATE INDEX" wird direkt nach den CREATE-Anweisungen für die Tabelle ausgegeben.
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)
Beachten Sie im obigen Beispiel, dass das Index-Konstrukt extern zu der Tabelle erstellt wird, auf die es sich bezieht, wobei direkt Column-Objekte verwendet werden. Index unterstützt auch die "Inline"-Definition innerhalb der Table, unter Verwendung von String-Namen zur Identifizierung von Spalten.
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),
)Das Index-Objekt unterstützt auch seine eigene create() Methode.
i = Index("someindex", mytable.c.col5)
i.create(engine)
CREATE INDEX someindex ON mytable (col5)
Funktionale Indizes
Index unterstützt SQL- und Funktionsausdrücke, wie sie vom Zielbackend unterstützt werden. Um einen Index auf eine Spalte mit absteigendem Wert zu erstellen, kann der Modifikator ColumnElement.desc() verwendet werden.
from sqlalchemy import Index
Index("someindex", mytable.c.somecol.desc())Oder mit einem Backend, das funktionale Indizes unterstützt, wie PostgreSQL, kann ein "case-insensitive" Index mit der lower() Funktion erstellt werden.
from sqlalchemy import func, Index
Index("someindex", func.lower(mytable.c.somecol))