Re: Case Insensitive CHECK CONSTRAINTs

* Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> [010314 11:52] wrote:
>
> I'd guess that making the lower() index unique would possibly work.
>
> On Wed, 14 Mar 2001, Shaw Terwilliger wrote:
>
> > I have a table with a TEXT field called "username". I'd like to retain the
> > case of the data stored here, but I'd like all comparisons to be done without
> > regard to case. Since all these accesses _should_ be done through database
> > functions, I can simply lower() the input values and compare. I also have
> > an index created on lower(username), so lookups are quick.
> >
> > However, I'd also like to have some sort of table constraint to make sure
> > alternate case "duplicate" records don't creep into the table. Any tips?

Define a trigger/rule that does either a transformation during insert
or updates the insert rule. If you put a unique index on the 'lower'
column then the rule should bomb out and explain why.

As a safety precaution, i would also make a rule that automagically
bombs out on a direct update to the 'lower' column.

--
-Alfred Perlstein - [bright(at)wintelcom(dot)net|alfred(at)freebsd(dot)org]