CODEWITHSUNDEEP
pythonsqlpostgresqlsqlalchemy
user2430771
user2430771

Reputation: 1422

SQLAlchemy one-to-one relationship creates multiple rows

I'm trying to make a one-to-one relationship between two tables in my database(postgresql). I'm using SQLAlchemy in python. So, I used an example given in the documentation itself. one-to-one relationship

from sqlalchemy import Column, ForeignKey, Integer, String, Date, Float
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

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

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

engine = create_engine('postgresql+psycopg2://testdb:hello@localhost/fullstack')
Base.metadata.create_all(engine)

This creates two tables parent and child. Now I insert values in parent table and child table

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from test_databasesetup import Base, Parent, Child

engine = create_engine('postgresql+psycopg2://testdb:hello@localhost/fullstack')
Base.metadata.bind = engine
DBSession = sessionmaker(bind=engine)
session = DBSession()

parent = Parent()
session.add(parent)
session.commit() // insert in 'parent' table with id=1
// insert into child
child = Child(parent_id=1)
session.add(child)
session.commit()

child = Child(parent_id=1)
session.add(child)
session.commit()

Inserting child again with the same parent_id should have thrown an error but the record got inserted.

id | parent_id 
----+-----------
  1 |         1
  2 |         1

What should be done here so that I can only insert one child corresponding to a parent id. I don't want children having the same parent_id.

Thanks.

Upvotes: 4

Views: 3296

Answers (1)

van
van

Reputation: 76992

The problem is that you are specifying the field parent_id directly. In this case sqlalchemy does not get a chance to validate the relationship to be one-to-one. Instead, use the relationship:

# add new parent to the database
parent = Parent()
session.add(parent)
session.commit()

# insert new child for this parent
child = Child(parent=parent)  # @note: this is the code change. \
# Here we refer to parent, not parent_id field
session.add(child)
session.commit()

# insert another child for the same parent:
# this will assign this new child to the parent, and will 
# *remove* previous child from this parent
child = Child(parent=parent)
session.add(child)
session.commit()

Another side-effect is much cleaner code. Yet another one is the fact that sqlalchemy can figure out the foreign keys itself, and you do not need to know the id of the object:

parent = Parent()
child = Child(parent=parent)
# it is enough to add only one related object, and the other (Child) will be added to session automatically
session.add(parent)
# we can commit only now, when we finished working on all the objects in the current session/transaction
session.commit()

In addition, you can add a unique constraint to the Child.parent_id field as an additional database level check, which would have thrown an error in your case:

parent_id = Column(Integer, ForeignKey('parent.id'), unique=True)

Upvotes: 5

Related Questions