February 20, 2013 at 8:45 pm
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.......
February 20, 2013 at 9:48 pm
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.
February 21, 2013 at 2:52 pm
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