I have already succeeded in adding one many-to-many relationship in my database. However, when trying to add another, I am met with:
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'user_shiftTemplate.template_id' could not find table 'shifttemplate' with which to generate a foreign key to target column 'id'
I have defined the association table before my two tables I am trying to create a relationship with:
user_shiftTemplate = db.Table('user_shiftTemplate',
db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
db.Column('template_id', db.Integer, db.ForeignKey('shifttemplate.id'))
)
And here are my actual two tables I am trying to create the relationship for (again, defined AFTER the association table shown above)
class User(db.Model, UserMixin):
id = db.Column(db.Integer, primary_key=True)
oauth_id = db.Column(db.String(512), nullable=False, unique=True, default='')
ext_id = db.Column(db.Integer, nullable=False, unique=True)
name = db.Column(db.String(300), nullable=False)
email = db.Column(db.String(256), unique=True, nullable=False)
phone = db.Column(db.Integer, nullable=False)
date_joined = db.Column(db.DateTime, nullable=False, default=getPacificTime)
password = db.Column(db.String(60), nullable=False)
meta = db.Column(db.JSON, nullable=False, default={})
shifts = db.relationship('Shift', secondary=user_shift, backref='employees')
#^^^^ This is the other many-to-many relationship which actually works ^^^^
shiftTemplates = db.relationship('ShiftTemplate', secondary=user_shiftTemplate, backref='employees')
class ShiftTemplate(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(128), nullable=False)
duration = db.Column(db.Integer, nullable=False)
maxEmployees = db.Column(db.Integer, nullable=False)
minEmployees = db.Column(db.Integer, nullable=False)
startTime = db.Column(db.String(4), nullable=False)
I have carefully made sure that the format of my ShiftTemplate table matches that of the other many-to-many relationship table I have successfully made. However, something is causing SQLAlchemy to not recognize my ShiftTemplate table for some reason. It should also be noted that in the db.ForeignKey('shifttemplate.id') line of the association table definition I have tried every capitalization combination, from shiftTemplate to ShiftTemplate to Shifttemplate and they all return the same error, so I doubt that is the cause.
I renamed my ShiftTemplate database table class to "Template" and updated all references to the table name accordingly. I have yet to discover why this works.