your revisions table has a composite unique constraint - one constraint with
two columns in it. therefore to refer to this constraint via foreign key, you
need a single composite foreignkeyconstraint - again, one constraint that links
two columns together. you would use ForeignKeyConstraint for this, not
ForeignKey, see ForeignKeyConstraint in
https://docs.sqlalchemy.org/en/20/core/constraints.html#defining-foreign-keys .
On Wed, Sep 13, 2023, at 1:43 PM, Lord Wolfenstein wrote:
> 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
> 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:[email protected]:5432/mydatabase"
> DATABASE =
> "postgresql+psycopg2://user:[email protected]: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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/ed00203b-d981-4f4f-9c7c-5f6c14660e74n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/ed00203b-d981-4f4f-9c7c-5f6c14660e74n%40googlegroups.com?utm_medium=email&utm_source=footer>.
>
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/2441309b-507a-4245-976e-f0e0056487b7%40app.fastmail.com.
