Unexpected Unique Constraint Violation

Question

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.

Aaron Bertrand · Accepted Answer · 2013-02-05T20:17:01.017

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.

Unexpected Unique Constraint Violation

1 Answers1