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?

score 147 · Answer 1 · edited Apr 12 '19 at 13:20

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)

score 56 · Accepted Answer · answered Dec 01 '10 at 13:30

56

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)

answered Dec 01 '10 at 13:30

Brett Bim

3,190
4
28
26

12

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](http://stackoverflow.com/a/9611874/851737) for the correct solution. – schlamar Apr 17 '12 at 06:07

score 12 · Answer 3 · answered May 12 '17 at 01:13

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)

score 3 · Answer 4 · answered Aug 12 '10 at 05:57

3

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

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

answered Aug 12 '10 at 05:57

carl

49,756
17
74
82

score 1 · Answer 5 · answered Apr 04 '23 at 13:57

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

How to create one-to-one relationships with declarative

5 Answers5

Linked

Related