April 22, 2012

Mysql alter statement

Question by Eagletrophy

this is my alter statement trying to connect two tables:

ALTER TABLE my_contacts 
  CHANGE `profession` `profession_id`  INT NOT NULL, 
  ADD CONSTRAINT professions_profession_id_fk 
  FOREIGN KEY (profession_id) REFERENCES professions (profession_id)

and I have the following error:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`contacts`.<result 2 when explaining filename '#sql-1ca_73'>, CONSTRAINT `professions_profession_id_fk` FOREIGN KEY (`profession_id`) REFERENCES `professions` (`profession_id`))

can anyone guide me on what this is? I am actually learning how to write sql myself since I have used tools all this while.

Answer by verhage

Your foreign key constraint is failing.

The foreign key you are trying to create enforces every profession_id in my_contacts to be present in your professions table. Right now, this is not the case. You should lookup all records in my_contacts where the profession_id is not present in professions and fix those first.

Answer by Starx

This is a relationship conflict. An easier to solve this is

  • Remove the relationship between the fields
  • Alter the tables
  • While adding the relationship again, fix the errors you receive.
...

Please fill the form - I will response as fast as I can!