InnoDB Fun

I have been working with InnoDB for the first time lately and here are a couple things I have learned so far:

1. You cannot assign a foreign key to a field if the two field types to do not match exactly. This means that if your 'user_id' field on your 'userGroup' table is unsigned and for some reason your 'id' field on your 'user' table is signed, it will not work. For instance if I have a relationship between 'applicant_id' on the 'application' table to the 'id' field on the 'applicant' table and I try to change one of the types
   

   #1025 - Error on rename of '.\sampledb\#sql-894_7bb' to '.\sampledb\applicant_applicant' (errno: 150)

   
   I got to a point where I needed to change all of my tinyint() fields to int() and I was going to have to unmap and then make the changes and then map them again. I have well over 30 relationships and I was not going to do that. The solution? Take a MySQL dump of the database and do a find/replace on tinyint(). That changes them all so they match, then just import.
   
   
2. If you try to assign a foreign key from one column to another and a row in the local table has a value that cannot be found in the foreign table, it will not work. You will receive an error:
   

   #1452 - Cannot add or update a child row: a foreign key constraint fails (`sampledb/group`, CONSTRAINT `group_ibfk_1` FOREIGN KEY (`id`) REFERENCES `employee` (`id`))

   
   This also happens if you try to add a new entry and the local_id does not match a row in the foreign table.