CODEWITHSUNDEEP
sqlalchemy
carl
carl

Reputation: 50544

How to create one-to-one relationships with declarative

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?

Upvotes: 70

Views: 73367

Answers (5)

chadwick.boulay
chadwick.boulay

Reputation: 1814

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

Upvotes: 150

bfontaine
bfontaine

Reputation: 19817

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

Upvotes: 3

hamidfzm
hamidfzm

Reputation: 4685

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)

Upvotes: 12

Brett Bim
Brett Bim

Reputation: 3308

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)

Upvotes: 60

carl
carl

Reputation: 50544

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

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

Upvotes: 3

Related Questions