Re: Case Insensitive CHECK CONSTRAINTs

* Stephan Szabo <sszabo@megazone23.bigpanda.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@wintelcom.net|alfred@freebsd.org]