Foreign Keys, Windows, and Non-Existent, Existing Tables!

[Jumpy09]

This is just starting to bother me, when does a table exist... but not exist?

 

I am utilizing Navicat for Database Administration and it makes adding Foreign Keys.. as simple as selecting from a drop down list.  So these tables do exist and their fields do exist... however I am getting this error!

 

Foreign key constraint fails for table `MyDB`.`My_Table`:
blahblahblah
But the parent table `MyDB`.`My_Table_2` or its .idb file does not currently exist!

 

I am using XXAMP 1.7.* which has not been a problem for me in a very long time.  I had a problem in the past where Windows wouldn't keep uppercasing of Table Names or Rows properly so my Foreign Keys would fail on a Linux Set-Up.  So I modified the "lower_case_table_names" to 0 which keeps the original table names.  All look-ups have always been in the correct casing, but I am starting to wonder if this may end up messing with my Foreign Keys.

 

They keep failing, but if mysql is storing them with the proper casing... why would the look-up be failing?

 

Any suggestions?

 

P.S. Keep in mind that Navicat gives me a list of tables to select from, all of which do actually exist.  I have not tried this on a Linux Set-Up as I only have access to the XXAMP Windows Set-Up during Development.  It is so much easier to locally edit files than have the middle man of making sure they are updated before testing.  No the table names and the db names in the error code are not real.  I am writing this from my Laptop and couldn't copy the actual error message from my Desktop.  I will continue looking for problems.

 

Thanks!

[Jumpy09]

Alright to answer this question in case someone happens to run across this problem.

 

When utilizing Windows and wanting to utilize Case-Sensitivity for Cross Windows/Linux Support...  utilizing Lower_Case_Table_Names = 0 will only help move the casing of the table names properly over to Linux.  Linux is a case-sensitive operating system so the table "accounts" is much different than "Accounts", where as in windows they are exactly the same thing.

 

Unfortunately where you can properly access your tables between both Windows and Linux with Case-Sensitivity, Foreign Keys seem to only work with what InnoDB was originally intended to do, which was to store all table names in the lowercase to aid in smooth transition between Linux and Windows.

 

I had a problem swapping Foreign Keys from Windows to Linux before due to the fact the Foreign Keys were trying to access the table names differently, so I thought I fixed this by swapping lower_case_table_names to 0 because I like to utilize Upper_Cased table names because I thought it looked nicer and cleaner.

 

Unfortunately everyone is just better off sticking to lower case table names regardless due to this if you are utilizing Foreign Keys.  I have not had any problem moving between Windows and Linux without Foreign Keys and I have always ended up keeping the correct casing on the table names.  However the Foreign Key portion of InnoDB doesn't like it very much... so just save yourself the trouble and always stick to lowercase table names.  I know most programmers already do this, but for people like me who want to make crap complicated.... hope this finds you well.

 

Note -- When I mean I was having issues swapping over, it was because Windows was spitting out the table names in lowercase and everything was set up for case-sensitivity.  I can't remember the specific problem or why I changed the lower_case_table_names to 0, all I know is I thought I fixed a problem.  Now I couldn't create or use Foreign Keys without it causing a nightmare.  So lesson learned, sticking with lower_case table names .

 

Thank you to everyone who attempted to answer this question or at least checked it out.  I had a feeling it had to do with the lowercase issue, which is why I included it.  Good luck in your programming career!

[Jessica]

Glad you figured it out! Thank you for posting your solution, and marking as solved.

[Jumpy09]

You are welcome, I figure if I find out the answer it is only right to post the solution for other people.  Technically I caused my own problem, but glad I remembered what potentially caused it.

 

Another thing is:  If you actually have this problem, dumping an SQL File and swapping the lower_case_table_names to 1, delete the tables, then execute the sql file... this will store the table names as lower case.  Just remember to swap all the queries to lowercase in this situation, just as a precaution.  This will swap all the Foreign Keys to the lower case, the tables to the lower case, and everything works then.  No need to remove Foreign Keys, especially if you made the booboo of disabling Foreign Key checks instead of resolving the originating problem like I did lol.