Foreign Key Addition Troubleshooting

[Jumpy09]

Alright I tried to set up Foreign Keys on a Database with data in it, but I decided that maybe it was failing because I had data stored already.  Now this isn't the case as I truncated all the tables and Foreign Keys still fail to be inserted.

 

I am getting the 1005 - Can't create table "" ( errno: 105 )

 

What I know:

 

Table Engine: InnoDB

Table Character Set: utf8 - UTF-8 UNICODE

Table Collation: utf8-unicode-ci

Table Row Format: COMPACT

 

Primary Keys: Set

Primary Key Types: INT / VARCHAR

Field Data Types: Exactly Identical for Primary Key of Parent Table and Foreign Key of Child Table

 

Note: I have verified this Information over and over.  I am 100% positive there are no MyISAM Tables and all the information is 100% Accurate.  I am open to suggestions, but this is a bit aggravating.

 

Additional Information:  I dumped the Database from PHPMyAdmin on a Publication Server and Executed the SQL Script through Navicat on my Development Server.  In the Script contained SET FOREIGN_KEY_CHECKS=0; at the top and SET FOREIGN_KEY_CHECKS=1; at the bottom.

 

I cannot figure out why I cannot add any Foreign Keys to the Tables and it doesn't matter what I am trying to add, it pops up with that error.

 

The MySQL User has Super Admin Privileges, so I do not think it is that.  Any suggestions?

[ManiacDan]

Create the table first, then alter the table to add the foreign keys one at a time until you find the problem.

[Jumpy09]

I have previously added Foreign Keys, this was before uploading it to the Production Server and then Exporting it to upload back to the Development Server.  I thought maybe it was because the data wasn't lining up properly, so I Truncated all of the tables and then tried to add them back again.

 

I can't get them to add on anything, going back and trying from the beginning isn't really an option at this moment as I am still developing on the project itself.  I had intended on adding the rest of the Foreign Keys once I was completely done with the Database, the Client needed something to get an Adsense Account, so I uploaded what I had.  Since the Client created an Account, I didn't want to upload a from scratch Database, so I just dumped it into an sql file and uploaded it onto my Development Server.  Since then I am unable to add Foreign Keys.

 

There isn't a mix-up of information as I Truncated and despite the difference between data types I can't get any Foreign Key to be added.  All Int Data Types are Unsigned, and all of the Varchar Fields have the exact length, char sets, collations, and none of them are Binary.

 

I posted this because I was wasting ample Development Time trying to find a resolution.  I am sure if I started from a clean database and added all the tables back up manually, then the Foreign Keys would stick.  I do not really have that time as a luxury right now, so I was wondering if something happened in the process of Dumping the Table and then Importing it into a different Server would cause this effect.

 

I dumped out of PHPMyAdmin and Imported via Navicat MySQL Premium.

 

I enabled skipping the Foreign Key Check as Importing has caused issues for me before.  I had considered that something was wrong with the FOREIGN_KEY_CHECK, but even set to 0 the Foreign Keys from the SQL File were added on Import.  It is just after it is successfully Imported that I cannot add any new Foreign Keys.  I have tried this on several different Parent / Child relationships to no avail.

 

Unless I can come up with a reason as to why it allowed me to add Foreign Keys on the Original Development Set-Up and not the Imported Development Set-Up, I am just going to have to skip Foreign Keys for this job.  I've double, triple and quadruple checked the Fields, the Data Types, the Engines, the Character Sets, the Collations and anything else that could potentially cause the issue.  As I said there are Foreign Keys in the database from when I originally created the table.  The problem didn't start until I exported and imported the database.

 

EDIT:::

 

Production Server is Linux

Development Server is Windows

 

I forced lower_case_table_names = 0 on my Development Server so that I could keep the Upper Case Table Names as it was required to be set.  If lower_case_table_names is set to 0 which turns on Case Sensitivity, could that potentially be a problem?  Navicat will Export the Development Server SQL with the proper case in the Table Names, so I would figure that it would be adding everything in terms of Foreign Keys properly.

[mikosiko]

Normally the error could be caused for:

a) The Foreign Key recipient table doesn't have a INDEX on the referenced column.

b) Some of the FK column or the referenced column Flags are different... common case UNSIGNED/SIGNED, BINARY/NON BINARY,  or (most common case)

b) The data type between the FK column and the referenced column are different.

[Jumpy09]

Normally the error could be caused for:

a) The Foreign Key recipient table doesn't have a INDEX on the referenced column.

b) Some of the FK column or the referenced column Flags are different... common case UNSIGNED/SIGNED, BINARY/NON BINARY,  or (most common case)

b) The data type between the FK column and the referenced column are different.

 

A) The Parent Table Columns are all Primary Keys, Indexes would automatically be added to the Child Table if not set before hand.  I have set indexes on the Child Table for testing, but to no Avail.

B) Both Parent Table Column and Child Table Column Flags are identical.  If Unsigned, both are Unsigned.  I do not use Binary.

C) The Data Types between the Parent Table Column and the Child Table Column are Identical.

 

The two I am attempting to set up are AID and CID - AID is varchar(36) and CID is int(11) Unsigned.

I have checked for all three of those potential issues, but everything is Identical.

 

As I mentioned I have forced lower_case_table_names to 0 on my Windows Development Server because I want Uppercase Letters in my Table Names.  When working on the Development Server this didn't want to comply, so when calling the Tables on the Linux Production Server nothing worked.  I could either keep lower_case_table_names forced to 0 on the Windows Development Server or Replace the Table Names in the SQL Export, which I opted for the easy solution.

 

Unless lower_case_table_names forced to 0 on the Windows Development Server is the issue, I do not know what else could be.

 

Indexes add just fine, but Foreign Keys will not.  The Tables on the Windows Development Server are uppercase where they should be in regards to the .frm files.  In the ALTER TABLE Syntax the Table Names are also Uppercase where they should be.

 

With Data in the Database, the Child Table Columns match up to Primary Keys in the Parent Table Columns.  The int(11) Primary Keys are Auto-Incrementing so they have to have the Primary Key Index.

 

I see absolutely no reason why these Foreign Keys shouldn't be submitted, I will try in a bit to add one on the Production Server to see if it is just the Windows Development Server.

 

UPDATE:::

 

I just successfully added the Primary Key to the Production Server which has the exact same Database as the Development Server.  I am beginning to think that it is due to the lower_case_table_names forced to 0.  If set to 1 or 2 the Table Names are not stored right which causes issued for the Production Server because windows stores everything lowercase.  I am calling all the Tables and Everything with the proper cases and do not mix casing in any situation.  I wanted to use uppercase letters to make it neater and swapping to lowercase would result in editing every query to work right on Linux.

 

Can anyone comment on whether forcing lower_case_table_names to 0 can cause problems with adding Foreign Keys?

[Jumpy09]

After some more investigation it appears as if the InnoDB Foreign Key Parser is separate from MySQL itself.  So if MySQL is forced to being Case Sensitive on a Windows Platform, the Foreign Key portion may be still set as lowercase when getting added, which I suppose would throw the error.

 

It seems I will just have to wait until I am completely done and just add the Foreign Keys on the Production Server and then Export it afterwards if I need to further work on it on the Development Server.

[Jumpy09]

Verified my problem.  lower_case_table_names forced to 0 on a Windows Server does throw problems with Foreign Key Constraints.

 

The Table Names used in the Foreign Keys are lowercased when lower_case_table_names set to 0 makes it case specific.  Since lowercase table names wouldn't match up with MiXeDcAsE Table Names, the Foreign Key Constraint Fails.

 

I verified this by trying to add in a new record in a Table which received it's Foreign Key from the Import.

 

If you also work on a Development Server that is on a Windows Operating System and you have forced lower_case_table_names to 0 to keep your Table Names.  I advice also running this on the Windows Server.

 

SET FOREIGN_KEY_CHECKS=0;

 

I would also only add Foreign Keys on a Linux Server to make sure if you have Case Specific Table Names, that the Foreign Keys will match up properly.

 

If you upload from Windows to Linux and you have forced lower_case_table_names to 0 on Windows, ensure you set the Foreign Key Checks back to 1, this should automatically happen if you turn off Foreign Key Checks in the SQL Export Dump.

 

If you force lower_case_table_names to 1 on Linux, then you shouldn't have any problems.

 

Currently until MySQL integrates FULL Foreign Key Support into MySQL, this will be an issue.  Thank you for all the assistance, kind of sad that you have to force Linux to work with Windows, but there isn't a full way to make Windows work with Linux.

 

Oh well, problem solved.