Mariadb unique constraint error ( duplicate entry) for two records with one space difference

Question

Mariadb is version 10.0.23 I use below script to do testing

create table test(
username varchar(30)
,constraint UK_TEST unique (username);

insert into test values('name1');
1 row inserted.

insert into test values ('name1 ');

the second insert got error, the error message is

duplicate entry 'name1 ' for key 'UK_TEST',

highlight, the second one is not same as the first one, the values have one more space in suffix

is there anyone can help me on this issue?

Answer 1

According to the documentation, for VARCHAR and several other data types, trailing spaces are ignored in comparisons, including those used for unique constraints:

Currently, all MariaDB collations are of type PADSPACE, meaning that VARCHAR (as well as CHAR and TEXT values) are compared without regard for trailing spaces. This does not apply to the LIKE pattern-matching operator, which takes into account trailing spaces.

If a unique index consists of a column where trailing pad characters are stripped or ignored, inserts into that column where values differ only by the number of trailing pad characters will result in a duplicate-key error.

Answer 2

You have marked "username" field as "UNIQUE". So, it will accept only unique values in that entire column. You are trying to insert duplicate values.

Read more at https://dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html