[web2py] Question about ondelete='CASCADE'

[Richard]

Hello,

My app raise issue when someone delete a record referenced by a other table. I this table I set for the reference field ondelete='NO ACTION', but as far as I understand it not solve noting since table1 (the referenced table) will not look at table2 definition to know what todo on delete.

What should I do to make sure my app not raise issue on delete of a record if the record someone try to delete could not be deleted because it is referenced by an other table?

Do I have to write my own function and trigger it with ondelete option of crud.update or SQLFORM?

Thanks

Richard

[Niphlod]

we should see the model and the traceback. Some db engines are more permissive than others. Often the cause of the issue is some sort of "unique" or "notnull" costraint set on the referenced table.

[Richard Vézina]

Here the error

<class 'psycopg2.IntegrityError'> ERREUR: UPDATE ou DELETE sur la table « table2 » viole la contrainte de clé étrangère « table3_field2t3_fkey » de la table « table3 » DETAIL: La clé (id)=(1) est toujours référencée à partir de la table « table3 ».

Here the code :

# Model 

db.define_table('table1',Field('fieldt1','string'), format='%(fieldt1)s')

db.define_table('table2',Field('fieldt2','string'), Field('field2t2', 'reference table1', ondelete='NO ACTION'), format='%(fieldt2)s')

db.define_table('table3',Field('fieldt3','string'), Field('field2t3', 'reference table2', ondelete='NO ACTION'), format='%(fieldt3)s')

# Controller

def create_update():

    """create update funciton"""

    form = crud.update(db[request.args(0)], request.args(1))

    return dict(form=form)

I attach the app...

The problem arrive when I try to delete a record in table2 that is attached to a record in table 3.

I can make a function that will check if this record is attached by a record in table 3 before trying to deleted it, but I am curious to know if there is an other solution.

The database is Postgres.

I just test with SQLite and it is even worse, deletion of the referenced record is allowed but the record in the table 3 still reference the record. Form my point of view deletion of a record referenced shouldn't be allowed if there is a ondelete clause apply to the reference field. I also try to change the 'NO ACTION' for 'CASCADE' and if I delete a record of table 3 that reference table2 record the referenced table 2 record doesn't get deleted (still with SQLite).

I use web2py 2.2.1 for all these test.

Hope my model is correct.

I just test also with Postgres (8.4) and even when CASCADE is setted the referenced records are not deleted on deletion.

Thanks

Richard

--
 
 
 

[Mark]

Based on your model, records in table2 are parent records, and records in table3 are child records. I think the ondelete means on_parent_delete. 

Mark 

[Richard Vézina]

You are right that what I am think, but the doc is not clear.

I open an other thread about a function that should be trigger on crud.update in case of deletation with crud.update(..., ondelete=funciton)

Thanks

Richard

On Tue, Dec 11, 2012 at 2:18 PM, Mark <czhan...@gmail.com> wrote:

 

[Niphlod]

ok, I had time to test.
SQLite environment, bug is there for 2.2.1, but is fixed in trunk.
For PostgreSQL, it's a different story. I'll quote the official docs on that

Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything. (The essential difference between these two choices is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not.) CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well. There are two other options: SET NULL and SET DEFAULT. These cause the referencing columns to be set to nulls or default values, respectively, when the referenced row is deleted. Note that these do not excuse you from observing any constraints. For example, if an action specifies SET DEFAULT but the default value would not satisfy the foreign key, the operation will fail.

So, ondelete='NO ACTION' will still raise the error. If you need - as it seems - to simply let the "check" loose if the parent record is deleted, the way to go is either 'SET NULL' or 'SET DEFAULT'.
BTW: To exploit SET DEFAULT you can't rely on the default= attribute of the field, that one is valid only inside the DAL.

SET NULL works perfectly fine (i.e. your table3 record will have a field2t3 set to NULL, effectively "loosing" the reference to table2.id)
PS: you have to let web2py recreate the table because once the table is created, changes to the ondelete attribute will not trigger the COSTRAINT drop and recreation.

[Richard Vézina]

Thanks Niphold I appreciate.

I try to work around this by creating a ondelete function that will try to delete the record if it raise a error respond to the user the record can't be delete.

I struggle to catch IntegrityError, I open an other thread about that if you have time.

I don't want a record still referenced to be deleted and I want to prevent web2py to throw a ticket about that.

Thanks

Richard