SQLAlchemy: deny column copy for multiple one-to-manies

Question

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.

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.

I've simplified the tables as much as possible to demonstrate.

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

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,
)

Using viewonly isn't an option as some columns in the relationships do write. I've tried every combination of I can think of to denote foreign() 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.

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

• Am I on the right track?
• Are there any examples worth looking at outside of the docs to help with this?
• Is my pain caused by the fact that the primaryjoins use and_()?

Answer 1

Your problem comes from the fact you have two relationships that can modify A.id, 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.

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