IntegrityError when inserting data in an association table using SQLAlchemy

Posted on

Question :

IntegrityError when inserting data in an association table using SQLAlchemy

I’m trying to insert the role data in this association table:

class Association(db.Model):
    __tablename__ = 'associations'
    user_id = db.Column(db.Integer, db.ForeignKey(''), primary_key=True)
    team_id = db.Column(db.Integer, db.ForeignKey(''), primary_key=True)
    role = db.Column(db.String)
    user = db.relationship("User", back_populates="teams")
    team = db.relationship("Team", back_populates="users")

It’s linked to 2 other tables:

class User(db.Model, UserMixin):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String)
    username = db.Column(db.String, nullable=False, unique=True, index=True)
    password_hash = db.Column(db.String)
    teams = db.relationship("Association", back_populates="user")

class Team(db.Model):
    __tablename__ = 'teams'
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String)
    player = db.Column(db.String)
    users = db.relationship("Association", back_populates="team")

However, when I try to insert the role in the association table:

t = Team.query.filter_by(id=team_name1_id).first()
a = Association(
a.user = User.query.filter_by(

I get this error:

IntegrityError: (raised as a result of Query-invoked autoflush;
consider using a session.no_autoflush block if this flush is occurring
prematurely) (sqlite3.IntegrityError) NOT NULL constraint failed:
association.team_id [SQL: u’INSERT INTO association (user_id, role)
VALUES (?, ?)’] [parameters: (1, u’point guard’)]

Do you know how can I fix it?

Asked By: nick


Answer #1:

The line that causes the autoflush is


You’ve not defined your relationship loading strategies, so they default to "select". Since you’re accessing the Team.users relationship attribute for the first time, it will emit a SELECT in order to fetch the related objects.

You’ve also made changes to the session indirectly in

a.user = User.query.filter_by(

which due to save-update cascade, on by default, places the new Association instance to the session as well.

In order to keep the DB’s and session’s state consistent SQLAlchemy has to flush your changes to the DB before the query is emitted, and so the half initialized Association is sent to the DB before being associated with the Team instance.

Possible solutions:

  1. Temporarily disable the autoflush feature for that particular append because you know you’re adding a new Association:

    with db.session.no_autoflush:

    The SELECT will still be emitted, though.

  2. Reverse the operation. Instead of appending the Association instance to the Team.users collection, assign the Team to the

    # Instead of t.users.append(a): = t  # Mr.
  3. A less obvious solution is to eager load the contents of the relationship when you fetch the Team instance, so that no SELECT is necessary:

    t = db.session.query(Team).options(db.joinedload(Team.users)).first()

    or not load them at all using the db.noload(Team.users) option.

Note that


is redundant. The fetched Team instance is already in the session – otherwise the lazy load of Team.users could not have proceeded in the first place.

Answered By: Ilja Everilä

Leave a Reply

Your email address will not be published. Required fields are marked *