Is there way to give user-friendly error message on constraint violation

Question

Say I have column Gender and constraint CHECK( Gender IN ('F', 'M', 'OTHER')).
If I accidentally forgot to handle this on client side, user will see smth like
ORA-02290: check constraint (SYS_C099871244) violated
which is not very helpful nor for user, nor for developer who maintains or debug

Is there way to provide developer-defined message like (pseudo)Java's
assert Gender IN (0,1):'Gender must be F or M'

The only way I can think of is to move constraints to the BEFORE UPDATE OR INSERT trigger and on failure do Raise_Application_Error( code, my_message ). But I don't like it

EDIT List of specific reasons, as per in the comments
1. I really like to keep logic as close to data as possible
2. For end user Raise_Application_Error message is indistinguishable from application message
3. Developers will see nice message, even if access data bypassing application
4. moving constraints to triggers is ugly (is it?), so I have to find smth different from Raise_Application_Error

EDIT2 1,5 years later, and after I've left db-related job, it has finally occured to me, what I really don't like about this - code duplication. I have to repeat exactly same logic on server and on client side. Most likely, in 2 different languages. And keep them in sync. This is just ugly.

Though as answers clearly indicate, there is nothing I can about this. So it's time for me to be good citizen and finally accept an answer (sorry, just forgot about that).

Vacuva

paxdiablo gives a good answer, if it does not satisfy you maybe you should specify why exactly you want to do it db server side and not application side? – Bicknell

@Bicknell I've added some thoughts in comments to his post – Vacuva

you state that you want to keep logic as close to the data as possible and that's a good thing for database logic. However, presentation of constraint violations, while detected by the DBMS, is an application issue, not a database issue. – Hibiscus

Answer 1

If you are looking for a way to tell Oracle always to replace the exception message "ORA-02290: check constraint (SYS_C099871244) violated" by another message like "ORA-20001: Gender must be F or M", then the answer is: no, it can't be done.

What you can do is provide a solution that can be used by developers in their code something like this:

...
begin
    insert into emp (empno, gender) values (p_empno, p_gender);
exception
    when others then
       error_pkg.handle_exception;
end;

The error_pkg.handle_exception procedure would parse the Oracle exception message and extract the name of the constraint (if it was a constraint violation) and lookup that constraint name in a cross reference table to get the required message, then use raise_application_error to re-raise the exception with the new message.

I suppose Oracle could offer a package and table like this as standard, but perhaps since in practice there are many different requirements for error handling in system, it isn't considered to be generally useful enough.

Assentation

Answer 2

Constraints are what databases use to protect themselves from errant applications, not from users.

That means that constraint violations should be captured by the application and possibly cleaned up for presentation to the user. I'd consider an application which didn't do that to be deficient in some manner.

I say 'possibly' since your application (at least for this case) should never see that happen. It should almost certainly be using a drop down limited-choice control for something like that. If it used a combo-box or (shock, horror) a free-format text entry field, it would need to be redefined.

That would mean that the violation would never occur unless, of course, the application and the constraint get out of sync at some point. But that's something that should be caught in testing, long before a customer ever gets their grubby little hands on your application.

---

To answer your actual question, the messages that come out of Oracle for constraint violations cannot be changed. The best you can do is to name your constraints intelligently so that it may make sense to an end user.

But I still maintain that this presentation of problems to a user is a responsibility of the application layer, not the database layer.

Hibiscus

First, you have not answered original question :) "Is there way or not?". Pedantism aside, gender is just for example, of course. I haven't thought this out yet, but I was thinking to my self: 1 I really like to keep logic as close to data as possible 2 For end user Raise_Application_Error message is indistinguishable from application message 3 Developers will see nice message, even if access data bypassing application 4 moving constraints to triggers is ugly (is it?), so I have to find smth different from Raise_Application_Error 5... 6 Profit! – Vacuva

One more point: No one relies on constraints being respected by application, otherwise why would we need to keep them in DB, right ? So why don't we built in a little more info into DB ? – Vacuva

As a DB developer, I'd still name a constraint so that (1) it is easier for an application developer to understand/trap/handle and (2) if the end-user gets a generic "Problem found. Call support" message, at least there's potentially a useful message in a log file for support to deal with – Bye

I'm not advocating relying on the application respecting the constraint, just on it reporting it nicely. Constraints are to protect the data and that's it. They don't care one little bit about reporting a violation to an end user, especially in a properly tiered setup. All they do is maintain ACID properties and tell the offending application that it did something wrong. The application should be responsible for the presentation layer. If your user sees a violation message directly from the DBMS then, in my opinion, your application is faulty. – Hibiscus

And the reason I didn't answer the original question is the same reason I would answer "How do I write an operating system in COBOL?" with "I wouldn't". The answer is moot in a properly designed system. But I'll update it as you wish. – Hibiscus

@Alexander Malakhov, I agree with paxdiablo - maybe the best argument against it is that constraints and error messages are not one to one. For example sometimes you might want to offer different courses of action for the same table and exception of the same constraint (various combinations of retry, cancel, ignore and custom actions) with different custom messages. – Bicknell

Answer 3

In short:
No way of catching oracle errors for custom handling that I know of. However I don't think you should be trying to do that anyway.

---

Long version:
The intentions behind your reasons are good, however...

I really like to keep logic as close to data as possible

Logic should be as close to data as possible, that is true; however this does not qualify - this is not logic, this is presentation of codes that identify exceptions to already defined logic, and presentation should not be mixed with data or logic layers (the domain of error messages spans over every part of the system; from client side to server side, also think about translating, consistent updates, easier management and overview of the messages, etc...)

For end user Raise_Application_Error message is indistinguishable from application message

True, but the reverse is valid as well and therefore not particularly relevant - if you have central repository of DB error codes, application error codes, and error handling will process it then it is irrelevant (for end user) which layer is presenting error messages. Also, long term, it is not clear that it would save you any work.

Developers will see nice message, even if access data bypassing application

This is true, for developers accessing DB directly there would be nicer error messages. Still a few comments apply here - in complex systems bypassing the application layer should not be allowed (even for developers); if that would be allowed you would expect devs to know where to look up the error messages from the constraint names (central repository of error codes and messages should/would be maintained in the same db)

moving constraints to triggers is ugly (is it?), so I have to find smth different from Raise_Application_Error

It is ugly in a sense that it is presentation and should not be in DDL. Also, it incurs unjustified(?) performance penalties if done through triggers (not sure how big, nor how elegant it could be done).

Note: All in all I do agree that it would be a nice feature to have possibility to hook into DBMS error handling.

However, error handling and error message processing has following properties

• needs to be maintainable (this, theoretically, could be done cleanly by storing custom error messages in the information schema, but SQL standard does not specify that so this is purely theoretical comment - in practice you will have to have your own tables for such purposes)

and, even more importantly

• error message processing is context sensitive (and error handler would be most informed from the point of view of the data client - sometimes same error code might need different presentation, different message)

Bicknell

Answer 4

Whether the constraint gets raised to the client or logged in a file for (potential) analysis by support, you should have a more useful message.

If you name your constraint it gets more helpful.

I'd go for something like

ALTER TABLE blah ADD CONSTRAINT blah_gender_ck CHECK ( Gender IN ('F', 'M', 'OTHER'));

Bye

Agree, it saves time while debugging. I name even NOT NULL constraints :) – Vacuva