This article is part 7 of 11 in the series Python SQLAlchemy Tutorial

Association Tables

In our previous articles, we used an association table to model many-to-many relationships between tables, such as the relationship between Department and Employee. In this article, we are going to dive deeper into the association table concept and see how we can use it to further solve more complicated problems.

DepartmentEmployeeLink and Extra Data

In our previous article, we created the following SQLAlchemy models:

Notice that the DepartmentEmployeeLink class contains two foreign key columns which are enough to model the many-to-many relationship between Department and Employee. Now let's add one more column extra_data and two more relationships department and employee.

With one more extra column and two more relationships on the DepartmentEmployeeLink association model, we can store more information and be more liberal with how we want to use it. For example, suppose we have an employee John who works part-time in the IT department, we can insert the string 'part-time' into the column extra_data and create an DepartmentEmployeeLink object to represent this relationship.

Then, we can find John by querying the IT department or the DepartmentEmployeeLink model.

Finally, adding an IT employee using the relationship Department.employees still works, as shown in the previous article:

Linking Relationships with Backref

One common keyword argument we have used so far in relationship definitions is backref. A backref is a common shortcut to place a second relationship() onto the destination table. For example, the following code puts a second relationship() "posts" onto the user table by specifying a backref on Post.owner:

This is equivalent to the following definition:

Now we have a one-to-many relationship between User and Post. We can interact with these two models in the following way:


Creating a one-to-one relationship between models is very similar to creating many-to-one relationships. By modifying the uselist argument's value to False in a backref(), we force the database models to be mapped to each other in a one-to-one relationship.

Then, we can use the models in the following way:

Relationship Update Cascades

In a relational database, referential integrity guarantees that when the primary key of a referenced object in a one-to-many or many-to-many relationship changes, the refering objects' foreign keys that reference the primary key will change as well. However, for databases that do not support referential integrity, such as SQLite or MySQL with their referential integrity option turned off, changing the primary key values of a referenced object does not trigger updates of the refering objects. In this case, we can use the passive_updates flag in relationship or backref to inform the database to execute extra SELECT and UPDATE statements that will update the values of the refering objects' foreign keys.

In the following example, we construct a one-to-many relationship between User and Address and not specifying the passive_updates flag in the relationship. The database backend is SQLite.

Then, when we change the primary key value of a User object, its Address objects' user_id foreign key values will not change. Therefore, when you want to access an address's user object again, you will get an AttributeError.

If we specify the passive_updates flag in the Address model, then we can change the primary key of john and expect SQLAlchemy to issue extra SELECT and UPDATE statements to keep home_of_john.user and office_of_john.user up-to-date.


In this article, we dig a little deeper into SQLAlchemy's association tables and the backref keyword argument. It's often crucial to understand the mechanism behind these two concepts to fully master complex join queries, as what will be shown in future articles.

To Practice: Try this interactive course on the basics of Lists, Functions, Packages and NumPy in Python.

About The Author

  • RM

    Hi. I am newbie to SQLAlchemy. This line creates an employee attribute and links it with the Employee class. I did not understand the backref part. Can you please explain, why does backref points to department_assoc?
    employee = relationship(Employee, backref=backref(“department_assoc”))

    And, when the following line is called, department_id and employee_id fields in DepartmentEmployeeLink gets automatically initialized?
    John_working_part_time_at_IT = DepartmentEmployeeLink(department=IT, employee=John, extra_data=’part-time’)

    • Xiaonuo Gantan

      According to, backref adds an attribute “department_assoc” on Employee objects. The name “department_assoc” is not special and it can be specified as any string.

      In this line John_working_part_time_at_IT = DepartmentEmployeeLink(department=IT, employee=John, extra_data=’part-time’), the value of is inserted into the new DepartmentEmployeeLink object’s department_id field and the value of into the employee_id field by SQLAlchemy. It helps you by mapping Python objects IT and John into the field / column values of the department_employee_link table.

  • Fernando Colmenarez

    Hi Xiaonuo, in DepartmentEmployeeLink(Base) donĀ“t used multiple primary key , in this case using , __table_args__ = (PrimaryKeyConstraint(’employee_id’, ‘departament_id’,name=’pk’), { } ) not if I’m wrong but worked for me so.. sorry my english

  • Maxime


    First off: Thank you for this article!

    Regarding the ‘DepartmentEmployeeLink and Extra Data’:
    I have a issue here and maybe someone could help my out with this…
    Instead of using the association table to join the department and the employee we could do something like:
    But my problem is: from IT, I can do: IT.employee_assoc and it gives me the association object. However, from john, if I do john.department_assoc, it is empty.

    When I the the association object from the IT and look into it, both ’employee’ and ‘department’ fields are filled in correctly… So why can’t I get the department_assoc from john in that case?


    • Maxime

      Hmmm… never mind… it seems to be an IDE issue… It all works fine now…. Sorry about that… thanks!