SQLAlchemy 같은 테이블에 여러 개의 Foreign key 연결하기

문제상황

class User(db.Model):
    ...

class Store(db.Model):

    ...

    # 메인 유저
    main_user = db.Column(db.Integer, db.ForeignKey('user.id'))
    user_main = db.relationship('User', backref=db.backref('store_by_main', uselist=False))

    # 서브 유저
    sub_user = db.Column(db.Integer, db.ForeignKey('user.id'))
    user_sub = db.relationship('User', backref=db.backref('store_by_sub', uselist=False))

 

그냥 연결하면 Ambiguouse 외래키라고 아래처럼 에러가 남

 

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Store.user_main - there are multiple foreign key paths linking the tables.  Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

 

 

 

 

해결

relationship에 속성으로 foreign_keys=[...]을 추가해줌

class User(db.Model):
    ...

class Store(db.Model):
    ...

    # 메인 유저
    main_user = db.Column(db.Integer, db.ForeignKey('user.id'))
    user_main = db.relationship('User', foreign_keys=[main_user], backref=db.backref('store_by_main', uselist=False))

    # 서브 유저
    sub_user = db.Column(db.Integer, db.ForeignKey('user.id'))
    user_sub = db.relationship('User', foreign_keys=[sub_user], backref=db.backref('store_by_sub', uselist=False))

 

 

 

 

참고

https://stackoverflow.com/questions/7548033/how-to-define-two-relationships-to-the-same-table-in-sqlalchemy

How to define two relationships to the same table in SQLAlchemy