Unique Key involving string fields

Question

I am having some issues with the implementation of a constraint for unique key.

It has 3 fields and one of them is a string. The problem is that I get violation of unique key when I send this two rows:

DB

First: Id-type int
Second: PosId-type uniqueidentifier
Third: Description-type nvarchar(256)

Defined constraint

ALTER TABLE dbo.StAPSachverhalte ADD CONSTRAINT
    UQ_myuniqueKey UNIQUE NONCLUSTERED 
    (
        CompanyId,
        PosId,
        Description
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

C# parameters code

new SqlParameter("@Description", SqlDbType.NVarChar, 256),
new SqlParameter("@PosId", SqlDbType.UniqueIdentifier),
...
MyParameters1[0].Value = _newData.Description;
MyParameters1[1].Value = _newData.PosId;

CommandText

@"INSERT INTO StAPSachverhalte( 
                    [Description], 
                    [PosId] 
                    ) 
                VALUES ( 
                    @Description, 
                    @PosId) 
                SET @ID = SCOPE_IDENTITY()",

Sent entries

'1, random GUID, 001'

'1, random GUID, 001 '

If I am not wrong, both should be valid because '001' <> '001 '.

Error

Cannot insert duplicate key in object 'dbo.XXX'. The duplicate key value is (76, 9bcba4e1-4d16-487c-977f-cda933bb0955, 0001 ).

I try to save the data with a normal sqlcommand executenonquery using paramenters and I check the myparameter[index] contains the corresponding value with an empty space at the end.

Any ideas?

Answer 1

After some research, I found the answer is that while you are indeed sending two different strings, the trailing spaces are not taken into account when the DB compare strings. Including the unique comparison when inserting.

So, to work as you want, you have to add another field, like the length of the string, as suggested here: MySQL database with unique fields ignored ending spaces