In lecture #7 of the Database Design section, the MySQL Workbench Data Modelling feature is used to determine what happens when the primary key of a record in the parent table is changed or a record in the parent table is deleted. In the Data Modelling utility, constraints such as CASCADE and RESTRICT are discussed and how they affect database behavior.
I wanted to ask if it is preferable to use triggers to implement these types of constraints as opposed to selecting built-in options? I would think triggers or other types of logic would make the database application using these tables more robust.
For example, I remember from my Access development days, in such situations my code would not use the Cascading Delete option in table relationship settings to automatically delete related child table records because sometimes the child table records that potentially will be deleted should first be archived and only afterwards deleted. Or, more often, per business rules, a parent table record having related child table records should never be deleted; rather, the user or the application must intentionally delete the related child table records prior to deleting a parent table record.
I don’t believe such functionality is available if the built-in constraint options are selected. As long as users don’t have direct access to the application’s underlying tables, logic can be written to handle cascading updates and deletes in a more controlled and granular fashion.
Can anyone with real world experience using MySQL comment on this matter to help me better understand proper database design principles?
Thank you kindly.