Many-to-many cascade delete

[Anders Buch]

Hello All,

Suppose I have a table of people, a table of secrets, and a many-to-many association table between them. A person can exist without knowing any secrets, but a secret without any associated people might as well be deleted. My reading of the documentation is that it is not possible to set cascade options to obtain this behavior? (Because a cascade delete option would cause a secret to be deleted if any person forgets about it?) If correct, is there another elegant way to achieve this?

Thanks in advance!

Anders Buch

[Val Huber]

Interesting problem... it's not exactly a cascade delete, which goes from parent (1 side) to children (many side).

I think you have to provide code to do this - brute force check the parent to see if it's an "empty nester".  This can be expensive if you are cascading a person delete to many person-secrets.  You might consider maintaining a count in secret to prune the query.

LogicBank (part of ApiLogicServer - both are open source) can maintain such counts without code, happy to discuss with you if you want to reach out.

Regards,

Val

[Richard Damon]

Fundamentally the cascade applies to doing something to the 'many' side
when something happens on the 'one' side.

A many-to-many association can't do this sort of things, and in fact,
you can't just 'create' a many-to-many relationship in a relational
database. To implement a many-to-many relationship, you need an
intermediary table that is many-to-one with each of the two sides to
build that many-to-many relationship.

You could use a cascade to update this interconnection table, to delete
the cross connection terms when you delete one side of a relationship
(and if you don't you can't delete the items without first removing all
the relationships). By itself, this doesn't help you, but you could
create a trigger on deleting this crossing element, and have it check if
after it is gone if the secret it was connected to is now no longer
referenced, and if so delete the secret.

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/b5e4faee-5cd6-4b9b-936b-0d80458f55e4n%40googlegroups.com
> <https://groups.google.com/d/msgid/sqlalchemy/b5e4faee-5cd6-4b9b-936b-0d80458f55e4n%40googlegroups.com?utm_medium=email&utm_source=footer>.


--
Richard Damon

[Mike Bayer]

The answers regarding "cascade" are correct, however this question has been asked a few times before and we have a recipe on the Wiki that discusses this problem and presents an ORM event oriented solution, which is at https://github.com/sqlalchemy/sqlalchemy/wiki/ManyToManyOrphan , which I put up after answering this question on stackoverflow a few times.   It also seems to state that SQLAlchemy-utils includes such a feature, which is probably based on this recipe, that's at https://sqlalchemy-utils.readthedocs.io/en/latest/listeners.html#many-to-many-orphan-deletion and maybe that works too.

this is kind of old-ish stuff so i haven't tested it recently to see if things are up to date but the basic idea should still be working.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b5e4faee-5cd6-4b9b-936b-0d80458f55e4n%40googlegroups.com.

[Anders Buch]

Thanks a lot, this is what I was hoping for!
Anders

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/t5XRcSJSdw8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/328beb56-2edf-417d-8593-1fb277a81929%40www.fastmail.com.