title: One To Many Relationship definition with reflection and declarative syntax in sqlalchemy gives join condition error
body: <p>I am trying to set One-To-Many relationship on an existing database.</p>
  
  <p>Simplified DDL is :</p>
  
  <pre><code>create table accnt (
    code varchar(20) not null
    , def varchar(100)
    , constraint pk_accnt primary key (code)
  );
  
  commit;
  
  create table slorder (
    code varchar(20) not null
    , def varchar(100)
    , dt date
    , c_accnt varchar(20) not null
    , constraint pk_slorder primary key (code)
    , constraint fk_slorder_accnt foreign key (c_accnt)
       references accnt (code)
       on update cascade on delete cascade
  );
  
  commit;
  </code></pre>
  
  <p>SqlAlchemy Code :</p>
  
  <pre><code>from sqlalchemy import *
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.orm import *
  
  engine = create_engine('firebird://sysdba:masterkey@127.0.0.1/d:\\prj\\db2\\makki.fdb?charset=WIN1254', echo=False)
  Base = declarative_base()
  Base.metadata.bind = engine
  
  class Accnt(Base):
      __tablename__ = 'accnt'
      __table_args__ = {'autoload': True}
  
      defi = Column('def', String(100))
  
  class SlOrder(Base):
      __tablename__ = 'slorder'
      __table_args__ = {'autoload': True}
  
      defi = Column("def", String(100))
      accnt = relationship('Accnt', backref='slorders')
  </code></pre>
  
  <p>gives </p>
  
  <pre><code>sqlalchemy.exc.ArgumentError: Could not determine join condition between parent/child tables on relationship SlOrder.accnt.  Specify a 'primaryjoin' expression.  If 'secondary' is present, 'secondaryjoin' is needed as well.
  </code></pre>
  
  <p>error.</p>
  
  <p>My possible solutions to this problem are :</p>
  
  <p><strong>1</strong></p>
  
  <pre><code>class SlOrder(Base):
      __tablename__ = 'slorder'
      __table_args__ = {'autoload': True}
  
      defi = Column("def", String(100))
      c_accnt = Column("c_accnt", String(20), ForeignKey('accnt.code'))
  
      accnt = relationship('Accnt', backref='slorders')
  </code></pre>
  
  <p>But this approach needs that I have to add every foreign key constraint column manually,  which leads to making reflection useles. (Because I've got many columns references to other tables.)</p>
  
  <p><strong>2</strong></p>
  
  <pre><code>class SlOrder(Base):
      __table__ = Table('accnt', metadata, autoload = True, autoload_with=engine)
      accnt = relationship('Accnt', backref='slorders', primaryjoin=(__table__.c_accnt==Accnt.code))
  </code></pre>
  
  <p>This approach has an another consequence (please see <a href="https://stackoverflow.com/questions/7679893/how-to-override-a-column-name-in-sqlalchemy-using-reflection-and-descriptive-synt">my previous question</a>)</p>
  
  <p>So what am I missing? What is the best way to define a relationship both using reflection and declarative syntax?</p>
  
  <p>EDIT :</p>
  
  <p>I've figured that SqlAlchemy finds and builds relationships if child table has only one reference to parent table.</p>
  
  <p>But if child table has more than one reference as :</p>
  
  <pre><code>create table slorder (
    code varchar(20) not null
    , def varchar(100)
    , dt date
    , c_accnt varchar(20) not null
    , c_accnt_ref  varchar(20) 
    , constraint pk_slorder primary key (code)
    , constraint fk_slorder_accnt foreign key (c_accnt)
       references accnt (code)
       on update cascade on delete cascade
  
    , constraint fk_slorder_accnt_ref foreign key (c_accnt_ref)
       references accnt (code)
       on update cascade on delete no action
  );
  </code></pre>
  
  <p>the above error occurs. </p>
  
  <p>So is it expected behavior of SqlAlchemy to give error if there is more than one relation between two tables?</p>