SQLAlchemy: deny column copy for multiple one-to-manies
<p>I've had a set of relationships running SQLAlchemy 0.9.10 in production for over a year and I'm looking to upgrade to 1.0+. All the tests pass after simply upgrading the package, however, new warnings are now being logged.</p>

<blockquote>
  <p>SAWarning: relationship 'A.c' will copy column test_c.a_id to column
  test_a.id, which conflicts with relationship(s): 'A.b' (copies
  test_b.a_id to test_a.id). Consider applying viewonly=True to
  read-only relationships, or provide a primaryjoin condition marking
  writable columns with the foreign() annotation.</p>
</blockquote>

<p>I've simplified the tables as much as possible to demonstrate.</p>

<p>The idea is that the parent <code>A</code> has two one-to-many child relationships (<code>B</code> and <code>C</code>) that behave as one-to-ones by each child denoting their "current" ID on the parent.</p>

<pre><code>class A(Base):
    __tablename__ = 'test_a'

    id = Column(BigInteger, primary_key=True, autoincrement='ignore_fk')
    b_id = Column(BigInteger)
    c_id = Column(BigInteger)

    __table_args__ = (
        ForeignKeyConstraint(
            ['id', 'b_id'],
            ['test_b.a_id', 'test_b.id'],
            use_alter=True,
            name='a_b_fk',
        ),
        ForeignKeyConstraint(
            ['id', 'c_id'],
            ['test_c.a_id', 'test_c.id'],
            use_alter=True,
            name='a_c_fk',
        ),
    )


class B(Base):
    __tablename__ = 'test_b'

    id = Column(BigInteger, autoincrement=True, primary_key=True)
    a_id = Column(ForeignKey('test_a.id'), primary_key=True)


class C(Base):
    __tablename__ = 'test_c'

    id = Column(BigInteger, autoincrement=True, primary_key=True)
    a_id = Column(ForeignKey('test_a.id'), primary_key=True)


# A has exactly one *current* B
A.b = sa_orm.relationship(
    B,
    primaryjoin=sa_sql.and_(A.id == B.a_id,
                            A.b_id == B.id),
    uselist=False,
)

# Every C refers to a single A
C.a = sa_orm.relationship(
    A,
    primaryjoin=C.a_id == A.id,
    foreign_keys=[C.a_id],
)

# Conversely, every A has zero or one C
A.c = sa_orm.relationship(
    C,
    primaryjoin=sa_sql.and_(A.id == C.a_id,
                            A.c_id == C.id),
    uselist=False,
)
</code></pre>

<p>Using <code>viewonly</code> isn't an option as some columns in the relationships do write. 
I've tried every combination of I can think of to denote <code>foreign()</code> on the relationship definitions, but I haven't been able to get the warnings to stop while still having the code function. After reading the docs and a good chunk of the SA code base, I'm seem to be stuck on how to set this up.</p>

<p>As far as I understand, I need to denote on <code>B</code> and <code>C</code> that the value of <code>A.id</code> is immutable. </p>

<ul>
<li>Am I on the right track? </li>
<li>Are there any examples worth looking at outside of <a href="http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#creating-custom-foreign-conditions" rel="nofollow">the docs</a> to help with this?</li>
<li>Is my pain caused by the fact that the <code>primaryjoin</code>s use <code>and_()</code>?</li>
</ul>

<p>Your problem comes from the fact you have two relationships that can modify <code>A.id</code>, which in turn stems from the fact that you've named it as part of two separate relationships. Whereas technically SQL allows you to do this, SQLAlchemy is more rigid in the patterns that it allows you to model.</p>

<p>The correct solution is to not have <code>A.id</code> be part of the join, since it's not strictly necessary. <code>b_id</code> and <code>c_id</code> columns have all the information you need to find the right column in <code>B</code> and <code>C</code> tables. You can keep the reference to <code>A.id</code> in the foreign keys if you like, if you are concern about data integrity.</p>

python postgresql sqlalchemy