Multiple Foreign key SqlAlchemy join error in flask

I have two classes. An institution can have multiple students. Hence, I have two classes as given below:

class University(db.Model):
    country = db.Column(db.String(120), primary_key=True)
    name = db.Column(db.String(120), primary_key=True)
    students = db.relationship('Student', backref='phd_granting_university', lazy='dynamic')

class Student(db.Model):
    name = db.Column(db.String(120), primary_key=True)
    phd_graduation_year = db.Column(db.Integer, primary_key=True)
    phd_granting_university_name = db.Column(db.String(120), db.ForeignKey('university.name'), primary_key=True)
    phd_granting_university_country = db.Column(db.String(120), db.ForeignKey('university.country'), primary_key=True)

I get the following error:

Could not determine join condition between parent/child tables on relationship University.students - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

Solution 1:

A composite foreign key must be declared at the table level. To do this in SQLAlchemy's ORM, the constraint is declared in the __table_args__ attribute:

class Student(db.Model):
    name = db.Column(db.String(120), primary_key=True)
    phd_graduation_year = db.Column(db.Integer, primary_key=True)
    phd_granting_university_name = db.Column(db.String(120), primary_key=True)
    phd_granting_university_country = db.Column(db.String(120), primary_key=True)

    __table_args__ = (
        db.ForeignKeyConstraint(
            [phd_granting_university_name, phd_granting_university_country],
            ['university.name', 'university.country'],
        ),
    )

This will generate this DDL:

CREATE TABLE students (
        name VARCHAR(120) NOT NULL, 
        phd_graduation_year INTEGER NOT NULL, 
        phd_granting_university_name VARCHAR(120) NOT NULL, 
        phd_granting_university_country VARCHAR(120) NOT NULL, 
        PRIMARY KEY (name, phd_graduation_year, phd_granting_university_name, phd_granting_university_country), 
        FOREIGN KEY(phd_granting_university_name, phd_granting_university_country) REFERENCES university (name, country)
)