Problems with unique key constraint.(CASE INSENSITIVE)

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • dycharles
    New Member
    • Sep 2007
    • 17

    Problems with unique key constraint.(CASE INSENSITIVE)

    I'm stack with this problem with some time now.

    Here is my problem.

    CREATE TABLE category
    (
    catdb_id bigserial NOT NULL,
    catdb_name character varying,
    catdb_remarks character varying,
    CONSTRAINT catdb_id_pk PRIMARY KEY (catdb_id)
    )

    INSERT INTO category(catdb_ name) VALUES('DyCharl eS')
    ---successfully inserted
    INSERT INTO category(catdb_ name) VALUES('dycharl es')
    ---successfully inserted

    How can i make this two values which i inserted above to be equal? Any solution. Thank you.
  • amitpatel66
    Recognized Expert Top Contributor
    • Mar 2007
    • 2358

    #2
    Originally posted by dycharles
    I'm stack with this problem with some time now.

    Here is my problem.

    CREATE TABLE category
    (
    catdb_id bigserial NOT NULL,
    catdb_name character varying,
    catdb_remarks character varying,
    CONSTRAINT catdb_id_pk PRIMARY KEY (catdb_id)
    )

    INSERT INTO category(catdb_ name) VALUES('DyCharl eS')
    ---successfully inserted
    INSERT INTO category(catdb_ name) VALUES('dycharl es')
    ---successfully inserted

    How can i make this two values which i inserted above to be equal? Any solution. Thank you.
    Convert the value to either UPPER or lower case everytime you do an insert because the data inserted in different case will not be the same

    Comment

    • dycharles
      New Member
      • Sep 2007
      • 17

      #3
      Thanks for the reply.

      I already do that. But i want to retain the original values or position that is being typed by the user. What will be my other solution? any suggestion? Thanks again.

      Comment

      • rski
        Recognized Expert Contributor
        • Dec 2006
        • 700

        #4
        Originally posted by dycharles
        I'm stack with this problem with some time now.

        Here is my problem.

        CREATE TABLE category
        (
        catdb_id bigserial NOT NULL,
        catdb_name character varying,
        catdb_remarks character varying,
        CONSTRAINT catdb_id_pk PRIMARY KEY (catdb_id)
        )

        INSERT INTO category(catdb_ name) VALUES('DyCharl eS')
        ---successfully inserted
        INSERT INTO category(catdb_ name) VALUES('dycharl es')
        ---successfully inserted

        How can i make this two values which i inserted above to be equal? Any solution. Thank you.

        Creating index should be enough
        CREATE UNIQUE INDEX in_category_cs ON category ((lower(catdb_n ame)))

        Comment

        • dycharles
          New Member
          • Sep 2007
          • 17

          #5
          What if the user will insert this three value.
          INSERT INTO category(catdb_ name) VALUES('DyCharl eS')
          ---successfully inserted
          INSERT INTO category(catdb_ name) VALUES('dycharl es')
          ---successfully inserted
          INSERT INTO category(catdb_ name) VALUES('dyChaRl eS')
          ---- successfully inserted

          How can they be equal in unique constraint or in unique index? Thanks

          Comment

          • rski
            Recognized Expert Contributor
            • Dec 2006
            • 700

            #6
            Originally posted by dycharles
            What if the user will insert this three value.
            INSERT INTO category(catdb_ name) VALUES('DyCharl eS')
            ---successfully inserted
            INSERT INTO category(catdb_ name) VALUES('dycharl es')
            ---successfully inserted
            INSERT INTO category(catdb_ name) VALUES('dyChaRl eS')
            ---- successfully inserted

            How can they be equal in unique constraint or in unique index? Thanks
            They are equal because index is using the lower() function.

            Comment

            • dycharles
              New Member
              • Sep 2007
              • 17

              #7
              Thanks, great help.

              More Power

              Comment

              Working...