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"...

[PN]

Your desired behavior is that when a color is deleted it should be set to null in tshirts, so you need to put the ondelete attribute in the color field in the color table instead of on the colors field in the tshirt table.

db.define_table('color',

                       Field('color', 'string', ondelete="SET NULL"))

db.define_table('tshirt',

                       Field('name', 'string'),

                       Field('colors', 'list:reference color'))

[Kablu®]

I think you are mistaken...

ondelete is for use with upload and reference fields...

http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Field-constructor

--
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/TycU_Q-ywYI/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.

[PN]

You're right, the update is defined on the field like you have. I have used 'NO ACTION' on a reference field with no issues. However never tried it 'SET NULL' on a List:reference. I spoke too soon.

[Niphlod]

you're all forgetting that:
- relational database do have FK working
- sqlite is not properly relational in matters of FK, unless you're working with a recent app (foreign key support was introduced by default in a recent release) and a fresh database
- list:reference is not something baked in the backend: it's a special representation that web2py and only web2py uses: web2py can't take care of pruning all references to the "color" going thoughout all your tshirt records every time you delete a color.