How To Make Unique Constraint Case Sensitive ?

  • I Have Table With Column With DataType Varchar(50) Which Is Having A Unique Constraint On That.

    For Example : I want To Insert These 3 Values Into Above Column

    'ABC' 'abc' 'Abc' .... etc

    I Here Is My Problem,

    If I Try To Insert Above 3 Values,Throws An Error (UNIQUE KEY constraint 'XXXXXXXX'. Cannot insert duplicate key in object).

    My Requirement Is To Treat Those 3 Values As Different Values And Insert Into That Column.

    Suppose If The Value Is Already There Like 'ABC' In The Column And If I try To Insert 'ABC' Again Then It It Should Through Me And Error

    But Not With These Values 'abc' 'Abc' ABc.... etc .It Should Treat Is As Different As We Can See Visually....

    Collation Setting On DataBase Level Is Case InSensitive.

    Collation Setting On Column Level Is Case Sensitive.

    Any Suggestions Please.......

  • Cant you just set the column to a case sensitive collation ?

    edit : Column is already set to CS

    That will teach me for replying before reading the entire post.

  • gopinathreddy.d (2/20/2013)


    I Have Table With Column With DataType Varchar(50) Which Is Having A Unique Constraint On That.

    For Example : I want To Insert These 3 Values Into Above Column

    'ABC' 'abc' 'Abc' .... etc

    I Here Is My Problem,

    If I Try To Insert Above 3 Values,Throws An Error (UNIQUE KEY constraint 'XXXXXXXX'. Cannot insert duplicate key in object).

    My Requirement Is To Treat Those 3 Values As Different Values And Insert Into That Column.

    Suppose If The Value Is Already There Like 'ABC' In The Column And If I try To Insert 'ABC' Again Then It It Should Through Me And Error

    But Not With These Values 'abc' 'Abc' ABc.... etc .It Should Treat Is As Different As We Can See Visually....

    Collation Setting On DataBase Level Is Case InSensitive.

    Collation Setting On Column Level Is Case Sensitive.

    Any Suggestions Please.......

    The two bolded statements above do not compute. Please post the DDL for the table.

    Indexes use the collations of the columns they reference. Per this proof-of-concept if the column were using a case-sensitive collation then you would be able to insert ABC, Abc and abc:

    USE tempdb;

    ---------------------------------

    IF EXISTS ( SELECT *

    FROM sys.objects

    WHERE object_id = OBJECT_ID(N'dbo.test_collation')

    AND type IN ( N'U' ) )

    DROP TABLE dbo.test_collation;

    CREATE TABLE dbo.test_collation (name VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AI); -- case-INsensitive collation

    CREATE UNIQUE INDEX [uix] ON dbo.test_collation (name);

    INSERT INTO dbo.test_collation(name)VALUES ('ABC');

    INSERT INTO dbo.test_collation(name)VALUES ('Abc'); -- error, as expected with a case=INsensitive collation

    ---------------------------------

    IF EXISTS ( SELECT *

    FROM sys.objects

    WHERE object_id = OBJECT_ID(N'dbo.test_collation')

    AND type IN ( N'U' ) )

    DROP TABLE dbo.test_collation;

    CREATE TABLE dbo.test_collation (name VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS) -- case-sensitive collation

    CREATE UNIQUE INDEX [uix] ON dbo.test_collation (name)

    INSERT INTO dbo.test_collation(name)VALUES ('ABC')

    INSERT INTO dbo.test_collation(name)VALUES ('Abc') -- no error, as expected with a case=sensitive collation

    GO

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply