Overlapping FK problem

[sector119]

I get following warning with my model:

SAWarning: relationship 'Transaction.organization' will copy column organizations.schema to column transactions.schema, which conflicts with relationship(s): 'Transaction.service' (copies services.schema to transactions.schema). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards.   The 'overlaps' parameter may be used to remove this warning.

I have all my tables partitioned by "schema" column so I have to put it to every PK and FK

I add "primaryjoin" to my relations as described at https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#overlapping-foreign-keys

but still get that warning. How can I fix it?

Thank You 

class Transaction(Base):
__tablename__ = 'transactions'

schema = Column(String(63), nullable=False, index=True)
id = Column(BigInteger, nullable=False, index=True)

user_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + '.users.id'), nullable=False, index=True)
office_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + '.offices.id'), nullable=False, index=True)
service_id = Column(Integer, nullable=False, index=True)
organization_id = Column(Integer, nullable=False, index=True)

...

service = relationship('Service',
primaryjoin='and_(Service.schema == foreign(Transaction.schema), Service.id == foreign(Transaction.service_id))')
organization = relationship('Organization',
primaryjoin='and_(Organization.schema == foreign(Transaction.schema), Organization.id == foreign(Transaction.organization_id))')
person = relationship('Person',
primaryjoin='and_(Person.schema == foreign(Transaction.schema), Person.id == foreign(Transaction.person_id))')
rollback_user = relationship('User', primaryjoin='User.id == Transaction.rollback_user_id')

__table_args__ = (
PrimaryKeyConstraint('schema', 'id'),
ForeignKeyConstraint(
(schema, service_id),
(Service.schema, Service.id)
),
ForeignKeyConstraint(
(schema, organization_id),
(Organization.schema, Organization.id)
),
{
'postgresql_partition_by': 'LIST (schema)'
}
)

[Mike Bayer]

by having foreign() on the Transaction.schema column, that means when you do this:

t1 = Transaction()

t1.service = some_service()

the ORM is being instructed to copy some_service.schema over to t1.schema.   Because "foreign" means "this is the column that mirrors the value of a canonical value on the related row".

so you have to decide what should happen if you did this:

t1.service = Service(schema="a", ...)

t1.organiazation = Organization(schema="b", ...)

t1.person = Person(schema="c", ...)

is the above possible?  or an error condition?

overall, if the plan is that "schema" will match across all the objects involved, and your application will make sure those are all set as needed, just remove the foreign() annotation from the Transaction.service column.  the primary joins already have enough information based on the service_id, organization_id and person_id columns.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/0d422feb-956f-4efc-b3c0-17473652c603n%40googlegroups.com.

[sector119]

Thank You, Mike,

Do you mean that I have to remove all foreign() annotations from all my relationships like this?

service = relationship('Service', primaryjoin='and_(Service.schema == Transaction.schema, Service.id == Transaction.service_id)')
organization = relationship('Organization', primaryjoin='and_(Organization.schema == Transaction.schema, Organization.id == Transaction.organization_id)')
person = relationship('Person', primaryjoin='and_(Person.schema == Transaction.schema, Person.id == Transaction.person_id)')

if so, I still get the same warning..

And must I set primaryjoin for service and organization relationships at all if I've set up FK for them

ForeignKeyConstraint(
(schema, service_id),
(Service.schema, Service.id)
)

and

ForeignKeyConstraint(
(schema, organization_id),
(Organization.schema, Organization.id)
),

понедельник, 28 декабря 2020 г. в 17:39:25 UTC+2, Mike Bayer:

[sector119]

>>> is the above possible?  or an error condition?

I don't want to restrict that case 

>>> overall, if the plan is that "schema" will match across all the objects involved, and your application will make sure those are all set as needed, just

>>> remove the foreign() annotation from the Transaction.service column.  the primary joins already have enough information based on the

>>> service_id, organization_id and person_id columns.

"schema" will match across all the objects - 100%

понедельник, 28 декабря 2020 г. в 17:39:25 UTC+2, Mike Bayer:

by having foreign() on the Transaction.schema column, that means when you do this:

[Mike Bayer]

On Mon, Dec 28, 2020, at 12:37 PM, sector119 wrote:

Thank You, Mike,

Do you mean that I have to remove all foreign() annotations from all my relationships like this?

service = relationship('Service', primaryjoin='and_(Service.schema == Transaction.schema, Service.id == Transaction.service_id)')

organization = relationship('Organization', primaryjoin='and_(Organization.schema == Transaction.schema, Organization.id == Transaction.organization_id)')

person = relationship('Person', primaryjoin='and_(Person.schema == Transaction.schema, Person.id == Transaction.person_id)')

no, only the foreign() annotation that surrounds "Transaction.schema".  leave the one that surrounds "Transaction.service_id".   that should eliminate the error.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/705259fe-7ac2-4905-a88d-ca2af34b2886n%40googlegroups.com.

[sector119]

Thanks!

service = relationship('Service', primaryjoin='and_(Service.schema == Transaction.schema, Service.id == foreign(Transaction.service_id))')

eliminates the error

But why I have no need to do the same with organization = relationship(...) ?

Because Service model references Organization one ?

 

понедельник, 28 декабря 2020 г. в 19:53:24 UTC+2, Mike Bayer:

[Mike Bayer]

well if only one relationship() has the foreign(Transaction.schema), then it doesn't conflict with anything else.

I'd still take it off though for consistency

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/9fd0fb5d-7b60-45d3-ac3a-dca438fd7a8bn%40googlegroups.com.