Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Given the following CREATE TABLE statement:

CREATE TABLE [dbo].[Unit]
(
    [id]                SMALLINT       NOT NULL IDENTITY(1,1),
    [name]              VARCHAR(100)   NOT NULL,

    CONSTRAINT [PK_Unit] PRIMARY KEY CLUSTERED ([id]),
    CONSTRAINT [UQ_Unit_Name] UNIQUE ([name])
)

How to I allow the following two strings to co-exist without violating the UNIQUE constraint?

INSERT INTO dbo.Unit (Name) VALUES('e³m³/day') 
INSERT INTO dbo.Unit (Name) VALUES('e3m3/day') 

Any insight appreciated.

EDIT

Current Collation is Latin1_General_CI_AS

EDIT

Unless someone has a better option, it appears that I may have to use Latin1_General_BIN and manually handle CI in the few places where I need it... will convert this to an answer if no better solutions are offered.

share|improve this question

1 Answer 1

up vote 5 down vote accepted

Why not just collate that column as case-sensitive? I don't think you need to resort to binary. This seems to work fine, just note that it may affect sorting, comparisons, unions etc:

CREATE TABLE [dbo].[Unit]
(
    [id]                SMALLINT       NOT NULL IDENTITY(1,1),
    [name]              VARCHAR(100)   COLLATE Latin1_General_CS_AS NOT NULL ,

    CONSTRAINT [PK_Unit] PRIMARY KEY CLUSTERED ([id]),
    CONSTRAINT [UQ_Unit_Name] UNIQUE ([name])
);
GO

INSERT INTO dbo.Unit (Name) VALUES('e³m³/day') 
INSERT INTO dbo.Unit (Name) VALUES('e3m3/day') 

Results:

1 row(s) affected.
1 row(s) affected.

Now, this works for the example in your question, but whether it works for your entire set of requirements, is hard to say. You may have characters that are different to you but aren't treated differently in terms of case-sensitivity, such as:

INSERT INTO dbo.Unit (Name) VALUES('ß')
INSERT INTO dbo.Unit (Name) VALUES('ss')

Results:

1 row(s) affected.

And then:

Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint 'UQ_Unit_Name'. Cannot insert duplicate key in object 'dbo.Unit'. The duplicate key value is (ss).
The statement has been terminated.

In that case you may need to use binary after all.

share|improve this answer
    
I am never really a fan of the hammer approach, so I will give this a try... –  Calgary Coder Feb 5 '13 at 20:18
    
Awesome, that does the trick, thank you! –  Calgary Coder Feb 5 '13 at 20:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.