Alembic does not add Cascade information in autogenerate?

Question

Here is my Budget schema

class Budget(db.Model):
    __tablename__ = 'budgets'
    # noinspection PyRedeclaration
    uuid = Column('uuid', GUID(), default=uuid.uuid4, primary_key=True,
                  unique=True)
    user_id = Column(GUID(), ForeignKey('users.uuid'), nullable=False)
    user = relationship('User', backref='budgets')
    created_on = Column('created_on', sa.types.DateTime(timezone=True),
                        nullable=False)

and BudgetCategories

class BudgetCategory(db.Model):
    __tablename__ = 'budget_categories'
    # noinspection PyRedeclaration
    uuid = Column('uuid', GUID(), default=uuid.uuid4, primary_key=True,
                  unique=True)
    budget_id = Column(GUID(), ForeignKey('budgets.uuid'), nullable=False)
    budget = relationship('Budget', backref='budgetCategories',
                          cascade="all, delete-orphan", single_parent=True)
    category = Column('category', sa.types.String, nullable=True)
    parent_category = Column('parent_category', sa.types.String, nullable=True)
    amount = Column('amount', Numeric(10, 2), nullable=False)
    recurring = Column('recurring', sa.types.Boolean,
                       nullable=False)
    created_on = Column('created_on', sa.types.DateTime(timezone=True),
                        nullable=False)

when I generate alembic migration script I do

 alembic revision --autogenerate -m 'add_budgetCategories_Jan252014'

and I get

def upgrade():
### commands auto generated by Alembic - please adjust! ###
    op.create_table('budgets',
    sa.Column('uuid', UUID(), nullable=False),
    sa.Column('user_id', UUID(), nullable=False),
    sa.Column('created_on', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['user_id'], ['users.uuid'], ),
    sa.PrimaryKeyConstraint('uuid'),
    sa.UniqueConstraint('uuid')
    )
    ### end Alembic commands ###

Then I do

 alembic revision --autogenerate -m 'add_budgetCategories_Jan252014'

and I get

def upgrade():
### commands auto generated by Alembic - please adjust! ###
    op.create_table('budget_categories',
    sa.Column('uuid', sa.GUID(), nullable=False),
    sa.Column('budget_id', sa.GUID(), nullable=False),
    sa.Column('category', sa.String(), nullable=True),
    sa.Column('parent_category', sa.String(), nullable=True),
    sa.Column('amount', sa.Numeric(precision=10, scale=2), nullable=False),
    sa.Column('recurring', sa.Boolean(), nullable=False),
    sa.Column('created_on', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['budget_id'], ['budgets.uuid'], ),
    sa.PrimaryKeyConstraint('uuid'),
    sa.UniqueConstraint('uuid')
    )
    ### end Alembic commands ###

Question

Why did alembic not generate syntax for ON CASCADE DELETE? I guess I am missing things, but not sure what? Can someone please help here?

Answer 1

the ON DELETE CASCADE syntax needs to be explicitly configured when you create a ForeignKey or ForeignKeyConstraint:

ForeignKey("foo.id", ondelete="CASCADE")

these settings are not directly linked to the fact that you used "cascade='all, delete-orphan'" on a relationship(), and in any case you have that cascade setting on the "many-to-one" side which doesn't even relate to anything in a FOREIGN KEY. To have the relationship cascade work in tandem with ON DELETE CASCADE it needs to be on the one-to-many side.

Relevant docs:

http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html#on-update-and-on-delete

http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#unitofwork-cascades

http://docs.sqlalchemy.org/en/rel_0_9/orm/collections.html#using-passive-deletes