thinkwisesoftware.com thinkwisesoftware.com
Closed

Show info of the conflicting records when deleting a row is not possible

  • 17 October 2019
  • 1 reply
  • 41 views
Votes
K.Bakkenes
Rank
Userlevel 5
Badge +6

Deleting a record is not possible when the record is linked in other tables (unless ‘cascade delete’ is set). The GUI will show the following error message when this situation occurs.

The error message doesn’t clearly mention which records are causing this problem. A user would like to see the specific records that are linked to the row. This error could occur in a lot of screens when ‘cascade delete’ is OFF.

A solution would be to create specific errors per table. But this would result in a lot of INSTEAD OF DELETE triggers and a lot of (TSF) messages.

I’d rather see the GUI solving this issue with the knowledge of the metamodel behind it and showing every entity with the look-up value of conflicting records.

1 reply

Henk Nicolai
Rank
Userlevel 4
Badge +1

The GUI cannot do this with 100% accuracy as it has no knowledge of what constraints are applied on the database. It may do predictions based on what it does know about references of the model, but cannot guarantee that once suggested records have been removed, the ‘master’ record can be removed as well. It also cannot guarantee that removing the suggested records is necessary at all, i.e. if they really were what caused the error message. And, it cannot rule out the effects of triggers.

It depends on how much of this is acceptable, what kind of solutions we could offer for this issue.

For background, what happens is that the error message returned from the database is matched against a regular expression and then converted into a more user friendly representation. The message in the screenshot is generated like this:

  1. Receive error message from database.

  2. Error code == 547, matches regular expression The DELETE statement conflicted with the REFERENCE constraint "(?<constraint>.+)"\. The conflict occurred in database "(?<database>.+)", table "(?<table>.+)"(, column '(?<column>.+)')?\. → this matches the definition of GUI message “err_reference_constraint_delete”.

  3. Look up translation for ‘table’ and ‘column’ in the model, for the current language.
  4. Paste into “err_reference_constraint_delete” translation for the current language: “Cannot delete this row due to related '{table}'. Please update or delete these '{table}' and try again.”

The GUI message, its error code and regular expression, together with its translations, are predefined in base project “SQLSERVER_DB” in the model.

 

Reply