[Python] Ancestor foreign keys are not enforced in Oracle DB when inserting data using...

I have created the SQLAlchemy data model:

class AncestorTable(Base):
__tablename__ = 'ancestor'

id: Mapped[int] = mapped_column(primary_key=True)
some_value: Mapped[float]

parent: Mapped['ParentTable'] = relationship()

class ParentTable(Base):
__tablename__ = 'parent'

id: Mapped[int] = mapped_column(primary_key=True)
ancestor_id: Mapped[int] = mapped_column(ForeignKey('ancestor.id'))
some_value: Mapped[float]

tables: Mapped[List['Table']] = relationship()

class Table(Base):
__tablename__ = 'table'

id: Mapped[int] = mapped_column(primary_key=True)
ancestor_id: Mapped[int] = mapped_column(ForeignKey('ancestor.id'))
parent_id: Mapped[int] = mapped_column(ForeignKey('parent.id'))
some_value: Mapped[float]


These objects are being inserted to Oracle tables with auto-increment id columns:

with Session(engine) as session:
session.add_all([responses])
session.commit()


The process of inserting data into the ancestor and parent tables is functioning correctly, with IDs being generated as expected. Additionally, the ancestor_id foreign key in the parent table is properly enforced. However, an issue arises with the final table: the ancestor_id foreign key constraint is not being enforced. Overall, while the foreign key constraints for parent relationships are consistently applied, those for ancestor relationships are not.

ID ANCESTOR_ID PARENT_ID VALUE
1 (null) 1 21
2 (null) 1 37

I have attempted to generate the sequence manually and also defined autoincrement directly in the Column clauses, but neither approach was successful.

Continue reading...