Обсуждение: Case Insensitive CHECK CONSTRAINTs

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?

--
Shaw Terwilliger <sterwill@sourcegear.com>
SourceGear Corporation
217.356.0105 x 641

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?

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

Shaw Terwilliger <sterwill@sourcegear.com> writes:
> I have a table with a TEXT field called "username".  I'd like to retain the=
> =20
> case of the data stored here, but I'd like all comparisons to be done witho=
> ut
> regard to case.  Since all these accesses _should_ be done through database=
> =20
> 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=
> =20
> alternate case "duplicate" records don't creep into the table.  Any tips?

Make that index be UNIQUE.

BTW, please don't send HTML-coded mail to the lists.  It's a pain in the
neck to quote.

            regards, tom lane

On 3/14/01, 6:59:18 PM, Shaw Terwilliger <sterwill@sourcegear.com> wrote
regarding [GENERAL] Case Insensitive CHECK CONSTRAINTs:

> 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?

As long as you have a UNIQUE index on lower(username) that will prevent
"duplicate"s. The UNIQUE applies to the index entry not the column(s) the
index applies to.

- Richard Huxton