Unique Mariadb constraint error (double entry) for two entries with the same space difference
Mariadb - version 10.0.23 I am using below script for 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 ');
second insert got error, error message
duplicate record 'name1' for key 'UK_TEST',
highlight, the second is not the same as the first, the values have one more space in the suffix
can anyone help me on this issue?
source to share
According to the documentation , for VARCHARs 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, which means that VARCHARs (as well as CHAR and TEXT values) are compared disregarding trailing spaces. This does not apply to pattern matching LIKE operator, which takes trailing spaces into account.
If the unique index consists of a column in which the end-string characters are delimited or ignored, a duplicate key error will be inserted into that column where the values differ only in the number of characters at the end of the string.
source to share
You marked the "username" field as "UNIQUE". This way it will only accept unique values across the entire column. You are trying to insert duplicate values.
More details at https://dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html
source to share
You run into problems like this when the username in the database is either set to "UNIQUE" or set to "PRIMARY KEY". In your case, this is unique , so you cannot have two strings that share the same username, which is very logical and correct.
I advise you to learn more about mysql before going ahead.
Read more about primary key constraints here: https://dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html
source to share