这里的问题是你必须声明ForeignKeyConstraint()
in __table_args__
,不在类的主体中。
换句话说,以下代码不会将外键约束应用于子表......
from sqlalchemy import create_engine, Column, Integer, text, ForeignKeyConstraint, String
from sqlalchemy.orm import declarative_base, relationship
connection_url = r"mssql+pyodbc://@.\SQLEXPRESS/myDb?driver=ODBC+Driver+17+for+SQL+Server"
engine = create_engine(connection_url)
Base = declarative_base()
class Parent(Base):
__tablename__ = "tbl_parent"
id1 = Column(Integer, primary_key=True)
id2 = Column(Integer, primary_key=True)
parent_name = Column(String(50))
children = relationship("Child", back_populates="parent")
class Child(Base):
__tablename__ = "tbl_child"
id = Column(Integer, primary_key=True, autoincrement=False)
child_name = Column(String(50))
parent_id1 = Column(Integer)
parent_id2 = Column(Integer)
# this does not work
ForeignKeyConstraint(
["parent_id1", "parent_id2"], ["tbl_parent.id1", "tbl_parent.id2"]
)
parent = relationship(
"Parent",
foreign_keys="[Child.parent_id1, Child.parent_id2]",
back_populates="children",
)
Base.metadata.drop_all(engine)
engine.echo = True
Base.metadata.create_all(engine)
"""DDL emitted:
CREATE TABLE tbl_parent (
id1 INTEGER NOT NULL,
id2 INTEGER NOT NULL,
parent_name VARCHAR(50),
PRIMARY KEY (id1, id2)
)
CREATE TABLE tbl_child (
id INTEGER NOT NULL,
child_name VARCHAR(50),
parent_id1 INTEGER,
parent_id2 INTEGER,
PRIMARY KEY (id)
)
"""
… 但是这个will work …
from sqlalchemy import create_engine, Column, Integer, text, ForeignKeyConstraint, String
from sqlalchemy.orm import declarative_base, relationship
connection_url = r"mssql+pyodbc://@.\SQLEXPRESS/myDb?driver=ODBC+Driver+17+for+SQL+Server"
engine = create_engine(connection_url)
Base = declarative_base()
class Parent(Base):
__tablename__ = "tbl_parent"
id1 = Column(Integer, primary_key=True)
id2 = Column(Integer, primary_key=True)
parent_name = Column(String(50))
children = relationship("Child", back_populates="parent")
class Child(Base):
__tablename__ = "tbl_child"
# this works
__table_args__ = (
ForeignKeyConstraint(
["parent_id1", "parent_id2"], ["tbl_parent.id1", "tbl_parent.id2"]
),
)
id = Column(Integer, primary_key=True, autoincrement=False)
child_name = Column(String(50))
parent_id1 = Column(Integer)
parent_id2 = Column(Integer)
parent = relationship(
"Parent",
foreign_keys="[Child.parent_id1, Child.parent_id2]",
back_populates="children",
)
Base.metadata.drop_all(engine)
engine.echo = True
Base.metadata.create_all(engine)
"""DDL emitted:
CREATE TABLE tbl_parent (
id1 INTEGER NOT NULL,
id2 INTEGER NOT NULL,
parent_name VARCHAR(50) NULL,
PRIMARY KEY (id1, id2)
)
CREATE TABLE tbl_child (
id INTEGER NOT NULL,
child_name VARCHAR(50) NULL,
parent_id1 INTEGER NULL,
parent_id2 INTEGER NULL,
PRIMARY KEY (id),
FOREIGN KEY(parent_id1, parent_id2) REFERENCES tbl_parent (id1, id2)
)
"""
参考:
https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html#orm-declarative-table-configuration