CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
duck
duck

Reputation: 130

Foreign key violation when adding child class to session in SqlAlchemy

I have the current situation:

Base = declarative_base()

class Parent(Base):
    __tablename__ = 'parent'

    id = Column(String(32), primary_key=True, index=True)


class Child(Parent):
    __tablename__ = 'child'

    parent_id = Column(ForeignKey('parent.id'), primary_key=True)
    other_field = Column(String(32))


class Other(Base):
    __tablename__ = 'other'

    id = Column(String(32), primary_key=True, index=True)
    reference_to_parent = Column(ForeignKey('parent.id'), primary_key=True)


child = Child(id='some_id', other_field="blah")
session.add(child)

other = Other(id="some_other_id", reference_to_parent='some_id')
session.add(other)
session.commit()  # <-- sqlalchemy.exc.IntegrityError

On commit, I get the error:

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) insert or update on table "other" violates foreign key constraint
DETAIL:  Key (reference_to_parent)=(some_id) is not present in table "parent".

However, if I do:

child = Child(id='id', other_field="blah")
session.add(child)
session.commit() . # <-- note the extra commit

other = Other(reference_to_parent='id')
session.add(other)
session.commit()

I get no such error. It seems that SQLAlchemy doesn't recognise that the child I have added to the session is in fact an instance of Parent and will create a row in that table.

Any idea what I'm doing wrong? It doesn't feel like the commit should be needed.

Upvotes: 2

Views: 2250

Answers (1)

Gophermofur
Gophermofur

Reputation: 2101

I think you need add a session.flush() after the first add. Flush will basically communicate your changes to the database in a pending state. Commit will actually write them to the database. 

child = Child(id='some_id', other_field="blah")
session.add(child)
session.flush()

other = Other(id="some_other_id", reference_to_parent='some_id')
session.add(other)
session.commit()

Upvotes: 5

Related Questions