Add reference integrity to Reference fields

Feature
1

The Reference fields are great, but they don’t have reference integrity: if you delete a record that is pointed by a reference field, this field is not set empty if you don’t implement it, and it isn’t checked if this field is required.

It could be implemented in each module and model that use Reference field… but it will generate repetitive code and some hidden bugs that could be resolved in a general way.

Proposed solution

It is only my first thought about the solution; it requires to be refined or directly discarted by another aproximation.

Option A: a table for each Reference instance

This design has similarities with Property design which has proved ineficient, but in this case it is only used on deletions, so I think could be good enought.

  • A model ir.reference with this fields:
    ** reference_model: name of model that has the Reference field
    ** reference_field: name of field that has the Reference field
    ** referenced_model: name of model that the reference points to
    ** referenced_id
    The value of reference is the concatenation of referenced_model + “,” + referenced_id
    ** required: boolean

  • On ModelStorage.delete, get from ir_reference table the records for its model and deleted instances
    ** If there is ir.reference with required=True => raise required field exception
    ** Set to null the reference field of found records and delete them. It should be done AFTER finish all delete() call

  • On fields.Reference, the set should maintain the ir.reference records.
    ** The Reference field should have an option to avoid this behaviour (it won’t generate ir.reference instances) because the developer implements this control? (I’m thinkg, for example, in “shipment” field of moves)

Option B: a table for each Reference field

Similar to A but it doesn’t have a record in ir.reference for each value of fields but only one for each field.

  • A model ir.reference with this fields:
    ** reference_model: name of model that has the Reference field
    ** reference_field: name of field that has the Reference field
    ** referenced_model: name of model that the reference points to
    ** required: boolean

  • On ModelStorage.delete, get from ir_reference table the reference fields that points to its model and search ‘reference_model’ records that points to deleted instances
    ** If there is ir.reference with required=True => raise required field exception
    ** Set to null the reference field of found records and delete them. It should be done AFTER finish all delete() call

  • On ModelStorage setup maintain the ir.reference records.
    ** The Reference field should have an option to avoid this behaviour (it won’t generate ir.reference instances) because the developer implements this control? (I’m thinkg, for example, in “shipment” field of moves)

2

I don’t think adding any new table is a good way to work because it will generate more work to fill it at the creation. In business application, we create much more often than delete.
Indeed we already have in ModelSQL.delete the code to manage foreign keys but only for Many2One. I remember well when implementing it to have thinking about adding the Reference fields or not. It was decided it will cost too much because as it can referenced many models, a lot of table will need to be checked. So instead for now, the Reference field replaces by None any reference that doesn’t exist. This is like if there were a ondelete="SET NULL" on it.
I think it could be good to give a try to include Reference fields in the foreign key check but only for the one which will be explicitly requested. For that we could add a ondelete keyword with a default value of None (to ignore).