I have a database that looks like this that I create with Alembic. The relationships are trivial except between Revision and ObjectCount, there it used two foreign keys [image: aaa.png]
The code looks like this ############################ from datetime import datetime from typing import Optional from sqlalchemy import ForeignKey, create_engine, UniqueConstraint from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, sessionmaker # type: ignore class Base(DeclarativeBase): pass class Blueprint(Base): __tablename__ = "blueprints" blueprint_id: Mapped[int] = mapped_column(primary_key=True) filename: Mapped[str] created: Mapped[datetime] = mapped_column(default=datetime.utcnow) revisions: Mapped[list["Revision"]] = relationship(back_populates="blueprint") # type: ignore def get_new_revision_number(self) -> int: if not self.revisions: return 1 return max(revision.revision_number for revision in self.revisions) + 1 def __str__(self): return f"{self.filename} : {self.blueprint_id}" class Revision(Base): __tablename__ = "revisions" __table_args__ = ( UniqueConstraint("blueprint_id", "revision_number", name="revision_blueprint_revision_number"), ) blueprint_id: Mapped[int] = mapped_column(ForeignKey("blueprints.blueprint_id"), primary_key=True) revision_number: Mapped[int] = mapped_column(primary_key=True) date: Mapped[datetime] = mapped_column(default=datetime.utcnow) savedata: Mapped[str] blueprint: Mapped["Blueprint"] = relationship(back_populates="revisions") # type: ignore object_count: Mapped[Optional[list["ObjectCount"]]] = relationship( back_populates="revision", primaryjoin="and_(Revision.blueprint_id==ObjectCount.blueprint_id, Revision.revision_number==ObjectCount.revision_number)", ) # type: ignore def __str__(self): return f"{self.blueprint.filename} : {self.blueprint_id} : {self.revision_number}" class Object(Base): __tablename__ = "objects" object_id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] def __str__(self): return self.name # https://docs.sqlalchemy.org/en/20/orm/join_conditions.html # https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html # https://docs.sqlalchemy.org/en/20/core/constraints.html#sqlalchemy.schema.UniqueConstraint class ObjectCount(Base): __tablename__ = "object_count" __table_args__ = ( UniqueConstraint("blueprint_id", "revision_number", "object_id", name="o_c_unique"), ) blueprint_id: Mapped[int] = mapped_column(ForeignKey("revisions.blueprint_id"), primary_key=True) revision_number: Mapped[int] = mapped_column(ForeignKey("revisions.revision_number"), primary_key=True) object_id: Mapped[int] = mapped_column(ForeignKey("objects.object_id"), primary_key=True) count: Mapped[int] object: Mapped["Object"] = relationship() # type: ignore revision: Mapped["Revision"] = relationship( back_populates="object_count", primaryjoin="and_(Revision.blueprint_id==ObjectCount.blueprint_id, Revision.revision_number==ObjectCount.revision_number)", ) # type: ignore def __str__(self): return f"{self.revision.blueprint.filename} {self.revision_number} {self.object.name} {self.count}" DATABASE = "postgresql+psycopg://user:password@192.168.10.111:5432/mydatabase" DATABASE = "postgresql+psycopg2://user:password@192.168.10.111:5432/mydatabase" #DATABASE = "sqlite:///database.db" engine = create_engine(DATABASE) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) ############################ I can create migrations with Alembic no problem. And I can migrate when I use SQLite. But when I try to migrate with PostgreSQL I get this error: sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "revisions" [SQL: CREATE TABLE object_count ( blueprint_id INTEGER NOT NULL, revision_number INTEGER NOT NULL, object_id INTEGER NOT NULL, count INTEGER NOT NULL, PRIMARY KEY (blueprint_id, revision_number, object_id), FOREIGN KEY(blueprint_id) REFERENCES revisions (blueprint_id), FOREIGN KEY(object_id) REFERENCES objects (object_id), FOREIGN KEY(revision_number) REFERENCES revisions (revision_number), CONSTRAINT o_c_unique UNIQUE (blueprint_id, revision_number, object_id) ) ] I think I clearly see a unique constraint in the code. I add the UniqueConstraint in __table_args__ but PostgreSQL still hits the wall then I migrate. I get the same error with both psycopg2==2.9.6 and psycopg==3.1.10. What frustrates me is that it works and all tests pass when I use SQLite. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ed00203b-d981-4f4f-9c7c-5f6c14660e74n%40googlegroups.com.