<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">if the following is defined:</p>
<p dir="auto">parent table:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="members = sqlalchemy.Table(
    'members',z3c.zalchemy.metadata,
    sqlalchemy.Column('userName', sqlalchemy.String,primary_key = True),
    sqlalchemy.Column('firstName', sqlalchemy.Unicode),
    sqlalchemy.Column('lastName', sqlalchemy.Unicode),
    sqlalchemy.Column('contactEmail', sqlalchemy.Unicode),
    sqlalchemy.Column('photo', sqlalchemy.Binary,
                      sqlalchemy.PassiveDefault(&quot;&quot;)),
    redefine=True
    )"><pre class="notranslate"><code class="notranslate">members = sqlalchemy.Table(
    'members',z3c.zalchemy.metadata,
    sqlalchemy.Column('userName', sqlalchemy.String,primary_key = True),
    sqlalchemy.Column('firstName', sqlalchemy.Unicode),
    sqlalchemy.Column('lastName', sqlalchemy.Unicode),
    sqlalchemy.Column('contactEmail', sqlalchemy.Unicode),
    sqlalchemy.Column('photo', sqlalchemy.Binary,
                      sqlalchemy.PassiveDefault("")),
    redefine=True
    )
</code></pre></div>
<p dir="auto">parent class mapper:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="sqlalchemy.mapper(Member,members,properties={
    '_personalAddress':sqlalchemy.relation(AddressInfo,
                                    uselist=False,
                                    backref='__parent__',
                                    cascade=&quot;all, delete-orphan&quot;,
                                    primaryjoin=sqlalchemy.and_(
    members.c.userName==addresses.c.userName, 
    addresses.c.addressType==1
    )),})"><pre class="notranslate"><code class="notranslate">sqlalchemy.mapper(Member,members,properties={
    '_personalAddress':sqlalchemy.relation(AddressInfo,
                                    uselist=False,
                                    backref='__parent__',
                                    cascade="all, delete-orphan",
                                    primaryjoin=sqlalchemy.and_(
    members.c.userName==addresses.c.userName, 
    addresses.c.addressType==1
    )),})
</code></pre></div>
<p dir="auto">joined table:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="addresses = sqlalchemy.Table(
    'addresses',z3c.zalchemy.metadata,
    sqlalchemy.Column('userName', sqlalchemy.Unicode,
                      sqlalchemy.ForeignKey('members.userName'),
                      primary_key=True,
                      nullable=False),
    sqlalchemy.Column('addressType', sqlalchemy.Integer,
                      nullable=False,
                      primary_key=True),
    sqlalchemy.Column('organisation', sqlalchemy.Unicode),
    sqlalchemy.Column('address', sqlalchemy.Unicode),
    sqlalchemy.Column('zipCode', sqlalchemy.Unicode),
    sqlalchemy.Column('city', sqlalchemy.Unicode),
    sqlalchemy.Column('state', sqlalchemy.Unicode, default=u'AT'),
    sqlalchemy.Column('email', sqlalchemy.Unicode),
    sqlalchemy.Column('phone', sqlalchemy.Unicode),
    sqlalchemy.Column('homePage', sqlalchemy.Unicode),
    sqlalchemy.Column('isContact', sqlalchemy.Boolean),
    )"><pre class="notranslate"><code class="notranslate">addresses = sqlalchemy.Table(
    'addresses',z3c.zalchemy.metadata,
    sqlalchemy.Column('userName', sqlalchemy.Unicode,
                      sqlalchemy.ForeignKey('members.userName'),
                      primary_key=True,
                      nullable=False),
    sqlalchemy.Column('addressType', sqlalchemy.Integer,
                      nullable=False,
                      primary_key=True),
    sqlalchemy.Column('organisation', sqlalchemy.Unicode),
    sqlalchemy.Column('address', sqlalchemy.Unicode),
    sqlalchemy.Column('zipCode', sqlalchemy.Unicode),
    sqlalchemy.Column('city', sqlalchemy.Unicode),
    sqlalchemy.Column('state', sqlalchemy.Unicode, default=u'AT'),
    sqlalchemy.Column('email', sqlalchemy.Unicode),
    sqlalchemy.Column('phone', sqlalchemy.Unicode),
    sqlalchemy.Column('homePage', sqlalchemy.Unicode),
    sqlalchemy.Column('isContact', sqlalchemy.Boolean),
    )
</code></pre></div>
<p dir="auto">it results in the following error</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;/Users/bd/zopes/fhvao/lib/python/fhvao/tests.py&quot;, line 5, in ?
    from fhvao.member import members,Member
  File &quot;/Users/bd/zopes/fhvao/lib/python/fhvao/member.py&quot;, line 185, in ?
    addresses.c.addressType==1
  File &quot;/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/__init__.py&quot;, line 50, in mapper
    return Mapper(class_, table, *args, **params)
  File &quot;/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/mapper.py&quot;, line 253, in __init__
    prop.init(key, self)
  File &quot;/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/mapper.py&quot;, line 866, in init
    self.do_init(key, parent)
  File &quot;/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/properties.py&quot;, line 215, in do_init
    self._find_dependent()
  File &quot;/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/properties.py&quot;, line 291, in _find_dependent
    self.primaryjoin.accept_visitor(visitor)
  File &quot;/Users/bd/zopes/fhvao/lib/python/sqlalchemy/sql.py&quot;, line 858, in accept_visitor
    c.accept_visitor(visitor)
  File &quot;/Users/bd/zopes/fhvao/lib/python/sqlalchemy/sql.py&quot;, line 947, in accept_visitor
    visitor.visit_binary(self)
  File &quot;/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/properties.py&quot;, line 752, in visit_binary
    self.func(binary)
  File &quot;/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/properties.py&quot;, line 282, in foo
    raise exceptions.ArgumentError(&quot;bidirectional dependency not supported...specify foreignkey&quot;)
ArgumentError: bidirectional dependency not supported...specify foreignkey"><pre class="notranslate"><code class="notranslate">Traceback (most recent call last):
  File "/Users/bd/zopes/fhvao/lib/python/fhvao/tests.py", line 5, in ?
    from fhvao.member import members,Member
  File "/Users/bd/zopes/fhvao/lib/python/fhvao/member.py", line 185, in ?
    addresses.c.addressType==1
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/__init__.py", line 50, in mapper
    return Mapper(class_, table, *args, **params)
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/mapper.py", line 253, in __init__
    prop.init(key, self)
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/mapper.py", line 866, in init
    self.do_init(key, parent)
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/properties.py", line 215, in do_init
    self._find_dependent()
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/properties.py", line 291, in _find_dependent
    self.primaryjoin.accept_visitor(visitor)
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/sql.py", line 858, in accept_visitor
    c.accept_visitor(visitor)
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/sql.py", line 947, in accept_visitor
    visitor.visit_binary(self)
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/properties.py", line 752, in visit_binary
    self.func(binary)
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/properties.py", line 282, in foo
    raise exceptions.ArgumentError("bidirectional dependency not supported...specify foreignkey")
ArgumentError: bidirectional dependency not supported...specify foreignkey
</code></pre></div>
<p dir="auto">this only happens if the column in the join is a primary_key in the child table, if you change the code above to:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="    sqlalchemy.Column('addressType', sqlalchemy.Integer,
                      nullable=False,
                      primary_key=False),"><pre class="notranslate"><code class="notranslate">    sqlalchemy.Column('addressType', sqlalchemy.Integer,
                      nullable=False,
                      primary_key=False),
</code></pre></div>
<p dir="auto">it works.</p>
<p dir="auto">a forther question:</p>
<p dir="auto">shouldn't the addressType be set to 1 automatically if i assign an address object to obj._personalAddress which has no addressType specified?</p>
<p dir="auto">Regards, Bernd</p>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">_find_dependent is looking at the join condition for either side being a primary key but doesnt look for any ForeignKey objects.  attached test program fails.</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/151/test.py">test.py</a></p>