Check constraints, the impossible dream?

I've always thought that using declarative constraints the preferred method of validating fields in an Oracle database. However, I'm now having serious doubts about the usefulness of check constraints.

The 1st problem is that a check constraint such as "ADD CONSTRAINT CK_TABLE_FIELD CHECK (value > 0)" returns the extremely terse and unhelpful message upon a failure: "ORA-02290: check constraint (SCOTT.CK_TABLE_FIELD) violated". How does this help an end-user realise that the field must be a number greater than zero? Is there a way of associating a user-defined error message to a constraint? Does this mean I need to create a trigger that calls a ‘validate_fieldx' function and uses RAISE_APPLICATION_ERROR so a sensible error message can be generated? Are there performance implications here?

The 2nd problem with validating a real field such as a postcode. In the UK, a postcode pattern can be defined in Perl or VB as "[A-Z]{1,2}\d{1,2} \d[A-Z]{2}". It seems the Oracle DB does not support regular expressions, so what would people suggest? A complex trigger, I'm guessing.

The 3rd problem lies with the user data-entry validation model (2-tier or n-tier). I believe that the database should be protected, i.e. constraints should be in place in the database to ensure data integrity. I would not rely on middle tier or client tier checks to protect my database. I also think that the UI should be user friendly. I.e. providing masks, dropdowns, and sensible error messages at field level. And finally, I don't want to have more than one definition of my checks, as that would be a maintenance headache. I've never seen a good solution to this but I'm always hoping… Received on Wed Mar 20 2002 - 09:25:07 CST