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