Are foreign keys required when using SQLAlchemy relationships

user_1309443 0
Viewed 611

Reading the following pages, I can see that I can setup relationships between tables using the orm relationship() function.

https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html

https://docs.sqlalchemy.org/en/13/orm/backref.html#relationships-backref

Using this example:

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    addresses = relationship("Address", backref="user")

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    email = Column(String)
    user_id = Column(Integer, ForeignKey('user.id'))

Do I need to have an explicit user_id = Column(Integer, ForeignKey('user.id')) statement for the Address class?

Wont the relationship(backref=...) is User provide an Address.user attribute?

If the ForeignKey column is needed in Address, then why isn't it needed in User?

sqlalchemy
1 Answers

First: You use foreign key in three circumstances:

  • You have a multi-key reference to another table.
  • You want to name the foreign key reference.
  • You want to add additional features, such as cascading deletes.

A fourth reason is also plausible: because local coding standards require the use of an explicit constraint.

Second: According to SQLAchemy documents

In fact, the relationship.backref keyword is only a common shortcut for placing a second relationship() onto the Address mapping, including the establishment of an event listener on both sides which will mirror attribute operations in both directions.

but it's better use back_populates because is explicitly. we add a .user relationship to Address explicitly. On both relationships, the relationship.back_populates directive tells each relationship about the other one, indicating that they should establish “bidirectional” behavior between each other.

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    addresses = relationship("Address", back_populates="user")

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    email = Column(String)
    user_id = Column(Integer, ForeignKey('user.id'))

    user = relationship("User", back_populates="addresses")
user_46537940
Related