Multiple foreign key paths linking the tables

Question

I'm doing a MCQ question/answers db schema. But this gave me an error. I'm pulling my hair on this one.

"Could not determine join condition between parent/child tables on relationship Question.options - 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."

class Question(db.Model):
    tablename = 'question'
    id = db.Column(db.Integer, primary_key=True)
    question_text = db.Column(db.Text)
    correct_option_id = db.Column(db.Integer, db.ForeignKey('option.id'))

    options = db.relationship('Option', backref='question', lazy='dynamic')

class Option(db.Model):
    tablename = 'option'
    id = db.Column(db.Integer, primary_key=True)
    question_id = db.Column(db.Integer, db.ForeignKey('question.id'))
    option_text = db.Column(db.Text)

score 0 · Accepted Answer · answered May 12 '20 at 07:50

Remove the correct_option_id Column from the Question Model. It creates ambiguity. So the database cannot identify what exactly is the correct relationship because of the 2 different foreign keys.

Instead, create a boolean column in Option named correct_answer. And use that to mark the correct answer.

class Question(db.Model):
    tablename = 'question'
    id = db.Column(db.Integer, primary_key=True)
    question_text = db.Column(db.Text)

    options = db.relationship('Option', backref='question', lazy='dynamic')

    # Newly added
    @property
    def correct_option_id(self):
        for option in self.options:
            if option.correct_answer:
                return option.id



class Option(db.Model):
    tablename = 'option'
    id = db.Column(db.Integer, primary_key=True)
    question_id = db.Column(db.Integer, db.ForeignKey('question.id'))
    option_text = db.Column(db.Text)

    # Newly added
    correct_answer = db.Column(db.Boolean, default=False)

Multiple foreign key paths linking the tables

1 Answers1