Many-to-Many relationship with composite keys in tables

models.py

import os

from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func, ForeignKeyConstraint
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()



class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    country_id = Column(Integer, primary_key=True)
    name = Column(String)
    employees = relationship(
        'Employee',
        secondary='department_employee_link'
    )


class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    hired_on = Column(DateTime, default=func.now())
    departments = relationship(
        Department,
        secondary='department_employee_link'
    )


class DepartmentEmployeeLink(Base):
    __tablename__ = 'department_employee_link'
    department_id = Column(Integer)
    department_country_id = Column(Integer)
    employee_id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    __table_args__ = (
        ForeignKeyConstraint(
            [department_id, department_country_id],
            [Department.id, Department.country_id]
        )
    )