sqlAlchemy: Could not determine join condition between parent/child tables

Question

I've been trying to solve this for hours now, I've read through a few stack overflows, the official SQL alchemy documentation and watched youtube tutorials and it's all the same code as mine.

I have 3 tables - Users and Roles, and user_role which joins them.

I keep getting the following error:

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship 
Users.role - there are no foreign keys linking these tables via secondary table 'user_role'.  Ensure that referencing 
columns are associated with a ForeignKey or ForeignKeyConstraint, or specify 'primaryjoin' and 'secondaryjoin' 
expressions.

My code:

engine = create_engine('***',
connect_args={'options': '-csearch_path={}'.format('flaskProject')})

Base = declarative_base(engine)
Base.query = db.session.query_property()

user_role = Table('user_role', Base.metadata,
Column('userId', Integer, ForeignKey("Users.id")),
Column('roleId', Integer, ForeignKey("Roles.id")))


class Users(UserMixin, Base):
__tablename__ = 'users'
__table_args__ = {'schema': 'flaskProject'}

id = Column(Integer, primary_key=True)
username = Column(String)
email = Column(String)
password_hash = Column(String)

role = relationship("Roles", secondary = user_role)



class Roles(Base):

__table_args__ = {'schema': 'flaskProject'}
__tablename__ = 'roles'


id = Column(Integer, primary_key=True)
name = Column(String)

Your users and roles tables live in the flaskProject schema but your user_role table does not. You might want to add schema="flaskProject" to your Table() call and define your foreign key declarations like Column("userId", Integer, ForeignKey("flaskProject.users.id")), — Gord Thompson
@GordThompson I just tried your idea, but I still get the same error user_role = Table('user_role', Base.metadata, Column('userId', Integer, ForeignKey("flaskProject.Users.id")), Column('roleId', Integer, ForeignKey("flaskProject.Roles.id")) ,schema='flaskProject') — igrilkul

igrilkul igrilkul · Accepted Answer · 2021-02-24T13:30:02

I found out the issues with my secondary table - I was missing the schema in the table definition, had to add the schema to the foreign keys, and column names are case-sensitive

user_role = Table('user_role', Base.metadata,
Column('userid', Integer, ForeignKey('flaskProject.users.id')),
Column('roleid', Integer, ForeignKey('flaskProject.roles.id'))
,schema='flaskProject')

sqlAlchemy: Could not determine join condition between parent/child tables

1 Answers