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`))

This can be dangerous as it suggests that a car references a missing color. — Michael Mior
– Michael Mior, Commented Oct 5, 2011 at 14:58

N.B. · Accepted Answer · 2011-10-05 14:51:36Z

7

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.

answered Oct 5, 2011 at 14:51

N.B.

14.2k3 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom Mac · Accepted Answer · 2011-10-05 15:35:41Z

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.

answered Oct 5, 2011 at 15:35

Tom Mac

9,8633 gold badges32 silver badges40 bronze badges

Collectives™ on Stack Overflow

mysql cannot create foreign key

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related