I'm setting up some table objects for SQLAlchemy. I have a user and checkout tables. I want to associate a user object with the checkin and the checkout, which are both recorded in the same checkout object, so I have an in_user and out_user associated with each checkout object.
I've run into a sqlalchemy.exc.AmbiguousForeignKeysError
To quote the exact error message:
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Checkout.out_auth_user - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table. I've done as the error message requests (see below), but the error still occurs.
I originally only specified user email because I wanted to be able to remove users in the future without corrupting historical data. However, I tried to add user id, but still got the same error.
There are many similar questions on StackOverflow, but I couldn't find one that addressed my problem and most of them are working with much older versions of sqlalchemy that did not support the foreign_keys argument to relationship. It seems like this often occurs with backreferences, but I'm not using those as far as I'm aware. This is a simple one-way link from a checkout object to two user objects.
Flask foreign_keys still shows AmbiguousForeignKeysError
sqlalchemy , AmbiguousForeignKeysError
The full code is on github at https://github.com/ACMWM/hwcheckout
Below is an MRE
from sqlalchemy import create_engine from sqlalchemy.orm import scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Boolean, Integer, String, ForeignKey, DateTime from sqlalchemy.orm import relationship db = "sqlite:///mre.db" engine = create_engine(db, convert_unicode=True) db_session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine)) Base = declarative_base() Base.query = db_session.query_property() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String) class HW(Base): __tablename__ = "HW" id = Column(Integer, primary_key=True) class Checkout(Base): __tablename__ = "Checkouts" what = Column(Integer, ForeignKey(HW.id)) hardware = relationship(HW, foreign_keys=[what]) id = Column(Integer, primary_key=True) out_auth_id = Column(Integer, ForeignKey(User.id)) out_auth_email = Column(String, ForeignKey(User.email)) out_auth_user = relationship(User, foreign_keys=[out_auth_id, out_auth_email]) in_auth_id = Column(Integer, ForeignKey(User.id)) in_auth_email = Column(String, ForeignKey(User.email)) in_auth_user = relationship(User, foreign_keys=[in_auth_id, in_auth_email]) Base.metadata.create_all(bind=engine, checkfirst=True) u = User(email="[email protected]") chk = Checkout(out_auth_user_id=u.id,out_auth_user_email=u.email) I'm using SQLAlchemy 1.3.3
EDIT: Remove double import of models. Same error still occurs
EDIT again: Got the MRE to reproduce the error
Postgres EDIT: Don't know if this helps, but when I tried to move my code to a real database, I got this error:
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "users" [SQL: CREATE TABLE "Checkouts" ( id SERIAL NOT NULL, outdate TIMESTAMP WITHOUT TIME ZONE, returndate TIMESTAMP WITHOUT TIME ZONE, who VARCHAR, reason VARCHAR, quantity INTEGER, what INTEGER, out_auth_id INTEGER, out_auth_email VARCHAR, in_auth_id INTEGER, in_auth_email VARCHAR, PRIMARY KEY (id), UNIQUE (id), FOREIGN KEY(what) REFERENCES "HW" (id), FOREIGN KEY(out_auth_id) REFERENCES users (id), FOREIGN KEY(out_auth_email) REFERENCES users (email), FOREIGN KEY(in_auth_id) REFERENCES users (id), FOREIGN KEY(in_auth_email) REFERENCES users (email) ) ] 
