I'd go the other way and use a trigger to validate the data, and roll
back the transaction if it isn't in valid format (assuming you are using
transactions). This way, ANY app that puts data in that field gets the
validation. An error in an application or typing by someone who has
direct table access will leave you with bunk data, which is worse IMO
than having to figure out the code for the trigger.
You have to keep in mind that I generally spend half my day fixing
broken data though :)
Mogens Melander wrote:
> On Thu, May 3, 2007 12:56, Tim Milstead wrote:
>> Is it possible to enforce data formatting in fields using something like
>> a regular expression?
>> varchar is great but does not stop someone putting in the wrong
>> reference number.
>> I suspect the answer is no, you have to do it at a higher level. What
>> aprroach would people suggest?
>> Perhaps another table with two fields - field_name and field_regex?
> And, what would you have mysql do if a user failed to enter fx. valid date ?
> Maybe you'd be better off, handling input validation in the user-interface.