How to create one-to-one relationships with declarative

Question

I have two tables, foo and bar, and I want foo.bar_id to link to bar. The catch is that this is a one-way one-to-one relationship. bar must not know anything about foo. For every foo, there will be one and only one bar.

Ideally, after selecting a foo, I could do something like this:

myfoo.bar.whatever = 5 

How to accomplish this?

Diva

Answer 1

If you want a true one-to-one relationship, you also have to use the "uselist=False" in your relationship definition.

bar_id = Column(Integer, ForeignKey(Bar.id))
bar = relationship(Bar, uselist=False)

Twill

This is not exactly right, because the uselist keyword has no impact if the FK of the join is in the same class than the relationship definition. See the answer of chadwick.boulay for the correct solution. – Mir

Answer 2

The documentation explains this nicely:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child = relationship("Child", uselist=False, backref="parent")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

OR

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'))
    child = relationship("Child", backref=backref("parent", uselist=False))

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)

Note: The API and associated documentation has changed considerably since the answer was written. New docs

Ptomaine

Those docs are like the least nicely written docs I've seen. They don't look anything like what you wrote! Yours makes way more sense. They used that strange mapped syntax and mapped_columns. I don't even know what those are lmao. Seems complicated. – Exegetics

@Zack, Thanks for letting me know the link to the docs is expired. I linked directly to older docs and provided a separate link to the latest. By the way, if you aren't familiar already, the code from the : and before the = is a type-hint. This is typically non-functional, but it looks like SQLAlchemy is using it to infer relationships. – Ptomaine

@ZackPlauché the new syntax might look strange at first but it’s a lot easier to work with because MyPy / your IDE now knows which types it should expect from each column. – Madelyn

@Ptomaine I'm familiar with type hints but "mapped" is so strange to me lol. Like vs Relationship or something. – Exegetics

Answer 3

I think if it is a truly one to one relationship we should add a uniqueness constraint to foreign key so another parent can not have other parent child!! Like this:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'), unique=True)
    child = relationship("Child", backref=backref("parent", uselist=False))

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)

Animated

Answer 4

It turns out this is actually quite easy. In your Foo model:

bar_id = Column(Integer, ForeignKey(Bar.id))
bar = relationship(Bar)

Diva

Answer 5

Other answers using uselist=False are correct, but in SQLAlchemy 2.0 relationship is now smart enough to deduce it if your Mapped annotation uses a non-collection type.

From the docs:

New in version 2.0: The relationship() construct can derive the effective value of the relationship.uselist parameter from a given Mapped annotation.

Here is chadwick.boulay’s code modified for SqlAlchemy 2.0:

class Parent(Base):
    __tablename__ = 'parent'
    id: Mapped[int] = mapped_column(Integer(), primary_key=True)
    child: Mapped["Child"] = relationship("Child", backref="parent")

class Child(Base):
    __tablename__ = 'child'
    id: Mapped[int] = mapped_column(Integer(), primary_key=True)
    parent_id: Mapped[int] = mapped_column(Integer(), ForeignKey('parent.id'))

Notice how child is annotated with Mapped["Child"], i.e. child is one Child. If you wanted to use a one-to-many relationship, you would annotate it as a list:

children: Mapped[List["Child"]] = relationship("Child", backref="parent")

Madelyn

For those looking at this solution with SQLAlchemy 2, be sure to check the docs (docs.sqlalchemy.org/en/20/orm/…) as you should almost always also set a database-level constraint to enforce that only one Child row may refer to a particular Parent row at a time. – Parma