SQLAlchemy, MySQL Error: Failed to add the foreign key constraint. Missing index for constraint in the referenced table

Question

I'm trying to create a responses table that contains a userid (corresponding to a user) and a questionid (corresponding to a specific question) using SQL Alchemy.

This is my code:

class Quiz(Base):
    __tablename__ = 'quiz'
    quizid = Column(Integer, primary_key=True)
    ...

class Question(Base):
    __tablename__ = 'question'
    quizid = Column(Integer, ForeignKey('quiz.quizid'), primary_key=True)
    questionid = Column(Integer, primary_key=True),
    ...

class Response(Base):
    __tablename__ = 'response'
    userid = Column(String(256), ForeignKey('user.userid'))
    questionid = Column(Integer, ForeignKey('question.questionid'))
    response = Column(String(16))

This results in the following error: (pymysql.err.OperationalError) (1822, "Failed to add the foreign key constraint. Missing index for constraint 'response_ibfk_1' in the referenced table 'question'")

Does anyone knoww how to approach this problem, specifically using SQLAlchemy?

Searching online, I've tried explicitly adding these relationships under the Response class but still seem to get the same error.

    user = relationship(User)
    question = relationship(Question)

.

Any help is appreciated--thank you!

Answer 1

The issue is coming from the composite primary key for the table Question.

By default, an index will be created for the primary key. From your definition it will create an index on question (questionid,quizid). So no index on questionid, which causes the error.

The easiest way to solve it is to add an index on the column :

questionid = Column(Integer, primary_key=True, index=True)

However, not sure that it's the right solution.

From your code, Response is linked to Question only by questionid which should mean that questionid is unique. If it's indeed the case, the primary key of Question should only be questionid instead of a composite one.

Note: you have an extra comma at the end of the line of the Question.questionid column declaration