Thread: 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
Attachment
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