3. Interview Questions- Relationship Configuration

Source: https://docs.sqlalchemy.org/en/20/orm/relationships.html

1. What is the `relationship()` function used for in SQLAlchemy?

Answer: The relationship() function defines how two ORM-mapped classes are related (e.g., one-to-many, many-to-one, or many-to-many).

2. How is a one-to-many relationship declared in SQLAlchemy ORM?

Answer: Use relationship() on the parent and ForeignKey() in the child:

class User(Base):
    addresses: Mapped[List["Address"]] = relationship("Address", back_populates="user")

class Address(Base):
    user_id: Mapped[int] = mapped_column(ForeignKey("user.id"))
    user: Mapped["User"] = relationship("User", back_populates="addresses")

3. What is the purpose of `back_populates` in a relationship?

Answer: It links both sides of a bidirectional relationship so changes on one side affect the other.

4. Can you use `backref` instead of `back_populates`?

Answer: Yes, backref creates both sides of the relationship automatically, while back_populates requires explicit declarations on both sides.

5. What is the default collection type for one-to-many relationships?

Answer: Python’s built-in list.

6. How do you define a many-to-one relationship?

Answer: Use ForeignKey() in the child and relationship() pointing to the parent class.

7. How do you define a many-to-many relationship in SQLAlchemy?

Answer: Use an association table (no mapped class) with two foreign keys and relationship(..., secondary=association_table).

8. What argument is used in `relationship()` to define a many-to-many join table?

Answer: secondary

posts = relationship("Post", secondary=post_tags, back_populates="tags")

9. What is the difference between `uselist=True` and `uselist=False`?

Answer: uselist=False makes the relationship scalar (single object), while uselist=True (default) makes it a list-like collection.

10. What does `lazy="select"` mean in a relationship?

Answer: It is the default loading strategy — related data is loaded in a separate SELECT query when the relationship is accessed.

11. How can you eager load a relationship in SQLAlchemy?

Answer: By setting lazy="joined" or using selectinload() in a query.

12. What is `cascade="all, delete-orphan"` used for?

Answer: It enables cascading operations (e.g., delete) and ensures that related "orphan" objects are deleted when unlinked.

13. How do you define a one-to-one relationship?

Answer: Set uselist=False on a one-to-many-style relationship, and ensure the foreign key is unique.

14. How does SQLAlchemy detect the direction of relationships?

Answer: Through foreign key constraints and declared relationships with relationship() and ForeignKey().

15. What is a `foreign_keys` argument used for in `relationship()`?

Answer: To manually specify which column(s) act as foreign keys in ambiguous or self-referential relationships.

16. What is an association object pattern in SQLAlchemy?

Answer: Instead of using a plain join table, an association table is mapped as a class to store extra fields on the relationship.

17. Can you create relationships with composite (multi-column) foreign keys?

Answer: Yes, by using ForeignKeyConstraint and providing primaryjoin expressions in the relationship().

18. How do you make a self-referential relationship?

Answer: Use relationship("ClassName", remote_side=[column]) to model trees or graphs.

19. What happens if you omit `back_populates` or `backref` entirely?

Answer: The relationship will be unidirectional — changes on one side won’t be reflected on the other.

20. What is the difference between `joined`, `selectin`, and `subquery` loading strategies?

Answer:

joined: Uses JOIN to fetch related objects.
selectin: Uses separate IN (...) queries.
subquery: Uses a subquery for eager loading.

21. What is the `primaryjoin` argument in `relationship()` used for?

Answer: It defines the exact ON clause for the join between two tables when SQLAlchemy cannot infer it, or when you need custom logic.

22. When should you use the `secondaryjoin` argument in a relationship?

Answer: In many-to-many or complex joins using an association table, secondaryjoin defines how the target table joins from the secondary table.

23. How do you handle a relationship between the same table (e.g., self-referencing)?

Answer: Use relationship("ClassName", remote_side=[column]) to indicate which side is the parent.

24. What does `viewonly=True` do in a relationship?

Answer: It makes the relationship read-only; changes to it won't affect the database during flush or commit.

25. What is an association object and how is it different from an association table?

Answer: An association object is a mapped class that represents the relationship table and allows storing additional fields (like timestamps, permissions), unlike a plain join table.

26. How can you define additional columns on a many-to-many relationship?

Answer: Use an association object instead of just an association table. Define a full class with additional fields.

27. What are the common values for the `cascade` option in a relationship?

Answer:

"save-update"
"merge"
"expunge"
"delete"
"delete-orphan"
"refresh-expire"
"all" (implies all above except "delete-orphan" unless specified)

28. How do you prevent deletion of orphaned child objects?

Answer: Do not use "delete-orphan" in the cascade parameter.

29. What is the default value of `cascade` in a relationship?

Answer: "save-update, merge" — meaning changes to children are automatically tracked and merged.

30. What is the purpose of `enable_typechecks=False` in `relationship()`?

Answer: It disables type checking when assigning related objects — useful in polymorphic or dynamic scenarios.

31. What does `passive_deletes=True` do in a relationship?

Answer: SQLAlchemy will not emit DELETE statements for related objects — it assumes the database handles cascading deletes.

32. When should you use `foreign_keys` in self-referential relationships?

Answer: To resolve ambiguity when both sides of the relationship refer to the same table and column names.

33. How does `lazy='dynamic'` change how relationships behave?

Answer: It returns a query object instead of a collection, allowing filtering and chaining before accessing related records.

34. What does `lazy='selectin'` do?

Answer: Performs a separate SELECT ... WHERE IN (...) to fetch related objects efficiently for many parent rows.

35. What’s the trade-off between `lazy='joined'` and `lazy='selectin'`?

Answer:

joined is faster for single-row loads but causes data duplication on many-to-one.
selectin is efficient for collections when loading multiple parents.

36. What is the purpose of `overlaps` in a relationship?

Answer: It prevents circular loading issues or warnings when multiple relationships share the same foreign keys or attributes.

37. How can you filter a relationship automatically (e.g., only active items)?

Answer: Use the primaryjoin argument or a with_loader_criteria() query modifier to enforce a condition.

38. What happens when you assign a list to a `uselist=False` relationship?

Answer: SQLAlchemy will raise an error, as uselist=False expects a scalar, not a list.

39. What is `collection_class` used for in relationships?

Answer: To customize the type of collection used in the relationship (e.g., set, dict, custom class).

40. Can a `relationship()` be defined on both sides of a many-to-many association?

Answer: Yes, and it is common to use back_populates or backref to link them.

41. Can you use a `relationship()` without a `ForeignKey`?

Answer: Yes, but it requires manual specification of primaryjoin (and foreign_keys if ambiguous). Without a ForeignKey, SQLAlchemy cannot auto-infer the relationship.

42. What does `compare_to` do in custom collection classes for relationships?

Answer: It defines how objects are compared for identity in a collection. This is part of custom collection classes used in collection_class.

43. What is the purpose of `remote_side` in relationships?

Answer: It disambiguates self-referential relationships by telling SQLAlchemy which side is considered “remote” (i.e., the parent in the hierarchy).

44. How do `selectinload()` and `joinedload()` differ in behavior for collections?

Answer:

selectinload() issues separate SELECT ... WHERE IN (...) for efficiency in loading multiple parents with children.
joinedload() performs a JOIN and may result in data duplication when parent has many children.

45. Can `relationship()` be used on columns with composite foreign keys?

Answer: Yes, but you must use ForeignKeyConstraint and explicitly define primaryjoin if needed.

46. What happens if a child object is assigned to multiple parents in a one-to-many relationship?

Answer: SQLAlchemy updates the foreign key in the child to point to the new parent and removes it from the original parent's collection, if the session is aware.

47. How does SQLAlchemy handle circular dependencies in relationships?

Answer: It delays loading via lazy="select" and may require overlaps or foreign_keys to break ambiguity and avoid recursion issues.

Answer: Use cascade="all, delete-orphan" in the relationship() definition.

49. Is it possible to override a relationship dynamically at runtime?

Answer: Yes, but it is discouraged. Relationships are normally static. If needed, dynamic behavior should be handled via custom query methods or attributes.

50. What is `active_history=True` used for in a relationship?

Answer: It forces SQLAlchemy to track changes even if the new value is the same as the current one — useful for event listening or change detection.

Previous2. Interview Questions- ORM Mapped Class Configuration Next4. Interview Questions- ORM Querying Guide

Last updated 8 months ago

hashtag1. What is the relationship() function used for in SQLAlchemy?

hashtag2. How is a one-to-many relationship declared in SQLAlchemy ORM?

hashtag3. What is the purpose of back_populates in a relationship?

hashtag4. Can you use backref instead of back_populates?

hashtag5. What is the default collection type for one-to-many relationships?

hashtag6. How do you define a many-to-one relationship?

hashtag7. How do you define a many-to-many relationship in SQLAlchemy?

hashtag8. What argument is used in relationship() to define a many-to-many join table?

hashtag9. What is the difference between uselist=True and uselist=False?

hashtag10. What does lazy="select" mean in a relationship?

hashtag11. How can you eager load a relationship in SQLAlchemy?

hashtag12. What is cascade="all, delete-orphan" used for?

hashtag13. How do you define a one-to-one relationship?

hashtag14. How does SQLAlchemy detect the direction of relationships?

hashtag15. What is a foreign_keys argument used for in relationship()?

hashtag16. What is an association object pattern in SQLAlchemy?

hashtag17. Can you create relationships with composite (multi-column) foreign keys?

hashtag18. How do you make a self-referential relationship?

hashtag19. What happens if you omit back_populates or backref entirely?

hashtag20. What is the difference between joined, selectin, and subquery loading strategies?

hashtag21. What is the primaryjoin argument in relationship() used for?

hashtag22. When should you use the secondaryjoin argument in a relationship?

hashtag23. How do you handle a relationship between the same table (e.g., self-referencing)?

hashtag24. What does viewonly=True do in a relationship?

hashtag25. What is an association object and how is it different from an association table?

hashtag26. How can you define additional columns on a many-to-many relationship?

hashtag27. What are the common values for the cascade option in a relationship?

hashtag28. How do you prevent deletion of orphaned child objects?

hashtag29. What is the default value of cascade in a relationship?

hashtag30. What is the purpose of enable_typechecks=False in relationship()?

hashtag31. What does passive_deletes=True do in a relationship?

hashtag32. When should you use foreign_keys in self-referential relationships?

hashtag33. How does lazy='dynamic' change how relationships behave?

hashtag34. What does lazy='selectin' do?

hashtag35. What’s the trade-off between lazy='joined' and lazy='selectin'?

hashtag36. What is the purpose of overlaps in a relationship?

hashtag37. How can you filter a relationship automatically (e.g., only active items)?

hashtag38. What happens when you assign a list to a uselist=False relationship?

hashtag39. What is collection_class used for in relationships?

hashtag40. Can a relationship() be defined on both sides of a many-to-many association?

hashtag41. Can you use a relationship() without a ForeignKey?

hashtag42. What does compare_to do in custom collection classes for relationships?

hashtag43. What is the purpose of remote_side in relationships?

hashtag44. How do selectinload() and joinedload() differ in behavior for collections?

hashtag45. Can relationship() be used on columns with composite foreign keys?

hashtag46. What happens if a child object is assigned to multiple parents in a one-to-many relationship?

hashtag47. How does SQLAlchemy handle circular dependencies in relationships?

hashtag48. How can you make sure orphaned related objects are deleted automatically?

hashtag49. Is it possible to override a relationship dynamically at runtime?

hashtag50. What is active_history=True used for in a relationship?

1. What is the `relationship()` function used for in SQLAlchemy?

2. How is a one-to-many relationship declared in SQLAlchemy ORM?

3. What is the purpose of `back_populates` in a relationship?

4. Can you use `backref` instead of `back_populates`?

5. What is the default collection type for one-to-many relationships?

6. How do you define a many-to-one relationship?

7. How do you define a many-to-many relationship in SQLAlchemy?

8. What argument is used in `relationship()` to define a many-to-many join table?

9. What is the difference between `uselist=True` and `uselist=False`?

10. What does `lazy="select"` mean in a relationship?

11. How can you eager load a relationship in SQLAlchemy?

12. What is `cascade="all, delete-orphan"` used for?

13. How do you define a one-to-one relationship?

14. How does SQLAlchemy detect the direction of relationships?

15. What is a `foreign_keys` argument used for in `relationship()`?

16. What is an association object pattern in SQLAlchemy?

17. Can you create relationships with composite (multi-column) foreign keys?

18. How do you make a self-referential relationship?

19. What happens if you omit `back_populates` or `backref` entirely?

20. What is the difference between `joined`, `selectin`, and `subquery` loading strategies?

21. What is the `primaryjoin` argument in `relationship()` used for?

22. When should you use the `secondaryjoin` argument in a relationship?

23. How do you handle a relationship between the same table (e.g., self-referencing)?

24. What does `viewonly=True` do in a relationship?

25. What is an association object and how is it different from an association table?

26. How can you define additional columns on a many-to-many relationship?

27. What are the common values for the `cascade` option in a relationship?

28. How do you prevent deletion of orphaned child objects?

29. What is the default value of `cascade` in a relationship?

30. What is the purpose of `enable_typechecks=False` in `relationship()`?

31. What does `passive_deletes=True` do in a relationship?

32. When should you use `foreign_keys` in self-referential relationships?

33. How does `lazy='dynamic'` change how relationships behave?

34. What does `lazy='selectin'` do?

35. What’s the trade-off between `lazy='joined'` and `lazy='selectin'`?

36. What is the purpose of `overlaps` in a relationship?

37. How can you filter a relationship automatically (e.g., only active items)?

38. What happens when you assign a list to a `uselist=False` relationship?

39. What is `collection_class` used for in relationships?

40. Can a `relationship()` be defined on both sides of a many-to-many association?

41. Can you use a `relationship()` without a `ForeignKey`?

42. What does `compare_to` do in custom collection classes for relationships?

43. What is the purpose of `remote_side` in relationships?

44. How do `selectinload()` and `joinedload()` differ in behavior for collections?

45. Can `relationship()` be used on columns with composite foreign keys?

46. What happens if a child object is assigned to multiple parents in a one-to-many relationship?

47. How does SQLAlchemy handle circular dependencies in relationships?

48. How can you make sure orphaned related objects are deleted automatically?

49. Is it possible to override a relationship dynamically at runtime?

50. What is `active_history=True` used for in a relationship?