On Monday, July 30, 2012 4:11:27 PM UTC-7, Michael Bayer wrote:
>
>
> On Jul 30, 2012, at 6:28 PM, kris wrote:
>
> >
> > After checking the docs, I am not exactly sure how to configure (if
> possible) a type such that SA will handle
> > the deletes if underlying database does not support CASCADE.
> >
> > 1. Is it now expected that all DBs support ondelete="CASCADE" and you
> are expected to use it?
>
> There's no expectation that the DB supports ondelete="CASCADE", while my
> life might have been easier for me to just do it that way, when I wrote
> SQLAlchemy I was deeply aware that lots of DBs don't do it (SQLite without
> FKs, MySQL without InnoDB) or only do it partially (oracle), so SQLAlchemy
> ORM can cascade deletes also, but in a much less efficient way. If
> cascade="all, delete" it will load in unloaded collections when the parent
> is deleted, then do a delete() on each child item. When that occurs, if
> that child item also has cascade="all, delete" on its own relationships,
> the process of "cascading the delete" continues. But you can see we're
> needing to load all objects in before we can delete them, and individually
> deleting each row.
>
> > 2. Should ORM relations with cascade="all, delete" also
> passive_deletes=True?
>
> only *if* you have a working "ON DELETE CASCADE" set up on your foreign
> keys. Otherwise when you delete a parent row, if the dependent rows
> aren't loaded into memory, they won't be deleted. Your database will
> either A. complain, if it enforces referential integrity or B. silently
> leave those rows in place.
>
>
So
3. Is there a fool-proof way to configure your table and mapper such that
you will get the most efficient delete strategy?
ON DELETE CASCADE when available or SA when not?
4. Can I always add ondelete='cascade' and it will be ignored (or
filtered) if the underying DB doesn't support it.
5. Add some sort of configuration setting
passive_deletes=( not db_sucks)?
Thx,
Kris
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/JHPfrUci85EJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
