Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How To Make Unique Constraint Case Sensitive ? Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 8:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:18 PM
Points: 12, Visits: 105
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.......
Post #1422388
Posted Wednesday, February 20, 2013 9:48 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 218, Visits: 1,847
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.
Post #1422392
Posted Thursday, February 21, 2013 2:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1422804
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse