mysql cannot create foreign key

Question

here are my two tables

CREATE TABLE IF NOT EXISTS `carslibrary` (   
  `CarID` int(10) unsigned NOT NULL AUTO_INCREMENT,   
  `CarName` varchar(255) NOT NULL,  
  `colorslibrary_ID` int(11) unsigned NOT NULL,   
  PRIMARY KEY (`CarID`),
  KEY `colorslibrary_ID` (`colorslibrary_ID`) 
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

CREATE TABLE IF NOT EXISTS `colorslibrary` (   
  `ColorID` int(11) unsigned NOT NULL AUTO_INCREMENT,   
  `ColorName` varchar(255) NOT NULL,
  PRIMARY KEY (`ColorID`) 
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

I get an error on the following query:

ALTER TABLE  `carslibrary` ADD FOREIGN KEY (  `colorslibrary_ID` )
REFERENCES  `cars2`.`colorslibrary` (`ColorID` );

MySQL says:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`cars2`.<result 2 when explaining filename '#sql-cf8_41a'>, CONSTRAINT `#sql-cf8_41a_ibfk_1` FOREIGN KEY (`colorslibrary_ID`) REFERENCES `colorslibrary` (`ColorID`))

Answer 1

Your tables aren't empty, therefore a constraint fails (reference not found) when you create it.

Use SET FOREIGN_KEY_CHECKS = 0; and re-run your alter table.

Answer 2

I would firstly identify orphaned rows in the carslibrary table:

select * from carslibrary where colorslibrary_ID not in (select ColorID from cars2.colorslibrary);

Then decide what you want to do with these orphaned rows. Want to DELETE them from the carslibrary table? UPDATE them to an existing parent ColorID in the colorslibrary? INSERT a new ColorID in the colorslibrary table to cater for the orphaned rows?

Once you've tidied up your data you should be able to run the ALTER TABLE with no errors.