Search code examples
mysqlforeign-keysvarcharmariadb

MariaDB VARCHAR foreign key reference with extra white space

I have a field in one table that references a field in another table. The foreign key constraint is working as it should, but if I add (UPDATE) an extra space character to the end of the field in the referencing table then the constraint is not enforced and the update is allowed even though the values differ.

Does this have something to do with how varchars work or is this a major bug? It was quite a surprise for us when we discovered it. Any thoughts?

Confirmed on MariaDB versions 10.0.14 and 5.5.35.

Thanks!

Solution

  • As far as I know, foreign key constraints are validated using the rules for =. That means that it follows the collation rules for the two columns (which should have the same collation). It also means that spaces at the end of the string are ignored.

    As per the documentation:

    Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:

    mysql> SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci;

+-----------------------------------------+
| 'ä' LIKE 'ae' COLLATE latin1_german2_ci |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+

mysql> SELECT 'ä' = 'ae' COLLATE latin1_german2_ci;

+--------------------------------------+
| 'ä' = 'ae' COLLATE latin1_german2_ci |
+--------------------------------------+
|                                    1 |
+--------------------------------------+

    In particular, trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator . . .

    Because the join condition is likely to use =, this comparison makes sense for foreign key comparisons.