SQLAlchemy foreign key for relationship

• with foreign key constraint

    teacher_id = db.Column(db.BigInteger, db.ForeignKey('teacher.id'), nullable=False, index=True)
    teacher = db.relationship(
        'Teacher', backref=db.backref('lectures', lazy='select')
    )

• without foreign key constraint

    teacher_id = db.Column(db.BigInteger, nullable=False, index=True)
    teacher = db.relationship(
        'Teacher', backref=db.backref('lectures', lazy='select'),
        foreign_keys=[teacher_id], primaryjoin='Teacher.id == Lecture.teacher_id'
    )

---

relationship이 추가될 경우, 여러번의 query가 발생한다.

2023-03-27 16:59:47,725 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2023-03-27 16:59:47,725 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-03-27 16:59:47,731 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2023-03-27 16:59:47,731 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-03-27 16:59:47,736 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
2023-03-27 16:59:47,736 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-03-27 16:59:47,742 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-03-27 16:59:47,751 INFO sqlalchemy.engine.Engine SELECT lecture.id AS lecture_id, lecture.teacher_id AS lecture_teacher_id, lecture.lecture_category_id AS lecture_lecture_category_id, lecture.name AS lecture_name, lecture.description AS lecture_description, lecture.price AS lecture_price, lecture.activated AS lecture_activated, lecture.created_at AS lecture_created_at, lecture.updated_at AS lecture_updated_at 
FROM lecture INNER JOIN teacher ON lecture.teacher_id = teacher.id LEFT OUTER JOIN lecture_category ON lecture.lecture_category_id = lecture_category.id LEFT OUTER JOIN enroll ON lecture.id = enroll.lecture_id 
WHERE lecture.activated = 1 
 LIMIT %(param_1)s, %(param_2)s
2023-03-27 16:59:47,752 INFO sqlalchemy.engine.Engine [generated in 0.00046s] {'param_1': 0, 'param_2': 100}
2023-03-27 16:59:47,761 INFO sqlalchemy.engine.Engine SELECT teacher.id AS teacher_id, teacher.name AS teacher_name 
FROM teacher 
WHERE teacher.id = %(pk_1)s
2023-03-27 16:59:47,762 INFO sqlalchemy.engine.Engine [generated in 0.00018s] {'pk_1': 3}
2023-03-27 16:59:47,764 INFO sqlalchemy.engine.Engine SELECT lecture_category.id AS lecture_category_id, lecture_category.name AS lecture_category_name 
FROM lecture_category 
WHERE lecture_category.id = %(pk_1)s
2023-03-27 16:59:47,764 INFO sqlalchemy.engine.Engine [generated in 0.00010s] {'pk_1': 2}
2023-03-27 16:59:47,768 INFO sqlalchemy.engine.Engine SELECT teacher.id AS teacher_id, teacher.name AS teacher_name 
FROM teacher 
WHERE teacher.id = %(pk_1)s
2023-03-27 16:59:47,768 INFO sqlalchemy.engine.Engine [cached since 0.006422s ago] {'pk_1': 1}
2023-03-27 16:59:47,769 INFO sqlalchemy.engine.Engine SELECT lecture_category.id AS lecture_category_id, lecture_category.name AS lecture_category_name 
FROM lecture_category 
WHERE lecture_category.id = %(pk_1)s
2023-03-27 16:59:47,769 INFO sqlalchemy.engine.Engine [cached since 0.005377s ago] {'pk_1': 3}
2023-03-27 16:59:47,771 INFO sqlalchemy.engine.Engine SELECT teacher.id AS teacher_id, teacher.name AS teacher_name 
FROM teacher 
WHERE teacher.id = %(pk_1)s
2023-03-27 16:59:47,771 INFO sqlalchemy.engine.Engine [cached since 0.009414s ago] {'pk_1': 4}
2023-03-27 16:59:47,772 INFO sqlalchemy.engine.Engine SELECT teacher.id AS teacher_id, teacher.name AS teacher_name 
FROM teacher 
WHERE teacher.id = %(pk_1)s
2023-03-27 16:59:47,772 INFO sqlalchemy.engine.Engine [cached since 0.01089s ago] {'pk_1': 5}
2023-03-27 16:59:47,775 INFO sqlalchemy.engine.Engine SELECT lecture_category.id AS lecture_category_id, lecture_category.name AS lecture_category_name 
FROM lecture_category 
WHERE lecture_category.id = %(pk_1)s
2023-03-27 16:59:47,775 INFO sqlalchemy.engine.Engine [cached since 0.01098s ago] {'pk_1': 4}
2023-03-27 16:59:47,778 INFO sqlalchemy.engine.Engine ROLLBACK