11

I want to add some constraint to my username varchar in the SQL table so that if a username exists, a duplicate username in a different case cannot be created. How can I do this? Thanks

Edit:
I am using PostgreSQL, a little syntax help will be greatly appreciated.

informatik01
  • 16,038
  • 10
  • 74
  • 104

3 Answers3

23

From the docs

CREATE UNIQUE INDEX lower_title_idx ON films ((lower(title)));
Patryk Kordylewski
  • 1,263
  • 1
  • 7
  • 11
  • Do you need to add a constraint to the table after this? I tried to do: `ALTER TABLE films ADD CONSTRAINT unique_file_title UNIQUE USING INDEX lower_title_idx;` but got error `Cannot create a primary key or unique constraint using such an index. Index contains expressions.` I tried inserting case-insensitive data and it seems to work even without the constraint. – RcoderNY Apr 23 '21 at 03:18
4

If the tables are not yet populated you may consider simply converting to a standard upper or lower case prior to doing any insertions and making the field a primary key (or just have a unique constraint). If the user want to see his userid in the case he specified this could be another column in the database.

Update:Based on the updated tags I would still suggest the solution I have proposed as being less dependent on a particular DBMS.

ojblass
  • 21,146
  • 22
  • 83
  • 132
  • 1
    -1 sorry. Only badness could arise out of storing a username in two different ways when one of them can be trivially computed from the other. There's no need to break normalisation here as Endlessdeath's answer shows. – j_random_hacker May 05 '09 at 13:23
4

Do note that PostgreSQL 8.4 (currently beta) will have a case-insensitive text type.

bortzmeyer
  • 34,164
  • 12
  • 67
  • 91