title: One-to-many relationships SQLAlchemy that depend on each other
body: <p>I'm trying to get the following models working together. Firstly the scenario is as follows: </p>
  
  <ol>
  <li>A user can have many email addresses, but each email address can only be associated with one user;</li>
  <li>Each user can only have one <em>primary email address</em> (think of it like their current email address).</li>
  </ol>
  
  <p>An email address is a user's id, so they must always have one, but when they change it, I want to keep track of other ones they've used in the past. So far the setup is to have a helper table <code>user_emails</code> that holds a tie between an email and a user, which I hear is not supposed to be setup as a class in using the declarative SQLAlchemy approach (though I don't know why). Also, am I right in thinking that I need to use <code>use_alter=True</code> because the <code>users</code> table won't know the foreign key <code>email_id</code> until it's inserted?</p>
  
  <p><code>models.py</code> looks like this:</p>
  
  <pre><code>"""models.py"""
  user_emails = Table('user_emails', Base.metadata,
                  Column('user_id', Integer, ForeignKey('users.id'),
                         primary_key=True),
                  Column('email', String(50), ForeignKey('emails.address'),
                         primary_key=True))
  
  class User(Base):
      __tablename__ = 'users'
      id = Column(Integer, Sequence('usr_id_seq', start=100, increment=1), 
                  primary_key=True)
      email_id = Column(String(50),
                        ForeignKey('emails.address', use_alter=True, name='fk_email_id'),              
                        unique=True, nullable=False)
      first = Column(String(25), unique=True, nullable=False)
      last = Column(String(25), unique=True, nullable=False)
  
      def __init__(self, first, last):
          self.first = first
          self.last = last
  
  class Email(Base):
      __tablename__ = 'emails'
      address = Column(String(50), unique=True, primary_key=True)
      user = relationship(User, secondary=user_emails, backref='emails')
      added = Column(DateTime, nullable=False)
      verified = Column(Boolean, nullable=False)
  
      def __init__(self, address, added, verified=False):
          self.address = address
          self.added = added
          self.verified = verified
  </code></pre>
  
  <p>Everything seems OK until I try and commit to the DB:</p>
  
  <pre><code>&gt;&gt;&gt; user = models.User("first", "last")
  &gt;&gt;&gt; addy = models.Email("example@example.com", datetime.datetime.utcnow())
  &gt;&gt;&gt; addy
  &lt;Email 'example@example.com' (verified: False)&gt;
  &gt;&gt;&gt; user
  &gt;&gt;&gt; &lt;User None (active: True)&gt;
  &gt;&gt;&gt;
  &gt;&gt;&gt; user.email_id = addy
  &gt;&gt;&gt; user
  &gt;&gt;&gt; &lt;User &lt;Email 'example@example.com' (verified: False)&gt; (active: True)&gt;
  &gt;&gt;&gt; Session.add_all([user, addy])
  &gt;&gt;&gt; Session.commit()
  &gt;&gt;&gt; ...
  &gt;&gt;&gt; sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt type 'Email' "INSERT INTO users (id, email_id, first, last, active) VALUES (nextval('usr_id_seq'), %(email_id)s, %(first)s, %(last)s, %(active)s) RETURNING users.id" {'last': 'last', 'email_id': &lt;Email 'example@example.com' (verified: False)&gt;, 'active': True, 'first': 'first'}
  </code></pre>
  
  <p>So, I figure I'm doing something wrong/stupid, but I'm new to SQLAlchemy so I'm not sure what I need to do to setup the models correctly.</p>
  
  <p>Finally, assuming I get the right models setup, is it possible to add a relationship so that by loading an arbitrary email object I'll be able to access the user who owns it, from an attribute in the Email object?</p>
  
  <p>Thanks!</p>