Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    8

    Question Unanswered: Case-Insensitive Unique Constraint

    I have a varchar as the primary key of one of my tables. What I would like to do is add a constraint so that case does not matter when comparing the text values. For example, if I have "text" in the primary key column of one of my rows, I want to dissallow the ability to insert a row with "Text" in the primary key column.

    This is what I have tried so far:
    constraint blah check (column_name not in (select lower(column_name) from table_name));

    and

    constraint blah unique(lower(column_name));

    Neither worked of course (sorry if those ideas seem off the wall, I'm used to Oracle's syntax, not that they would work in Oracle).

    Any ideas appreciated, thanks.

  2. #2
    Join Date
    Dec 2003
    Posts
    8
    Ok, I've found the solution. Hopefully this will help someone with a similar problem:

    create unique index on table_name (lower(column_name));

    I didn't fully grasp the concept that unique constraints implicitly create indexes.

  3. #3
    Join Date
    Apr 2003
    Posts
    23
    If you really want to have a primary key, don't forget to set the NOT NULL constraint on the field.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •