sqlalchemy.exc.NoReferencedTableError: Foreign key could not find table

Question

I have some models that i'd like to migrate, 2 of them are:

FamilyMember.py

class FamilyMember(db.Model):
    __tablename__ = 'family_members'
    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    name = db.Column(db.String(120), index=True)
    email = db.Column(db.String(120), index=True, unique=True)
    password = db.Column(db.String(128), nullable=False)
    notification = db.Column(db.Boolean, default=True)
    auto_ml = db.Column(db.Boolean, default=True)
    photo = db.Column(db.String(128), nullable=True)
    created_at = db.Column(db.DateTime(timezone=True), server_default=db.func.now())
    updated_at = db.Column(db.DateTime(timezone=True), server_default=db.func.now(), onupdate=db.func.now())
    notifications = db.relationship('Notification', backref='family_members', lazy='dynamic')

And Notification.py

class Notification(db.Model):
    __tablename__ = 'notifications'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    fm_id = db.Column(db.Integer, db.ForeignKey('family_members.id'))
    text = db.Column(db.String(255))
    read = db.Column(db.Boolean, default=False)
    type = db.Column(db.Integer)
    created_at = db.Column(db.DateTime(timezone=True), server_default=db.func.now())
    updated_at = db.Column(db.DateTime(timezone=True), server_default=db.func.now(), onupdate=db.func.now())

Regarding to this post, i have to explicitly state table name with __tablename__ = 'tablename', i've done that but it didn't work the way it supposed to and still got the error sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'notifications.fm_id' could not find table 'family_members' with which to generate a foreign key to target column 'id'. What should i do?

score 0 · Answer 1 · answered Jul 10 '20 at 10:12

You can use back_populates instead of backref in db.relationship()

class Notification(db.Model):
    __tablename__ = 'notifications'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    fm_id = db.Column(db.Integer, db.ForeignKey('family_members.id'))
    text = db.Column(db.String(255))
    read = db.Column(db.Boolean, default=False)
    type = db.Column(db.Integer)
    created_at = db.Column(db.DateTime(timezone=True), server_default=db.func.now())
    updated_at = db.Column(db.DateTime(timezone=True), server_default=db.func.now(), onupdate=db.func.now())
    family_members = db.relationship("FamilyMember", back_populates="notifications")

class FamilyMember(db.Model):
    __tablename__ = 'family_members'
    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    name = db.Column(db.String(120), index=True)
    email = db.Column(db.String(120), index=True, unique=True)
    password = db.Column(db.String(128), nullable=False)
    notification = db.Column(db.Boolean, default=True)
    auto_ml = db.Column(db.Boolean, default=True)
    photo = db.Column(db.String(128), nullable=True)
    created_at = db.Column(db.DateTime(timezone=True), server_default=db.func.now())
    updated_at = db.Column(db.DateTime(timezone=True), server_default=db.func.now(), onupdate=db.func.now())
    notifications = db.relationship("Notification", back_populates="family_member")

After hours, i figured out something that cause the relationship was not detected, i dunno why but only on FamilyMember model it is not auto reload when save on debug mode while every other models is auto reloading when saved file — Vicky Sultan, Jul 10 '20 at 14:54

sqlalchemy.exc.NoReferencedTableError: Foreign key could not find table

1 Answers1