list reference with ondelete=set null breaking when delete parent

[André Kablu]

I have 2 tables, using sqlite:

db.define_table('color',

                       Field('color', 'string'))

db.define_table('tshirt',

                       Field('name', 'string'),

                       Field('colors', 'list:reference color', ondelete="SET NULL"))

When I delete a color, all tshirts that have this color will break with the error message:

"using a recursive select but encountered a broken reference"

So the delete() is not putting NULL on the reference...

This is a bug right? Not the correct behavior...

I was using list:reference b/c it seems much easier than building 3 tables... but now I am not sure if it is good to be used once it is not "automatic"...

[Leonel Câmara]

I think you should have a third table anyway or searches for T-shirts of a given colour will be quite slow.

[Kablu®]

I will migrate it later to mongodb, but wanted it to work on sqlite too for dev, and not have to create a lot of validators on each delete or update.

That`s why I was wondering if web2py can handle this transparently.

On Thu, Mar 20, 2014 at 4:07 PM, Leonel Câmara <leonel...@gmail.com> wrote:

I think you should have a third table anyway or searches for T-shirts of a given colour will be quite slow.

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/0tVp5QVR1-8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[André Kablu]

Yes just migrated to mongodb, and there really exist some issue.

using CASCADE or SET NULL, it does not matter, web2py does not respect and remove the reference from the referencing collection or delete the reference...

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[Francisco Costa]

Hi André, did you find a way to solve this?

To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

[Ron Chatterjee]

Wonder if he can try to make it a list and do list.remove?

Have you tried that?

[Paolo Valleri]

ondelete is for reference field.

At a first sight, a possible workaround would be to write your own 'ondelete' by using the _before_delete and _after_delete callbacks

Paolo

[Francisco Costa]

Hi Paolo, but how do you find the tables (and records) that use the deleted record as a Foreign Key?

I don't know if those records should have the "is_active" set to False, or the Foreign Key set to None