add ability to configure "on delete" and "on update" attributes of foreign keys:

[fgregg]

sqlite supports these, and it would be quite nice to be able to add them with sqlite-utils.

https://www.sqlite.org/foreignkeys.html#fk_actions

[simonw]

Wow, this is a neat feature I didn't know about. Looks like there are a bunch of options:

• NO ACTION (default)
• RESTRICT: application is prohibited from deleting a parent key when there exists one or more child keys mapped to it
• SET NULL: when a parent key is deleted the child key columns of all rows in the child table that mapped to the parent key are set to contain SQL NULL values
• SET DEFAULT: set a specific default
• CASCADE: propagates the delete or update operation on the parent key to each dependent child key

[simonw]

I think the natural place to add these in the Python library API would be https://sqlite-utils.datasette.io/en/stable/python-api.html#adding-foreign-key-constraints - maybe something like this:

db["books"].add_foreign_key("author_id", "authors", "id", on_delete=RESTRICT)

Then for the CLI tool could be added to https://sqlite-utils.datasette.io/en/stable/cli-reference.html#add-foreign-key

sqlite-utils add-foreign-key my.db books author_id authors id --on-update SET_NULL

I wouldn't support these for the other methods of adding foreign keys - foreign_keys(...) for the various .insert() etc methods and the add_foreign_keys(...) bulk menthod.