foreign keys with cascade delete not correctly generatedΒ #387
Description
Things to check first
-
I have searched the existing issues and didn't find my bug already reported there
-
I have checked that my bug is still present in the latest release
Sqlacodegen version
3.0.0
SQLAlchemy version
2.0.35
RDBMS vendor
PostgreSQL
What happened?
I have a table that references another with a foreign key. The foreign key is set to ON CASCADE DELETE
When attempting to delete a parent entity with sqlalchemy e.g.
item = session.get(id)
session.delete(item)
session.commit()
I get an error
sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "work_item_id" of relation "work_item_vote" violates not-null constraint
DETAIL: Failing row contains (04a2a98c-7310-4c3f-9608-389c1ed73098, null, [email protected]).
[SQL: UPDATE work_item_vote SET item_id=%(item_id)s::UUID WHERE item_vote.item_vote_id = %(item_vote_item_vote_id)s::UUID]
[parameters: {'item_id': None, 'item_vote_item_vote_id': UUID('04a2a98c-7310-4c3f-9608-389c1ed73098')}]
where SQLAlchemy is attempting to update the child row instead of deleting it
The generated code I get for the tables are
class Item(Base):
__tablename__ = "item"
__table_args__ = (PrimaryKeyConstraint("item_id", name="item_pkey"),)
item_id: Mapped[uuid.UUID] = mapped_column(
Uuid, primary_key=True, server_default=text("uuid_generate_v4()")
)
title: Mapped[str] = mapped_column(String)
status: Mapped[str] = mapped_column(String)
created_at: Mapped[datetime.datetime] = mapped_column(DateTime)
last_updated_at: Mapped[datetime.datetime] = mapped_column(DateTime)
description: Mapped[Optional[str]] = mapped_column(String)
item_vote: Mapped[List["ItemVote"]] = relationship(
"ItemVote", back_populates="item"
)
class ItemVote(Base):
__tablename__ = "item_vote"
__table_args__ = (
ForeignKeyConstraint(
["item_id"],
["item.item_id"],
ondelete="CASCADE",
name="item_vote_item_id_fkey",
),
PrimaryKeyConstraint("item_vote_id", name="item_vote_pkey"),
)
item_vote_id: Mapped[uuid.UUID] = mapped_column(
Uuid, primary_key=True, server_default=text("uuid_generate_v4()")
)
item_id: Mapped[uuid.UUID] = mapped_column(Uuid)
user_id: Mapped[str] = mapped_column(String)
item: Mapped["Item"] = relationship(
"Item", back_populates="item_vote"
)
the generated model for Item seems to be missing cascade='all, delete' or passive_deletes
Database schema for reproducing the bug
CREATE TABLE item (
item_id uuid DEFAULT uuid_generate_v4() NOT NULL,
title varchar NOT NULL,
description varchar NULL,
status varchar NOT NULL,
created_at timestamp NOT NULL,
last_updated_at timestamp NOT NULL,
CONSTRAINT item_pkey PRIMARY KEY (item_id)
);
CREATE TABLE item_vote (
item_vote_id uuid DEFAULT uuid_generate_v4() NOT NULL,
item_id uuid NOT NULL,
user_id varchar NOT NULL,
CONSTRAINT item_vote_pkey PRIMARY KEY (item_vote_id),
CONSTRAINT item_vote_item_id_fkey FOREIGN KEY (item_id) REFERENCES item(item_id) ON DELETE CASCADE
);