I’m volunteering for a small local community’s project while learning Python at the same time. It’s first time with Flask and SQLAlchemy for me (MySQL is used to store the data). ORM I chose was working fine at first, but when I tried to switch to many-to-many model between two tables following this official guide, this error “NoReferencedTableError” started to appear during initialization.

Here is full error line:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'assoc_tbl_1_user_X_challenge.challengeId' could not find table 'challengesTbl' with which to generate a foreign key to target column 'id'

I can’t spot an error in my definitions no matter how hard I try. I suspect it may have something to do with the fact that association_table1 variable is defined before the classes which define other tables, and when it’s instantiated it can’t find them. But even if I move it to the end of the file, it still doesn’t work, now because it’s referenced from the other tables classes’ definitions. Declaring it first and initializing at the end of the file didn’t help either.

I also noted that when I try to run an external script that pre-initializes my database using the same ORM file, only three tables seem to be created:

mysql> show tables;
+------------------------------+
| Tables_in_nekoko$ABsitedb    |
+------------------------------+
| challengesTbl                |
| rideRecordingsTbl            |
| usersTbl                     |
+------------------------------+
3 rows in set (0.00 sec)

I don’t see association table assoc_tbl_1_user_X_challenge, shouldn’t it be created at the same time as well?

My current data model is this:

from . import db
from flask_login import UserMixin
from sqlalchemy import text

class Base(db.DeclarativeBase):
    pass

association_table1 = db.Table(
    "assoc_tbl_1_user_X_challenge",
    Base.metadata,
    db.Column("userId", db.ForeignKey("usersTbl.id"), primary_key=True),
    db.Column("challengeId", db.ForeignKey("challengesTbl.id"), primary_key=True),
)

class User(UserMixin, db.Model):
    __tablename__ = "usersTbl"
    id = db.Column(db.Integer, primary_key=True) # primary keys are required by SQLAlchemy
    email = db.Column(db.String(100), unique=True)
    password = db.Column(db.String(1000))
    givenName = db.Column(db.String(100))
    sn = db.Column(db.String(100))
    totalDistanceKm = db.Column(db.Float, server_default=text("0.0"))
    groups = db.Column(db.JSON, nullable=False)
    isActive = db.Column(db.Boolean, default=True, nullable=False, index=True)
    challenges = db.relationship('Challenge', secondary=association_table1, back_populates="participants")
    rides = db.relationship('Ride', backref='usersTbl', lazy=True)

    def __repr__(self):
        return f'<User "{self.email}">'

class Ride(db.Model):
    __tablename__ = "rideRecordingsTbl"
    id = db.Column(db.Integer, primary_key=True)
    rideRecording = db.Column(db.LargeBinary)
    #rideRecording = db.Column(db.LONGBLOB, nullable=False)
    rideName = db.Column(db.String(150))
    distanceKm = db.Column(db.Float, server_default=text("0.0"))
    duration = db.Column(db.Time)
    uploadDateTime = db.Column(db.DateTime)
    stream_hash = db.Column(db.String(1000))
    ownerId = db.Column(db.Integer, db.ForeignKey('usersTbl.id'), nullable=False)

    def __repr__(self):
        return f'<Ride "{self.rideName[:20]}...">'

class Challenge(db.Model):
    __tablename__ = "challengesTbl"
    id = db.Column(db.Integer, primary_key=True)
    rideRecording = db.Column(db.LargeBinary)
    challengeName = db.Column(db.String(150))
    challengeTargetDistanceKm = db.Column(db.Float, server_default=text("0.0"))
    challengeCurrnetTotalDistanceKm = db.Column(db.Float, server_default=text("0.0"))
    challengeEnds = db.Column(db.DateTime)
    challengeStarts = db.Column(db.DateTime)
    createdByUserId = db.Column(db.Integer)
    isActive = db.Column(db.Boolean, default=True, nullable=False, index=True)
    participants = db.relationship('User', secondary=association_table1, back_populates="challenges")

    def __repr__(self):
        return f'<Challenge "{self.challengeName[:20]}...">'