Bug #35520 Foreign keys: case sensitive constraint names
Submitted: 24 Mar 2008 17:29 Modified: 3 Jul 2008 17:25
Reporter: Peter Gulutzan Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:6.1.0-fk-debug OS:Linux (SUSE 10 | 32-bit)
Assigned to: Dmitry Lenev CPU Architecture:Any
Triage: D3 (Medium)

[24 Mar 2008 17:29] Peter Gulutzan
Description:
I'm using mysql-6.1-fk.
I start mysqld with --foreign-key-all-engines=1.

I create a table with two foreign-key constraints:
ı (U+0131 Latin small letter dotless I) and I.
I expect an error. There is no error.

I create a table with two foreign-key constraints:
Σ (U+03A3 Greek capital letter sigma) and
ς (U+03C2 Greek small letter final sigma).
I expect an error. There is no error.

A possible applicable rule is (quoting
MySQL Reference Manual 8.2.2. Identifier Case Sensitivity)
http://dev.mysql.com/doc/refman/6.0/en/identifier-case-sensitivity.html
"Object names may be considered duplicates if their uppercase forms
are equal according to a binary collation."
That's not true for all things, and I'm aware it's controversial.
Putting it another way, I believe that:
(1) upper('ı') = upper('I') /* using upper() not lower() */
(2) I can't create procedures named ı and I
therefore I believe that it's wrong to create constraints
named ı and I.

How to repeat:
mysql> set names utf8;
Query OK, 0 rows affected (0.03 sec)

/* Demonstrate that ı = I for procedures. */

mysql> create procedure ı () set @a=5;
Query OK, 0 rows affected (0.17 sec)

mysql> create procedure I () set @a=5;
ERROR 1304 (42000): PROCEDURE I already exists

/* Demonstrate that ı <> I for constraints. */

mysql> create table k6 (s1 int constraint I references k6(s1)
    -> constraint ı references k6(s1));
Query OK, 0 rows affected (0.10 sec)

/* Demonstrate that Σ <> ς for constraints. */
mysql> create table k7 (s1 int constraint Σ references k7(s1)
    -> constraint ς references k7(s1));
Query OK, 0 rows affected (0.07 sec)
[24 Mar 2008 22:10] Miguel Solorzano
Thank you for the bug report.

[miguel@mirador dbs]$ 6.1fk/bin/mysql -uroot db4
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 6.1.0-fk-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure ı () set @a=5;
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure I () set @a=5;
ERROR 1304 (42000): PROCEDURE I already exists
mysql> create table k6 (s1 int constraint I references k6(s1)
    -> constraint ı references k6(s1));
Query OK, 0 rows affected (0.01 sec)

mysql> create table k7 (s1 int constraint Σ references k7(s1)
    -> constraint ς references k7(s1));
Query OK, 0 rows affected (0.01 sec)

mysql>
[3 Jul 2008 17:25] Dmitry Lenev
To sum up IRC discussion between me, Peter and Konstantin.
 
Currently uniqueness of constraint names is enforced by creating 
.CNS files in the database directory (much in the same way as it 
happens for trigger names). Thus their case sensitivity is the
same as case sensitivity of filesystem.
 
Making constraint names case insensitive requires some work and/or
would create yet another kind of objects with name case-sensitivity
rules different from all other kind of objects (and most probably 
would not make us closer to the standard rules).
 
Therefore we decided that at least until WL#922/923 are on the way
we will accept current case sensitivity rules (i.e. FS dependency)
and won't make significant changes to current implementation. 
We might adjust it slightly if we discover that current 
implementation introduces problems with meta-data locking.