6

I have a business app with various business forms where users edit data. When a user deletes a record - it shows "FK_ABC_AA.. constraint violated, etc."

I want to do better than that and tell the users something informative.

I've seen some applications just display "You cannot delete this record because other data references it." or something along those lines. I don't like that because it is not enough info.

I can parse error messages from database and display specific human-readable messages but this "smells" before I even do it. Is there preferred way to display such information?

EDIT:

I should mention that:

  1. Application is not crashing in my case. The record is NOT deleted - transaction rolled back. It's just a message that shows..

  2. Deleting associated records is not really practical in this situation. It's a big ERP-like database. So, let's say you created a customer and want to delete it right away - it's OK. But you really don't want to delete customer with associated orders.

  3. Disabling the button also not practical because that will require ongoing modifications to the same spots as I add relationships. Also, I need to do additional querying every time I retrieve record just to see if I can delete it.

  4. Most users DO NOT have permission to even do Delete, so it is not about being nice to everybody.

2
  • 1
    @katit: I don't understand the end of your third point: so you don't want to spend 10 ms. doing an additional query, but are ready, instead, to lose 5 seconds of your customers time reading an error message he don't even care about? By the way, if the number of queries matter, in most databases, you can query both data itself and the existence of related foreign entities in the same query. Feb 14, 2012 at 16:48
  • @MainMa: My 3rd point has nothing to do with user's 5 seconds. I ask this question to see what is the best way to give user nice information. In my 3rd point I explain why checking database is no good. What if someone inserted related record after I retreived one and did checks? Sorry, but seems like you didn't work with big loosely-coupled systems.
    – katit
    Feb 14, 2012 at 16:53

7 Answers 7

6

It sounds like this is an application where the user seriously needs to know when an operation goes through. In this case you can not get away with hiding the function or hiding the error message as others have suggested.

From your description, I am inferring that these are admin or otherwise trusted users that are knowledgeable about what they are doing (like deleting a customer). You can't violate these constraints and you have to express the intent of the constraint and the required course of action to the user.

If all orders for a customer must be deleted from a customer before they can be deleted, tell the user this (and possibly where this could be done). Consider giving them an option to do this right away.

Give them a Cancel option (emphasized) and a "Delete parent and child records" option. In this case it would be "delete customer and all related orders". Explain it in the terms the user will understand. Clearly indicate what will happen when you hit the delete button if you go this route.

If they can't delete associated records all at once (you hint they can't), tell them what must be done for them to be able to delete the parent record. If they have to delete all orders or close all orders from that customer, tell them that. If it's a completely impossible situation, disabling the delete button and giving a reason in a tool tip explaining why this record can't be deleted. It doesn't seem like this should happen often.

You say you can't disable the button, but really all you have to do is check before and after the button is clicked; if upon opening a customer you know I can't delete them no matter what, just disable the button. If it looks like I can delete them, fire off a check after you click the button to see if I still can delete the record; just give the error message if I can't. I see no practical or UX reason you can't do both.

5
  • Yes, what you suggest totally make sense. Thing is - system has framework built for all CRUD operations. Adding checks will require bunch of custom coding on each screen. Also, our application grows, new modules added all the time and now I need to remember to modify Customers screen when I add another reference to Customers table
    – katit
    Feb 14, 2012 at 20:37
  • Plus all the database overhead related to lookups/checks from every data-entry screen. All we really need is to display user friendly message on why he can't delete. And that requires some kind of proxy for database constraint violation messages
    – katit
    Feb 14, 2012 at 20:39
  • @katit Our ERA system here took the course of "just display the damn SQL error message" which leads to endless IT calls about the simplest thing, at the least you should be able to catch the SQL errors and return something meaningful if generic, and output specific for common errors
    – Ben Brocka
    Feb 14, 2012 at 21:00
  • Just display the.. is what I have right now and I am getting calls :) But I will get same calls if I disable button :) asking why?! My idea was to put messages through filter and make them "pretty" for known validations, otherwise just diplay damn SQL Message..
    – katit
    Feb 14, 2012 at 21:02
  • For common stuff I'd definitely display meaningful messages; identify common problems and give helpful messages for those errors, you can probably ignore the rest until they become issues, since there's too many possible errors.
    – Ben Brocka
    Feb 14, 2012 at 21:07
1

You don't display those messages to the user in a first place, because the user doesn't care to know why your applications is not working as expected. Those errors must be handled by QA department before releasing the software, or, if impossible, saved in the log file for further bug report.

If the record to delete contains associated records in other tables, it means that:

  • Either you must delete associated records, in which case, just do it.
  • or you must not delete the first record itself. In this case, the delete button in UI must be disabled, with maybe a tooltip explaining why is it disabled. If it was impossible (for example the foreign records were added by the other user meanwhile), then notify the user that the record is impossible to delete.
6
  • 2
    While generally accurate; the user may care about the underlying data, depending on who the user is and who the application is targeting. Feb 14, 2012 at 16:37
  • 1
    @Aaron McIver: those cases are extremely rare and are often misunderstood (for example, as a developer, when I use the IDE, I don't care why it crashes; I just need it to never crash). Also, as you see from the edit of the question, this is a perfect case where the end users don't care about FK violations: if they can't delete a customer, they must be unable to do it from the UI, instead of having a large "Delete" button which will raise a cryptic error mostly every time they click on it. Feb 14, 2012 at 16:43
  • It is inposible in a multi user system to always have the delete button disabled when there are associate records, as another user may have just added the associate record.
    – Ian
    Feb 14, 2012 at 16:53
  • Thanks Ian, I just added this to my comment in original post. Another thing here - my users will WANT to know why they can't delete. If I start disabling buttons out of the blue - there will be questions WHY it is disabled.
    – katit
    Feb 14, 2012 at 16:57
  • @Ian: that's why in the last point of my answer, I discuss two cases, the second one being the case when you cannot disable the button. Feb 14, 2012 at 20:22
1

I'm with svick, only I prefer Active/Inactive status flags on entities. Not only can you drive button enable/disable, but also use coloring on the record itself (ie. in a UI table) to indicate it's Active status, retain referential integrity, get it all with one simple query, and not confuse Inactive with actually having been Deleted. An audit table usually holds data like action=deleted, user=some_user, date=some_long, status=Active ... or status=Inactive ... so Deleting (action) is different than Deactivating (status).

0

Another option may be to have a flag on each row that says whether it's deleted.

This way, deleting a row does not violate any foreign keys. And in case you want to "undelete" a row, or view information about a deleted row, you can.

2
  • This works around the actual foreign key constraint but may result in invalid data (orders with no customer, ect) which breaks the whole point of proper relationships.
    – Ben Brocka
    Feb 14, 2012 at 19:25
  • Well, not exactly. You won't get orders with no customers, but you can get orders with deleted customers. You have to think how to handle that, but that doesn't necessarily mean it's a bad idea.
    – svick
    Feb 14, 2012 at 19:30
0

What I did in a recent app is check to see if a record could be deleted, and not let the user remove it unless it would be deleteable. If done carefully, it can be reasonably efficient to do this, and it means that the user simply knows that this record cannot be deleted, although the reason why is irrelevant to the user. Hopefully, they will have an idea of the reasons why, which should lead them to explore further.

Of course this will not always be relevant, but the principle that they should only be allowed to do things that make sense should be adhered to. You may have to also provide information or click throughs to indicate what the issue is - if it has child records, you might want to indicate the number of records, and provide click through to see these.

But the detailed solution will be more specific to your application and users. The way that works for your application will depend on your specifics.

0

Okay, so you want to make your Application more informative. You know the data, you know the schema, you can inform the user appropriately through business rules in your Application:

  1. User presses the Delete button
  2. You query the schema to see if there is a FK relationship and FK records
  3. You inform the user that there are FK records and perhaps a summary
  4. Give them the choice to Continue or Cancel
  5. If the user chooses to Continue, take them to deleting the FK records first
  6. If the user chooses to Cancel, then you cancel the Delete button action
0

I suggest deactivating the delete button for records that have associated data.

If you think the user cares about why he/she may not be able to delete it, put an info button in the place of the delete button that tells them that the user has associated orders (or whatever the specific reason may be).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.