Better error message for deletion of variable in use

[marcelgerber]

Core problem

We regularly get internal messages asking about this cryptic error message in the OWID Admin:

QueryFailedError: ER_ROW_IS_REFERENCED_2: Cannot delete or update a parent row: a foreign key constraint fails
(`live_grapher`.`chart_dimensions`, CONSTRAINT `chart_dimensions_variableId_9ba778e6_fk_variables_id`
FOREIGN KEY (`variableId`) REFERENCES `variables` (`id`) ON DELETE RESTRICT ON UPDATE RESTR)

This error message indicates that the user wants to delete a variable that is currently in use - but it doesn't do a very good job as an error message :)

MVP solution

In the code paths where we delete variables (which are: DELETE variable, DELETE dataset, import dataset), introduce an explicit check for this condition and provide a good error message.

The error message should include:

• For every conflicting variable contained in the dataset:
  • The variable name
  • The variable id
  • maybe, a link to the variable page in the admin
  • maybe, a list of grapher charts that currently use this variable

Suggested solution

As per @danielgavrilov's suggestion:

• Show in the admin an indicator of how many charts use this variable / dataset
• Disable delete button for a variable / dataset that's in use

Out of scope

• Checking for usages in explorers and the ETL dag
• Switching to archiving instead of deleting (preferred strategy still in discussion)

Context

One Slack conversation about this

[danielgavrilov]

It might be possible to make it obvious before hitting upload – I don't remember exactly, but I think the server returns the dataset's variables and allows authors to match up the old variable names to the new ones.

Instead of passing just the variable names to the client-side for matching, we could pass a flag whether the variable name is used in a chart, and then on the client-side we can show an error in the matching section.