* 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]