From: Martijn Tonies
Date: May 3 2007 2:33pm
Subject: Re: Enforcing Data Format
List-Archive: http://lists.mysql.com/mysql/206558
Message-Id: <02bc01c78d90$0e310890$9902a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: 7bit




> 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 :)

Which is exactly why constraints and this type of stuff needs
to be implemented in the database :-)

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

>
>
> Mogens Melander wrote:
> > On Thu, May 3, 2007 12:56, Tim Milstead wrote:
> >> Hello,
> >>
> >> 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.
> >
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=m.tonies@stripped
>
>
>