List:General Discussion« Previous MessageNext Message »
From:Ferindo Middleton Date:May 19 2006 8:55pm
Subject:Re: how to default column value to lower( )
View as plain text  
Thanks Jay. Yeah, I'll just write a trigger... It sure would be cool though
if you could say something like..." ADD COLUMN new_column VARCHAR(100)
DEFAULT LOWER(new_column) ...." and then be done without having to
explicitly define a trigger for it.

This is for a userid field. Userid values at my org must be lowercase so we
can ensure that the userid values are portable and can be used exactly as
caputred within the db accross all our various systems... I just didn't want
developers/admins to have to worry about making/forcing this integrity
checks within the application and even if there's some person that
accidentally INSERTs records on the command line without following this
all-lowercase requirement.

Ferindo
On 5/19/06, Jay Pipes <jay@stripped> wrote:
>
> You could always use a trigger on BEFORE UPDATE/BEFORE INSERT which
> changes NEW.column to LOWER(column).  I know it's not declarative, but
> it works.
>
> sheeri kritzer wrote:
> > Not that I know of, but if you don't do binary (case-sensitive)
> > searching then does it really matter?? You can retrieve with LOWER, or
> > put it in your application, if your application needs to display it
> > that way.
> >
> > -Sheeri
> >
> > On 5/18/06, Ferindo Middleton <ferindo.middleton@stripped> wrote:
> >> I have column and I want to make sure the db is always making sure the
> >> value
> >> that gets input into this VARCHAR() column is always lowercase;
> >>
> >> Is there a way to set the value of a column within a table to
> >> automatically
> >> be lowercase. I know how to use the LOWER() function when performing
> >> queries
> >> but is there a way to define LOWER( ) within the definition of the
> table
> >> column itself without having the application specify LOWER( ) to any
> >> value
> >> passed to this column or if a record had to be manually input and the
> >> admin
> >> forgot to make sure all the characters were lowercase.
> >>
> >> Ferindo
> >>
> >>
> >
>
> --
> Jay Pipes
> Community Relations Manager, North America, MySQL Inc.
> Roaming North America, based in Columbus, Ohio
> email: jay@stripped    mob: +1 614 406 1267
>
> Are You MySQL Certified? http://www.mysql.com/certification
> Got Cluster? http://www.mysql.com/cluster

Thread
how to default column value to lower( )Ferindo Middleton18 May
  • Re: how to default column value to lower( )sheeri kritzer19 May
    • Re: how to default column value to lower( )Jay Pipes19 May
      • Re: how to default column value to lower( )Ferindo Middleton19 May
  • Re: how to default column value to lower( )Martijn Tonies19 May