Restricting a delete based on a many-to-many mapping (Using secondary)

[Torsten Irländer]

Hi,

I have a similar prolem as described in 

https://groups.google.com/forum/#!topic/sqlalchemy/OLeCERDZxyk

I am using the secondary attribute to define the many-to-many relationship between User and Groups.

Now i want prevent to delete a group if a user is still in the group. Setting the Foreign-Key to "nullable=False" as advised in the post above does not work for me on SQLAlchemy level. It does work when trying to delete the Group directly in Postgres (Error ist raised). I am using no cascading deletes in the relation definition.

I assume this has somethong to do with the default behaviour of deleting rows in many-to-many relationsships when using the secondary attribtute as described here:

http://docs.sqlalchemy.org/en/rel_0_9/orm/basic_relationships.html#deleting-rows-from-the-many-to-many-table

So I am wondering if there is a easy way to change this behavior. Or will i need to use an association table?

Torsten

[Michael Bayer]

Torsten Irländer <torsten....@googlemail.com> wrote:

> Hi,
>
> I have a similar prolem as described in
> https://groups.google.com/forum/#!topic/sqlalchemy/OLeCERDZxyk
>
> I am using the secondary attribute to define the many-to-many relationship between User and Groups.
>
> Now i want prevent to delete a group if a user is still in the group. Setting the Foreign-Key to "nullable=False" as advised in the post above does not work for me on SQLAlchemy level. It does work when trying to delete the Group directly in Postgres (Error ist raised). I am using no cascading deletes in the relation definition.

when you say “prevent”, do you mean, session.delete() will silently do nothing, or that an error is raised (or how exactly are these deletes originating)? If the database is set up with constraints as you describe, then it would raise constraint violations. Or are you looking for some error message to be raised sooner?

[Torsten Irländer]

Prevent means raise an error which could be excepted (and optionally analysed) to show the user some feedback that the deletion of the groups can not be done because there are still referencing objects. 

The deletion is done by a session.delete(). 

The constraint seems to work in general as deleting a group on database level doesn't work anymore. But when deleting the group by using session.delete() the entries in the secondary table are automatically removed for the deleted group.

What do you mean by raising an error message sooner? 

[Michael Bayer]

Torsten Irländer <torsten....@googlemail.com> wrote:

>
>
> Am Donnerstag, 12. Februar 2015 18:57:43 UTC+1 schrieb Michael Bayer:
>
>
> Torsten Irländer <torsten....@googlemail.com> wrote:
>
> > Hi,
> >
> > I have a similar prolem as described in
> > https://groups.google.com/forum/#!topic/sqlalchemy/OLeCERDZxyk
> >
> > I am using the secondary attribute to define the many-to-many relationship between User and Groups.
> >
> > Now i want prevent to delete a group if a user is still in the group. Setting the Foreign-Key to "nullable=False" as advised in the post above does not work for me on SQLAlchemy level. It does work when trying to delete the Group directly in Postgres (Error ist raised). I am using no cascading deletes in the relation definition.
>
> when you say “prevent”, do you mean, session.delete() will silently do nothing, or that an error is raised (or how exactly are these deletes originating)? If the database is set up with constraints as you describe, then it would raise constraint violations. Or are you looking for some error message to be raised sooner?
>
> Prevent means raise an error which could be excepted (and optionally analysed) to show the user some feedback that the deletion of the groups can not be done because there are still referencing objects.
> The deletion is done by a session.delete().

What’s wrong with the IntegrityException here? Not specific enough? I’d recommend parsing the string message within it to determine its cause - this is a feasible approach that we use widely with openstack. The exceptions that relate to this particular violation will be predictable. The database does a great job of catching this error, so efforts for SQLAlchemy to do the same thing would be redundant.

[Torsten Irländer]

Nothing is wrong with the IntegrityException if this Exception is actually raised :) That is currently my problem: I except such an exception but it isn't raised. Instead the group is deleted (including all entries for this group in the secondary table) although still having users referencing the group. 

[Michael Bayer]

Torsten Irländer <torsten....@googlemail.com> wrote:

>
> Nothing is wrong with the IntegrityException if this Exception is actually raised :) That is currently my problem: I except such an exception but it isn't raised. Instead the group is deleted (including all entries for this group in the secondary table) although still having users referencing the group.

I made an answer for something like this over here: http://stackoverflow.com/questions/9234082/setting-delete-orphan-on-sqlalchemy-relationship-causes-assertionerror-this-att/9264556#9264556, over there they wanted the equivalent of User to be deleted if all the Groups are. There’s no integrity constraint applicable to an association table so you’d need to catch this in Python. If these are Session.delete() operations, then you’d need to check the object and assert that its user collection is empty within a before_flush() event.




>
>
> --
> 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 post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Torsten Irländer]

Am Donnerstag, 12. Februar 2015 22:16:37 UTC+1 schrieb Michael Bayer:

Torsten Irländer <torsten....@googlemail.com> wrote:

>
> Nothing is wrong with the IntegrityException if this Exception is actually raised :) That is currently my problem: I except such an exception but it isn't raised. Instead the group is deleted (including all entries for this group in the secondary table) although still having users referencing the group.

There’s no integrity constraint applicable to an association table so you’d need to catch this in Python.    If these are Session.delete() operations, then you’d need to check the object and assert that its user collection is empty within a before_flush() event.

First, thanks for your fast replies Michael!

Do I understand it correctly that in contrast to plain SQL (Invoking the delete command in e.g psql), there is no way to make the database side constraints in the association table applicable in SQLAlchemy if I configure this table in the relation using the secondary attribute? So even if there is a constraint in the association table (Foreign key must not become null) this is ignored by SQLAlchemy.

I assume that this is because of the unique behavior described here:

http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#deleting-rows-from-the-many-to-many-table

I think this fits good on the most use cases and I am grateful for not keeping an eye on deleting the entries in the association table too. But in my use case (and I think it is not so uncommon) I would like to able to tell SQLAlchemy not to be too clever and not to delete the entries in the association table. This way the database can to its work and check for its integrity, and raising an exception. 

However, solving the problem on python side is an option so thanks for the hint to the before_flush event.

What about using an Association Object as a workaround? Is this also effected from the automatic deletion of the entries in the association table?

Torsten

[Michael Bayer]

Torsten Irländer <torsten....@googlemail.com> wrote:

> First, thanks for your fast replies Michael!
>
> Do I understand it correctly that in contrast to plain SQL (Invoking the delete command in e.g psql), there is no way to make the database side constraints in the association table applicable in SQLAlchemy if I configure this table in the relation using the secondary attribute? So even if there is a constraint in the association table (Foreign key must not become null) this is ignored by SQLAlchemy.

SQLAlchemy is not capable of “ignoring” a constraint. For the use case of many-to-many relationships, if the relationship is established in the direction from the object being deleted towards the dependent rows, it will emit a DELETE for those rows in the association table before deleting the object itself. So there is no constraint violation.

The relationship will only take steps to alter the database if it’s not a “view only” relationship. If you put viewonly=True on the side that you don’t want this deletion to occur (referring to relationships/backrefs here; it makes a difference which side you put it on), you’ll get the integrity violation when the object on the local side of that relationship is deleted, if remote rows exist.

There is also an option “passive_deletes” which supports the setting “all”, indicating that the remote objects should never be nulled out, however this feature only applies to one-to-many/many-to-one right now. It could be made to support the “secondary” use case as well, in that it would prevent the UOW from ever deleting any rows in the association table, but this feature is not implemented right now.

If viewonly is not an option, then I’d recommend using events to check for this condition.