SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How To Make Unique Constraint Case Sensitive ?


How To Make Unique Constraint Case Sensitive ?

Author
Message
gopinathreddy.d
gopinathreddy.d
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 131
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.......
matak
matak
SSC-Addicted
SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)

Group: General Forum Members
Points: 451 Visits: 3948
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.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15025 Visits: 14396
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search