By default, MySQL takes advantage of case-sensitive filenames on Linux, with lower_case_file_system = OFF and lower_case_table_names = 0 (i.e. the filesystem is case-sensitive, therefore database names are case sensitive; and table names aren’t forced to lower case). In this situation it’s possible to have two different databases whose names differ only in case – for example, Photos and photos – although this should probably be avoided wherever possible!
The “FOREIGN KEY Constraints” section of the MySQL manual has this to say about constraint names:
If the
CONSTRAINT symbolclause is given, thesymbolvalue must be unique in the database. If the clause is not given, InnoDB creates the name automatically.
This isn’t completely accurate. Internally, InnoDB stores foreign key constraint names as case-insensitive, with the database name prepended. So although onedatabase.b_ibfk_1 is treated as different to anotherdatabase.b_ibfk_1, Photos.b_ibfk_1 and photos.b_ibfk_1 are treated as the same.
So if database names that only differ in case are to be avoided, why might this be a problem? Well, if you want to follow the advice in the MySQL manual, and use lower_case_table_names = 1, you need to change all database names to lower case before using the new setting.
Say you have this database:
CREATE DATABASE Photos;
USE Photos;
CREATE TABLE a (one INT PRIMARY KEY) TYPE=InnoDB;
CREATE TABLE b (one INT PRIMARY KEY, FOREIGN KEY (one) REFERENCES a(one)) TYPE=InnoDB;
Notice the constraint on table b. Now you want to change the database name to photos, so you create a new database with that name, dump the tables and data from the old database with mysqldump, and then try to import them into the new database. You’ll get this error:
ERROR 1005 (HY000) at line 42: Can't create table './photos/b.frm' (errno: 121)
This slightly cryptic message is telling you that table b can’t be created in database photos – because the constraint name you’re trying to use in photos (lower case) is already used in Photos (upper case). The full details of the error can be seen in the output from SHOW INNODB STATUS:
070312 21:31:27 Error in foreign key constraint creation for table `photos/b`.
A foreign key constraint of name `photos/ba_ibfk_1`
already exists. (Note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.
There are a number of ways to get around this:
- drop the old database before importing into the new one;
- remove the constraints from the old database before importing into the new one;
- manually change the constraint names in the export file before running the import.
Since MySQL 5.0 doesn’t allow a database to be renamed, it’s worth bearing this is in mind if you’re going through the mysqldump and import procedure to rename a database, where the old and new database names differ only in their case.
Cool!