Why does this Insert of two distinct values cause a Unique Constraint violation?

user_7024019 0
Viewed 86

Given the following table definition

CREATE TABLE Test
(
  ID bigint IDENTITY( 1, 1 ) not null,
  NativeName nvarchar(150) not null,
  EnglishName nvarchar(150) null,
  CONSTRAINT PK_Test PRIMARY KEY NONCLUSTERED ( ID )
);

ALTER TABLE Test ADD CONSTRAINT UI_Test_NamePair UNIQUE (NativeName, EnglishName);

Why do the following statements cause a Unique Constraint violation on the second insert? And is there any way around it?

INSERT INTO Test( EnglishName, NativeName ) VALUES ( N'Sangri', N'桑日县' );
INSERT INTO Test( EnglishName, NativeName ) VALUES ( N'Sangri', N'桑日县 ཟངས་རི་རྫོང་།' );

This results in (translated):

Violation of UNIQUE KEY constraint "UI_Test_NamePair". A duplicate key can not be inserted into the Test object. The duplicate key value is (桑日县 ཟངས་རི་རྫོང་།, Sangri).

sql-server
1 Answers

As I mentioned in the comment, this is likely due to the collation you're using. What ever your default collation is, the characters 'ཟངས་རི་རྫོང་།' are being ignored for comparison purposes. For example, forcing the collation to Latin1_General_CI_AS replicates this error:

CREATE TABLE Test
(
  ID bigint IDENTITY( 1, 1 ) not null,
  NativeName nvarchar(150) COLLATE LATIN1_GENERAL_CI_AS NOT NULL,
  EnglishName nvarchar(150) null,
  CONSTRAINT PK_Test PRIMARY KEY NONCLUSTERED ( ID )
);

ALTER TABLE Test ADD CONSTRAINT UI_Test_NamePair UNIQUE (NativeName, EnglishName);

INSERT INTO Test( EnglishName, NativeName ) VALUES ( N'Sangri', N'桑日县' );
INSERT INTO Test( EnglishName, NativeName ) VALUES ( N'Sangri', N'桑日县 ཟངས་རི་རྫོང་།' );

Msg 2627 Level 14 State 1 Line 2
Violation of UNIQUE KEY constraint 'UI_Test_NamePair'. Cannot insert duplicate key in object 'dbo.Test'. The duplicate key value is (桑日县 ཟངས་རི་རྫོང་།, Sangri).
Msg 3621 Level 0 State 0 Line 2
The statement has been terminated.

One way to solve the problem is to use a different collation, such as a binary collation:

--Ensure you drop prior test table
CREATE TABLE Test
(
  ID bigint IDENTITY( 1, 1 ) not null,
  NativeName nvarchar(150) COLLATE Latin1_General_BIN NOT NULL,
  EnglishName nvarchar(150) null,
  CONSTRAINT PK_Test PRIMARY KEY NONCLUSTERED ( ID )
);

ALTER TABLE Test ADD CONSTRAINT UI_Test_NamePair UNIQUE (NativeName, EnglishName);

INSERT INTO Test( EnglishName, NativeName ) VALUES ( N'Sangri', N'桑日县' );
INSERT INTO Test( EnglishName, NativeName ) VALUES ( N'Sangri', N'桑日县 ཟངས་རི་རྫོང་།' );

Note that as a result, when comparing the values against others columns with your default collation you will likely get collation errors. Therefore, you might want to use a computed column, and force the collation and index that:

CREATE TABLE Test
(
  ID bigint IDENTITY( 1, 1 ) not null,
  NativeName nvarchar(150) NOT NULL,
  NativeNameBinCollated AS NativeName COLLATE Latin1_General_BIN PERSISTED,
  EnglishName nvarchar(150) null,
  CONSTRAINT PK_Test PRIMARY KEY NONCLUSTERED ( ID )
);

ALTER TABLE Test ADD CONSTRAINT UI_Test_NamePair UNIQUE (NativeNameBinCollated, EnglishName);

INSERT INTO Test( EnglishName, NativeName ) VALUES ( N'Sangri', N'桑日县' );
INSERT INTO Test( EnglishName, NativeName ) VALUES ( N'Sangri', N'桑日县 ཟངས་རི་རྫོང་།' );
user_20299830
Related