python - One to many + one relationship in SQLAlchemy? -

i'm trying model following situation: program has many versions, , 1 of versions current 1 (not latest).

this how i'm doing now:

class program(base):     __tablename__ = 'programs'     id = column(integer, primary_key=true)     name = column(string)     current_version_id = column(integer, foreignkey('program_versions.id'))      current_version = relationship('programversion', foreign_keys=[current_version_id])     versions = relationship('programversion', order_by='programversion.id', back_populates='program')   class programversion(base):     __tablename__ = 'program_versions'     id = column(integer, primary_key=true)     program_id = column(integer, foreignkey('programs.id'))     timestamp = column(datetime, default=datetime.datetime.utcnow)      program = relationship('filter', foreign_keys=[program_id], back_populates='versions') 

but error: not determine join condition between parent/child tables on relationship program.versions - there multiple foreign key paths linking tables. specify 'foreign_keys' argument, providing list of columns should counted containing foreign key reference parent table.

but foreign key should provide 'program.versions' relationship? there better way model situation?

this design not ideal; having 2 tables refer 1 another, cannot insert either table, because foreign key required in other not exist. 1 possible solution in outlined in selected answer of this question related microsoft sqlserver, summarize/elaborate on here.

a better way model might introduce third table, versionhistory, , eliminate foreign key constraints on other 2 tables.

class versionhistory(base):     __tablename__ = 'version_history'     program_id = column(integer, foreignkey('programs.id'), primary_key=true)     version_id = column(integer, foreignkey('program_version.id'), primary_key=true)     current = column(boolean, default=false)     # i'm not familiar sqlalchemy, suspect relationship      # information goes here somewhere 

this eliminates circular relationship have created in current implementation. query table program, , receive existing versions program, etc. because of composite primary key in table, access specific program/version combination. addition of current field table takes burden of tracking currency off of other 2 tables, although maintaining single current version per program require trigger gymnastics.

hth!