unique Constraint doesn't work properly with space character

Question

In char data type including unique constraints, when adding a space starting to the string in the column, unique constraint violated error is given. For example, when trying to make a column with '10200 - Kopie' '10200 - Kopie - Kopie' I get a unique constraints error, although it is clear that the two data are not the same. SQL Server 2019.

Answer 1

As other have said, the data you posted does not result in any constraint violation:

   CREATE TABLE Ates (col varchar(200) NOT NULL UNIQUE)  
   go  
   INSERT Ates(col)  
      VALUES('10200 - Kopie'),   
             ('10200 - Kopie - Kopie')  
   go  

yields (2 rows affected).

However, if now attempt to add:

   INSERT Ates(col)  
      VALUES('10200 - Kopie   ')  

This does indeed result in a constraint violation. In SQL Server trailing spaces does not count as significant when comparing values.

Answer 2

Hi @Ates Ates
Could you please share us your table structure (CREATE TABLE …)？
Since I guess it may be caused by the short length of your CHAR column, please refer to the following example.

CREATE TABLE Test (col char(13) NOT NULL UNIQUE)  
  
SET ANSI_WARNINGS OFF;  
INSERT Test(col)  
VALUES('10200 - Kopie'),   
      ('10200 - Kopie - Kopie')  
SET ANSI_WARNINGS ON;  
  
DROP TABLE Test  

Of course, this is just my guess. Whether it is correct or not depends on your table structure.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.