Sqlalchemy One - Many and One-One in one table

Question

I've got two models: User and Group.

User can be in one group so:

class User(db.Model):
    # other fields
    group_id = db.Column(db.Integer(), db.ForeignKey('group.id'))

but on the other hand I would also have some info about user who create that specific group:

class Group(db.Model):
    # other fields
    users = db.relationship("User", backref='group')
    created_by = db.Column(db.Integer(), db.ForeignKey('user.id'))

Result is:

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

I tried use_alter=True, but it gives me:

sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint ForeignKeyConstraint(

Answer 1

Interestingly I'd expect you to get an AmbiguousForeignKeyError but instead you seem to get a CircularDependencyError? According to the docs this is caused by two scenarios:

• In a Session flush operation, if two objects are mutually dependent on each other, they can not be inserted or deleted via INSERT or DELETE statements alone; an UPDATE will be needed to post-associate or pre-deassociate one of the foreign key constrained values. The post_update flag described at Rows that point to themselves / Mutually Dependent Rows can resolve this cycle.
• In a MetaData.sorted_tables operation, two ForeignKey or ForeignKeyConstraint objects mutually refer to each other. Apply the use_alter=True flag to one or both, see Creating/Dropping Foreign Key Constraints via ALTER.

I'm not sure what you're executing that's causing this particular error, but most likely you'll be able to solve it by solving the ambiguous reference.

The ambigious reference is due to SQLAlchemy not being able to figure out how to perform the join when there are multiple references (users and created_by in this case). This can be resolved by specifying how the relationship should join which can be done by either giving the specific foreign key it should use or by explicitly determining the join condition.

You can see these being applied to your example here:

class User(Base):
    # Other setup / fields

    group_id = Column(Integer, ForeignKey('group.id'))

class Group(Base):
    # Other setup / fields

    created_by_id = Column(Integer, ForeignKey('user.id'), nullable=False)
    created_by = relationship("User", foreign_keys=[created_by_id])
    users = relationship("User", backref="group", primaryjoin=id==User.group_id)

Documentation regarding relationship joins: http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#configuring-how-relationship-joins