sqlalchemy.exc.CircularDependencyError: Can't sort tables for DROP; an unresolvable foreign key dependency exists between tables

Question

I'm stuck on the error in the title of this post. I have a table Question that has a double connection (one to one and one to many) with another table Answer. I can create the tables, but I can't drop them for some reason.

I tried to add a "primary join" argument as suggested in this post but to no avail.

Why is this code failing?

#Initialize database
db = SQLAlchemy(app)

class Question(db.Model):
    __tablename__ = "Question"
    id = Column(Integer, primary_key=True)
    question = Column(String(256),nullable=False)
    correct_answer_id = Column(Integer,ForeignKey('Answer.id'))

    correct_answer = relationship("Answer",foreign_keys=[correct_answer_id],uselist=False,primaryjoin="Question.correct_answer_id==Answer.id")
    answers = relationship("Answer", backref="Question", primaryjoin="Question.id==Answer.question_id")

class Answer(db.Model):
    __tablename__ = "Answer"
    id = Column(Integer, primary_key=True)
    question_id = Column(Integer, ForeignKey('Question.id'),nullable=False)
    answer = Column(String(256))

db.drop_all()

db.create_all()

Answer 1

The solution for your issue is in the part of the error message that you didn't include in the question:

sqlalchemy.exc.CircularDependencyError: Can't sort tables for DROP; an unresolvable foreign key dependency exists between tables: Answer, Question. Please ensure that the ForeignKey and ForeignKeyConstraint objects involved in the cycle have names so that they can be dropped using DROP CONSTRAINT.

That is, instead of

    correct_answer_id = Column(Integer, ForeignKey("Answer.id"))

use something like

    correct_answer_id = Column(
        Integer, ForeignKey("Answer.id", name="fk_question_correct_answer")
    )

(and similarly for the FK in the other table).

Edit:

If the table already exists and the database has assigned a name to the foreign key then you can use that name instead of making up your own. For example, SQL Server assigns names like this:

> select TABLE_NAME, CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY' and TABLE_NAME in ('Question', 'Answer')

TABLE_NAME  CONSTRAINT_NAME
----------  ------------------------------
Question    FK__Question__correc__656C112C
Answer      FK__Answer__question__66603565

(2 rows affected)

so we can use

    correct_answer_id = Column(
        Integer, ForeignKey("Answer.id", name="FK__Question__correc__656C112C")
    )