are web2py Database Abstraction Layer (DAL) references on cascade by default?

Question

When I create a database in web2py using the DAL, and I create a table for the user comments on my website for example and I need to be able to get the user that sent that particular comment, I can do it by email..
However emails can change over time (possible option) and the database itself could end up looking for a non existent user if the email is not updated on all "child" tables that uses that email as reference 1 to 1 for that user.
For this reasons, I would need to automatically update all the Foreign Keys in child tables, so is this feature (update on cascade on foreign keys) present and by default when using DAL / is it possible to tell the DAL connection to do it by adding a updateoncascade=True in needed Field("name", type="type", notnull=True, updateoncascade=True) fields?

Answer 1

The DAL does not provide an API to specify ON UPDATE CASCADE when creating a table, so you would have to do that externally. Alternatively, you could make use of the _after_update hook to update records in any child tables.

Also consider whether you want to set a foreign key on the email address rather than using the built-in reference field functionality, which creates a foreign key on the id field of the parent table. Because the id of a given user record will never change, you do not have to worry about cascading updates:

db.define_table('comments',
    ...,
    Field('author', 'reference auth_user'))

Above, 'reference auth_user' sets up a foreign key to the db.auth_user.id field.