title: SQL-Alchemy one-to-one relationship enforcement
body: <p>I set up a one-to-one relationship between two tables <code>user</code> and <code>transaction</code> using SQL-Alchemy relationships and following their <a href="http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html" rel="nofollow noreferrer">tutorial</a>.</p>
  
  <p>Creating two transactions <code>t1</code>, <code>t2</code> connected to the same user <code>u</code> (which shouldn't be possible under a 1:1 relationship) and committing both results in the foreign key of <code>t1</code> being set to <code>NULL</code> in the database with no error being thrown.</p>
  
  <pre><code>from sqlalchemy import create_engine, Integer, String, Column, ForeignKey
  from sqlalchemy.orm import backref, relationship, Session
  from sqlalchemy.ext.declarative import declarative_base
  engine = create_engine('sqlite:///storage.db', echo=True)
  Base = declarative_base()
  
  class User(Base):
      __tablename__ = 'user'
      id = Column(Integer, primary_key=True)
  
  class Transaction(Base):
      __tablename__ = 'transaction'
      id = Column(Integer, primary_key=True)
      user_id = Column(Integer, ForeignKey('user.id'))
      user = relationship(User, backref=backref('transaction', uselist=False))
  
  
  Base.metadata.create_all(engine)
  session = Session(bind=engine)
  u = User()
  t1 = Transaction()
  t1.user = u
  session.add(t1)
  session.commit()
  
  t2 = Transaction()
  t2.user = u
  session.add(t2)
  session.commit()
  </code></pre>
  
  <p><code>t1.user</code> evaluates to <code>NULL</code> after the execution</p>
  
  <p>This might quickly lead to an undesirable database state, is this behaviour desired or is there a way to strictly enforce the foreign key relationship?</p>